Video Screencast Help
Search Video Help Close Back
to help

Add/Remove Programs Compressed Reporting

Created: 20 Apr 2010 | Updated: 24 Aug 2010 | 4 comments
Broadway's picture
Broadway
0 0 Votes
Login to vote
This issue has been solved. See solution.

I'm looking to clone the Add/Remove Programs Report in NS6 to compress it to what would be shown in the Add/Remove Programs GUI of Windows.  The default report lists everything that is in the Registry which is too much information than what I am looking for.  Is there a way to clone the report and modify it to remove all the programs that are hidden in the GUI?  Something along the lines of Hidden = False of the SQL?

Discussion Filed Under:
, , , , ,

Comments 4 Comments • Jump to latest comment

dfrancis's picture
dfrancis Accredited
Add/Remove Programs Compressed Reporting - Comment:20 Apr 2010 : Link

I haven't cloned it myself, but in the where clause if you add

and table.Hidden = 'False'

that should filter out all of the hidden apps.  Make sure you look at the query to see what name they gave Inv_AeX_OS_Add_Remove_Programs.  It's usually t0, t1, or something along those lines.  Once you know what that is, replace "table" in the above clause with that and you should be good to go.

--Dave

If a forum post solves your problem, please flag it as a solution. If you like an article, blog post or download vote it up.
+4
Login to vote
  • Actions
Broadway's picture
Broadway
Add/Remove Programs Compressed Reporting - Comment:20 Apr 2010 : Link

Here is what I'm working with:

SELECT DISTINCT  

car.[Name] [Application Name], 
car.[Version], 
car.[Publisher] 
FROM dbo.vComputer i 
JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1 
ON a1.[_ResourceGuid] = i.Guid 
JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car 
ON car.[_KeyHash] = a1.[_KeyHash] 
JOIN dbo.Inv_AeX_AC_Identification d 
ON d.[_ResourceGuid] = i.Guid 
JOIN dbo.CollectionMembership cm 
ON cm.ResourceGuid = d.[_ResourceGuid] 
JOIN dbo.vCollection it 
ON it.Guid = cm.CollectionGuid 
WHERE car.[Name] LIKE '%ApplicationName%' 
AND d.[System Type] LIKE 'Win%' 
AND i.[Name] LIKE '%ComputerName%' 
AND d.Domain LIKE '%Domain%' 

AND it.[Guid] = '%Collection%'

and

SELECT DISTINCT    

i.[Name] [Name],  
car.[Name] [Application Name],  
car.[Version],  
car.[Publisher]  
FROM dbo.vComputer i  
JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1  
ON a1.[_ResourceGuid] = i.Guid  
JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car  
ON car.[_KeyHash] = a1.[_KeyHash]  
JOIN dbo.Inv_AeX_AC_Identification d  
ON d.[_ResourceGuid] = i.Guid  
JOIN dbo.CollectionMembership cm  
ON cm.ResourceGuid = d.[_ResourceGuid]  
JOIN dbo.vCollection it  
ON it.Guid = cm.CollectionGuid  
WHERE REPLACE(REPLACE(car.[Name], '[', ''), ']', '') LIKE REPLACE(REPLACE('%Application Name%', '[', ''), ']', '')  
AND d.[System Type] LIKE 'Win%'  
AND i.[Name] LIKE '%ComputerName%'  
AND d.Domain LIKE '%Domain%'  
AND it.[Guid] = '%Collection%' 
AND car.[Version] LIKE '%Version%'

What kind of changes would I be looking for?   SQL is not a strength at all of mine.

0
Login to vote
  • Actions
dfrancis's picture
dfrancis Accredited
Add/Remove Programs Compressed Reporting - Comment:20 Apr 2010 : Link

Modified queries below for you.

Altiris never wrote nice looking queries for their canned reports, so it's sometimes a bear trying to understand everything they're doing.  In this situation they named the Add/Remove Programs table "car" so I just added the and car.[Hidden] = 'False' to suppress the hidden results from showing up.  If you want to replace the queries in your cloned report with the ones below, run it and the original report side by side for just one computer and you'll see what it did.


SELECT DISTINCT  

car.[Name] [Application Name], 
car.[Version], 
car.[Publisher] 
FROM dbo.vComputer i 
JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1 
ON a1.[_ResourceGuid] = i.Guid 
JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car 
ON car.[_KeyHash] = a1.[_KeyHash] 
JOIN dbo.Inv_AeX_AC_Identification d 
ON d.[_ResourceGuid] = i.Guid 
JOIN dbo.CollectionMembership cm 
ON cm.ResourceGuid = d.[_ResourceGuid] 
JOIN dbo.vCollection it 
ON it.Guid = cm.CollectionGuid 
WHERE car.[Name] LIKE '%ApplicationName%' 
AND d.[System Type] LIKE 'Win%' 
AND i.[Name] LIKE '%ComputerName%' 
AND d.Domain LIKE '%Domain%' 

AND it.[Guid] = '%Collection%'
AND car.[Hidden] = 'False'

and

SELECT DISTINCT    

i.[Name] [Name],  
car.[Name] [Application Name],  
car.[Version],  
car.[Publisher]  
FROM dbo.vComputer i  
JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1  
ON a1.[_ResourceGuid] = i.Guid  
JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car  
ON car.[_KeyHash] = a1.[_KeyHash]  
JOIN dbo.Inv_AeX_AC_Identification d  
ON d.[_ResourceGuid] = i.Guid  
JOIN dbo.CollectionMembership cm  
ON cm.ResourceGuid = d.[_ResourceGuid]  
JOIN dbo.vCollection it  
ON it.Guid = cm.CollectionGuid  
WHERE REPLACE(REPLACE(car.[Name], '[', ''), ']', '') LIKE REPLACE(REPLACE('%Application Name%', '[', ''), ']', '')  
AND d.[System Type] LIKE 'Win%'  
AND i.[Name] LIKE '%ComputerName%'  
AND d.Domain LIKE '%Domain%'  
AND it.[Guid] = '%Collection%' 

AND car.[Version] LIKE '%Version%'
AND car.[Hidden] = 'False'

--Dave

If a forum post solves your problem, please flag it as a solution. If you like an article, blog post or download vote it up.
SOLUTION
+2
Login to vote
  • Actions
Broadway's picture
Broadway
Add/Remove Programs Compressed Reporting - Comment:20 Apr 2010 : Link

Perfect!  That is exactly what I was looking for.  Thanks dfrancis!

0
Login to vote
  • Actions