This issue needs a solution.

Altiris Report - MS Products

Created: 03 Feb 2011
Login to vote
-1 1 Vote

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.

Filed Under

Comments

03
Feb
2011

Yes, clone the reports. and

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

Cesar Echeverria Castillo

Consultant Engineer

InterLAN Ltda, Medellin, Colombia

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

03
Feb
2011

Thanks, but when I cloned the

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.

Capture.PNG
03
Feb
2011

Clic en Clone, type new name

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

See screenshot.

Cesar Echeverria Castillo

Consultant Engineer

InterLAN Ltda, Medellin, Colombia

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

mclemson
Trusted Advisor
Accredited
03
Feb
2011

To edit a report, clone it

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?

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

03
Feb
2011

Thanks all. I have managed to

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.

03
Feb
2011

which location?

which location?

Cesar Echeverria Castillo

Consultant Engineer

InterLAN Ltda, Medellin, Colombia

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

mclemson
Trusted Advisor
Accredited
03
Feb
2011

Agree

I agree.  How do you want to define location?

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

03
Feb
2011

Report

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.

03
Feb
2011

the 7 locations/sites have

the 7 locations/sites have different subnet?

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

Cesar Echeverria Castillo

Consultant Engineer

InterLAN Ltda, Medellin, Colombia

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

mclemson
Trusted Advisor
Accredited
03
Feb
2011

Do you synchronize with AD?

Are you synchronizing with Active Directory using the directory connector?

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

04
Feb
2011

Additonal Info

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.

07
Feb
2011

You could also do this fairly

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..

mclemson
Trusted Advisor
Accredited
07
Feb
2011

AD connector

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

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

08
Feb
2011

Still no results...

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?

mclemson
Trusted Advisor
Accredited
08
Feb
2011

For the GUID, did you

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.

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

08
Feb
2011

Still nothing.

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-

mclemson
Trusted Advisor
Accredited
09
Feb
2011

View GUID, not group GUID

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.

Altiris Organizational Views

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

09
Feb
2011

Got it.

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.

mclemson
Trusted Advisor
Accredited
09
Feb
2011

Post the query?

Can you post your existing query?

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

09
Feb
2011

Query

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?

09
Feb
2011

Edit..

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

jharings
Trusted Advisor
10
Feb
2011

One comment

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.

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.