Client Management Suite

Hi,
I'm looking to add a field to an existing report. The report is Computer Summary. I would like to add in the OU that the machine is a memeber of.

Any help appreciated - Thanks

Joe.

you have to do is clone the report.
edit the new report and select the part of the additions "IAD. [Distinguished Name] [OUAD]"
on the part of the "from" add " LEFT OUTER JOIN dbo.Inv_Global_Active_Directory_Details IAD
  ON i.Guid = IAD._ResourceGuid"
 

Save Changes
and ready.
 

I send you the report attachment.

Attachment(s)

Computer Summary with AD.zip   4 KB 1 version

Thanks :-)

To get just the final OU name, use a query like this:

 

SELECT

v1.[Name] as 'ComputerName',

Item.Name as 'GroupName',

Item.Description as 'GroupDescription',

v1.Guid as 'ComputerGuid',

ScopeMembership.ScopeCollectionGuid as 'GroupGuid',

Item.SecurityGuid

FROM vComputer v1

JOIN ScopeMembership ON v1.Guid=ScopeMembership.ResourceGuid

JOIN ScopeCollection ON ScopeMembership.ScopeCollectionGuid=ScopeCollection.ScopeCollectionGuid

JOIN Item ON ScopeMembership.ScopeCollectionGuid=Item.SecurityGuid

--Return results only if the Organizational View is the Active Directory Organizational View

WHERE ScopeCollection.ScopeSetGuid='{AB123C45-D67E-8901-FGH2-345678I90123}'

The GUID here is the GUID of the Active Directory VIEW.  Right-click on the VIEW (blue globe = view, green grid globe = groups) and click Properties, then copy the GUID into the query.

If you run the query above, your results include the name of computers that exist in the Active Directory view, the group name of that OU (e.g. AltirisTest), the description from Altiris, and then the Computer and Group GUIDs (for troubleshooting's sake).

If you wanted to take this concept and apply it to the Computer Summary report, I would do it this way:

Add Item.Name to your SELECT statement (you make a jump from the Computer's GUID to the same GUID belonging in a ScopeMembership, then from the GUID of the ScopeMembership's collection to ScopeCollection's GUID, and then match this GUID to the Item table)

Item.Name as 'Group Name'

Add these two lines as JOINs:

JOIN dbo.ScopeCollection sc ON sm.ScopeCollectionGuid=sc.ScopeCollectionGuid

JOIN Item ON sm.ScopeCollectionGuid=Item.SecurityGuid

And add this line as part of your WHERE clause:

AND sc.ScopeSetGuid='{AB123C45-D67E-8901-FGH2-345678I90123}'

Again this GUID restricts you to the Active Directory view and gets the last group name only.

but when I run your first script it gives me error

Msg 208, Level 16, State 1, Line 1

Invalid object name 'vComputer'.

I'm hopeless as SQL!!

Joe.

What if you skip the example and jump straight to the extra lines I posted, inserting them into the existing (cloned) report?

Nope - still getting errors. Any chance anyone could attach this so I could use it. My sql skills are non existent, and if there are problems in the additions I would be none the wiser :-)

Joe.

Can you post your query and the error you're getting?  I just shut down my pre-prod NS7 server while I install 7.1, so I can't peek at it right now.

Invalid object name 'vcomputer' sounds like you're not running this against the Symantec_CMDB database, more like master, which would be the default when you open SSMS.

OK - this is the hardware summary report edited with the suggested above details by Mclemson. No wi Hope they are in the right place ..

DECLARE @v1_TrusteeScope nvarchar(max)

SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT i.Guid, Item.Name as 'Group Name'

CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name])) ELSE i.[Name] END [Name],

CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN REPLACE (i.[Name],SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name]))+ '.','') ELSE i.Domain END [Domain],

hs.[Vendor Name] [Manufacturer],

sn1.[Model] [Computer Model],

cpu.[Max Clock Speed (Mega-hertz)] [CPU Frequency],

cpu.[Family] [CPU Type],

hs.[Total RAM] / (1024 * 1024) [Memory (MB)],

ISNULL (i.[OS Name],os.[Name]) [Operating System],

d.[OS Version] [OS Version],

hs.[Total fixed drive size] / (1024) [Disk Size(MB)],

ld.[Free Space (Bytes)] [Free Space(MB)],

sn1.[Identifying Number] [Serial Number],

IAD.[Distinguished Name] [OU AD]

FROM dbo.vComputer i

JOIN dbo.ScopeMembership sm

ON sm.[ResourceGuid] = i.Guid

AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))

LEFT JOIN dbo.Inv_AeX_AC_Identification d

ON d._ResourceGuid = i.Guid

JOIN dbo.CollectionMembership cm

ON cm.ResourceGuid = i.[Guid]

JOIN dbo.vCollection it

ON it.Guid = cm.CollectionGuid

LEFT JOIN dbo.vHWPhysicalMemory m1

ON m1.[_ResourceGuid] = i.Guid

LEFT JOIN dbo.vHWComputerSystem sn1

ON sn1.[_ResourceGuid] = i.Guid

LEFT JOIN dbo.vHWProcessor cpu

ON cpu.[_ResourceGuid] = i.[Guid]

LEFT JOIN (SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)], _ResourceGuid

FROM dbo.vHWLogicalDisk GROUP BY _ResourceGuid) ld

ON i.Guid = ld._ResourceGuid

LEFT JOIN dbo.vOSOperatingSystem os

ON os._ResourceGuid = i.Guid

JOIN dbo.Inv_Operating_System_Summary oss

ON oss.ComputerResourceGuid = i.Guid

JOIN dbo.Inv_Hardware_Summary hs

ON hs.ComputerResourceGuid = i.Guid

LEFT OUTER JOIN dbo.Inv_Global_Active_Directory_Details IAD

ON i.Guid = IAD._ResourceGuid

JOIN dbo.ScopeCollection sc

ON sm.ScopeCollectionGuid=sc.ScopeCollectionGuid

JOIN Item

ON sm.ScopeCollectionGuid=Item.SecurityGuid

WHERE 1 = 1

AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')

AND i.Domain LIKE '%Domain%'

AND lower (it.[Guid]) LIKE lower ('%Filter%')

AND sc.ScopeSetGuid='{9fe89aed-9054-4465-84df-d1c945bbc834}'

I excute this in SQL studio express and get the folowing errors :

Msg 156, Level 15, State 1, Line 6

Incorrect syntax near the keyword 'CASE'.

Msg 156, Level 15, State 1, Line 23

Incorrect syntax near the keyword 'LEFT'.

Msg 102, Level 15, State 1, Line 36

Incorrect syntax near 'ld'.

And it being run agains the correct database also.

Joe.

Insert a comma after 'Group Name':
 

SELECT DISTINCT i.Guid, Item.Name as 'Group Name'
 

Change to:

SELECT DISTINCT i.Guid, Item.Name as 'Group Name', 

Getting threre. The query now wuns but returns no results. I am entering the GUID from the OU underneath the active directory views. I've tried a few that do contain PCs but no luck... thanks for persevering :-)

Joe.

You need the GUID of the view, not the GUID of the group.  You're putting the GUID of the view there so that it only looks at groups in that view, not groups everywhere.  Otherwise you'd get duplicate results for each resource, each result listing a different group.

I am going to Manage, All Resources. I right click the Blue globe "Active Directory domains" and select properties. I copy the guid from this into the sql - it runs but there no rows returned. Is this correct?

Moved to Altiris report and ran. Still just giving me the headers and no results returned.

when I righ click the blue Globe and select properties The information below is displayed.. (why does it sy it is a vew ?) Is this the wrong one ?

You want to only return groups that are located in the AD view.  So by including the GUID of the view in the report, you're doing just that.

The report does work in my environment.  In SQL Studio, you can try testing it by removing the @TrusteeScope parts and then replacing all the variables with actual text, e.g. '%DOMAIN%' becomes 'MYCOMPANY%'

If you run it in SQL Studio with a specific computer name, domain, and filter, without the scoping, and then return it to Altiris and provide the same inputs for the computer name, domain, and filter variables, but receive no results, it suggests to me either a lasting SQL error or a permissions issue.  But I have to assume you're part of Symantec Administrators, so I admit that has me stuck.

I am, I should mention, a SQL novice.