Video Screencast Help

Add field to existing report

Created: 04 Mar 2011 • Updated: 04 Mar 2011 | 16 comments
This issue has been solved. See solution.

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.

 

 

Comments 16 CommentsJump to latest comment

Cesar_echeverria's picture

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.

AttachmentSize
Computer Summary with AD.zip 4.83 KB

Cesar Echeverria Castillo

Consultant Engineer

InterLAN Ltda, Medellin, Colombia

P.S.: If this works please let the community knows about it.

SOLUTION
mclemson's picture

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.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

BugTastic's picture

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.

mclemson's picture

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

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

BugTastic's picture

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.

mclemson's picture

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.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

jharings's picture

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.

Jim Harings
HP Enterprise Services
1st Rule of Connect Club: Mark the post that helped you the most as a 'solution'. 2nd Rule of Connect Club:You must talk about Connect club.

BugTastic's picture

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.

 

 

mclemson's picture

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', 

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

BugTastic's picture

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.

mclemson's picture

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.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

BugTastic's picture

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?

mclemson's picture

If you're still running in SQL Studio, and are not getting errors, move your query back to your Altiris report.  The variables (%Computer%, %Domain%, %Filter%, and Trustee Scope) need inputs in order to return results.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

BugTastic's picture

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 ?

 

 

Name: Active Directory Domains
Description: This organizational view contains all the resources imported from Active Directory.
Product Name: Directory Connector
Folder: \Resource Management\Organizational Views
Creation Date: Sunday, August 29, 2010, 2:40 AM
Modification Date: Monday, February 28, 2011, 1:05 PM
Last modified by:  
Guid: {da713c37-d04b-4069-bec0-649079a78646}
mclemson's picture

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.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com