Altiris Report - MS Products

[Migration User]

Hello Everyone,

I'm looking for some assistance in creating a report that will outline / count all MS products by location. The report should allow the user to drill down to view each location and pc.

Altiris has a few reports with this information already:

• Count of computers by OS
• Count of Microsoft Products
• Count of Computers by Office Edition and Version

Is there any way to view the SQL behind these reports?

Currently running Altiris NS v.7.0.8641.

Thanks for you help.

[Migration User]

Yes, clone the reports. and edit new report to view the SQL

[Migration User]

Thanks, but when I cloned the reports I dont get an edit option. I only get edit  when I select new SQL report.

 

See screenshot.

[Migration User]

Clic en Clone, type new name to report, run new report and clic in edit.

See screenshot.

[Migration User]

To edit a report, you must first Clone the report.  After providing a new name for the cloned report and clicking okay, highlight it in the left pane by clicking on it.  Once you do so, you will have an edit button in the right  pane located in the top, right-hand corner.  If you click Edit, you'll be able to view the SQL for the report as well as modify the report.

Does this help?

[Migration User]

Thanks all. I have managed to combine the above reports by type and count. However, I am unable to find the table/field that will allow me to sort by location.

Anyone know what this table/field is?

Thanks.

[Migration User]

which location?

[Migration User]

I agree.  How do you want to define location?

[Migration User]

Hopefully, I can explain this better this time. Sorry for the confusion.

My environment is spread across 7 different locations / sites. In AD each site has their own computers OU. How would you create a report to identify what software is in each site?

Thanks. Again sorry if this is more confusing than it should be.

[Migration User]

the 7 locations/sites have different subnet?

the differences over IP?
if so, create filter in base subnet, and report new add filter parameter

[Migration User]

Are you synchronizing with Active Directory using the directory connector?

[Migration User]

Thanks for your help.

Yes, each site has different subnets. How do I filter via subnet in the query?

Yes, i'm synchronizing with AD using the directory connector. Is there no easy way to query what systems are from NY vs NJ?

Thanks.

[Migration User]

You could also do this fairly easily by creating filters for your locations, especially if you are using AD import... you can create filters based on Site or OU. Then create the SQL query to include a selectable filter list..

[Migration User]

If your group names are unique, you can use a query like this to show the GroupName.  You must isolate it to a particular view by using the GUID of that view in the query.  In this example, you would replace the example GUID with the GUID of the AD view.  (Get the GUID of the view by right-clicking the view and choosing Properties, then copying down the GUID.)

Does this help?  Not sure if your group names are unique.  For example:

• Group A
• ---Workstations
• ---Servers
• Group B
• ---Workstations
• ---Servers

Would cause ambiguous query results because this doesn't breadcrumb the OG.

This is right from SQL studio, be sure to format properly for a SQL query from within Altiris.

USE Symantec_CMDB

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

[Migration User]

Hi mclemson thanks for your help,

 

I have a Organizational Group called Houston which contains 200 systems. However, when I run the SQL query I get 0 results. Any thoughts?

 

I'm open to doing this another way. I have the data (MS Applications / count) but, my only problem is dividing this by location. IE Houston has 100 Windows 2008 Servers...etc.

Is it possible to create Organizational groups based on subnets? Than run a SQL query to identify the applications and count?

How does everyone do their MS True-up?

[Migration User]

For the GUID, did you right-click on the Organizational View that has Houston under it?  If so, this query should return the organizational group name for each computer (e.g. Houston, El Paso, San Antonio, Arlington, Dallas, Corpus Christi, Austin, Plano, Fort Worth -- okay now I'm just trying to name as many Texan cities as I can think of).

With my base query proven and returning group name, you'd then incorporate the general idea into your existing query.  Does the basic query work if you confirm you're using the Organizational View GUID (not the GUID from the groups, which the query will find for you)?  If you didn't specify view by providing a GUID, you'd get duplicate results.. e.g. Joe-PC is in Group Computers (default view), and Joe-PC is in Group Houston (AD view), etc.

[Migration User]

Hi,

I think I may be doing something wrong. I tried the Houston GUID but got no results. I than tried the Default > All Resources > Asset > Network Resource > Computer GUID. But this also returned 0 results even though it has over 900 systems.

Right now, I would be happy if I can just get a simple report of all my systems organized by site or subnet. I can than atleast try to combine it with the application query.

Maybe there is something wrong with my views? All my systems are named with the site code in the begining. IE - HOUXXX or HOU-

[Migration User]

You should be right-clicking, choosing Properties, and selecting the GUID of the Organizational View, not any Organizational Groups.  Test the query I posted before trying to combine it with your query.  A view has a blue-and-green globe icon, while a group has a latitude and longitute grid globe icon.

I've attached a screenshot clarifying, just in case.

[Migration User]

Hi mclemson, I have modified the script and now can see the information. Now how would you recommend I combine this with the other SQL query?

Thanks for your help.

[Migration User]

Can you post your existing query?

[Migration User]

I combined two report queries, "Count of computers by OS & Count of Microsoft Products".

DECLARE @v1_TrusteeScope nvarchar(max)
 SET @v1_TrusteeScope = N'S-1-1-0,S-1-5-11,S-1-5-21-3720477820-511671263-1889751726-1007,S-1-5-21-94197280-668744178-145704350-21395,S-1-5-32-544'

SELECT
 arc.[DisplayName] [Microsoft Product],   
 COUNT(Distinct CAST(i.[Guid] AS varchar(40))) [Number of Computers]  
FROM
 dbo.vComputer i   
JOIN dbo.ScopeMembership sm
 ON sm.[ResourceGuid] = i.Guid
 AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
JOIN dbo.Inv_AddRemoveProgram arc  
 ON arc.[_ResourceGuid] = i.[Guid]   
JOIN dbo.CollectionMembership cm  
 ON cm.ResourceGuid = i.[Guid]  
JOIN dbo.vCollection it  
 ON it.Guid = cm.CollectionGuid  

WHERE arc.[DisplayName] LIKE '%Microsoft Office%' 
 AND i.[System Type] LIKE 'Win%'  
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%web%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('% MUI %')
 AND LOWER (arc.[DisplayName]) NOT  LIKE LOWER ('%primary%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%security%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%runtime%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%activation%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%update%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%live%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%components%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%viewer%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%programs%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%connector%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%service pack%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%trial%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%labs%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%plugin%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%proof%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%library%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%evaluation%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%sample%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%template%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%language%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%engine%') 
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%add-in%') 
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%inside%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%resource kit%')
 AND LOWER (arc.[DisplayName]) NOT LIKE LOWER ('%chinese%')
AND i.Domain LIKE '%'  
 AND LOWER (it.[Guid]) like LOWER ('2c1b4a56-4bc6-4e03-8589-fb53499b7e55') 
GROUP BY 
 arc.[DisplayName] 
  
UNION ALL   

SELECT DISTINCT o1.[Name] [Operating System],
COUNT (DISTINCT (i.Guid)) [Number of Computers]
FROM dbo.vComputer i 
JOIN dbo.ScopeMembership sm
 ON sm.[ResourceGuid] = i.Guid
 AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
JOIN dbo.vOSOperatingSystem o1 
 ON  o1.[_ResourceGuid] = i.Guid
LEFT JOIN dbo.vOSOperatingSystemWindows cop
 ON cop.[_ResourceGuid] = o1.[_ResourceGuid]
JOIN dbo.Inv_AeX_AC_Identification d 
 ON d.[_ResourceGuid] = i.Guid 
GROUP BY
 o1.[Name]
ORDER BY 1

 

How do I combine your query with mine?

[Migration User]

Feel free to modify my query, I updated it with a few more filters incase anyone else is trying to do the same.

[Migration User]

You have a lot of LOWER statements in your query. I believe SQL 2005, by default should be case insensitive, so you shouldn't need those. If I remember correctly this can slow down the processing.