Login to participate
Endpoint Management & Virtualization BlogsRSS

Report to Display List of Software Installed on Machines When a List of Usernames is Given

Eshwar's picture
Eshwar
October 20th, 2008
Filed under: , , ,

I have created the following report when I was asked to write an altiris report, to find a list of software installed on the given user’s PCs.

I was given a list of users as input to the report. Now I have to find out the list of software in each user’s PC. There are actually two reports in this.

  1. Finding primary user of the computer
  2. Display list of software on the computer

1. Finding primary user of the computer:

SELECT DISTINCT (PU.[User]), vComputer.[Name] AS 'Machine Name',
ACI.[Domain] AS 'Domain'      
FROM vComputer        
INNER JOIN [inv_aex_ac_primary_user] PU  
ON PU._ResourceGuid = vComputer.Guid        
JOIN [Inv_AeX_AC_Identification] ACI  
ON ACI.[_ResourceGuid] = vComputer.Guid   
WHERE PU.[Month] = datename(month, getdate())   
AND UPPER(PU.[User])
IN (Enter_usernames_in_single_quotes_SeparatedBy_comma) 
ORDER BY PU.[User]

2. Display list of software on the computer:

SELECT DISTINCT(vComputer.[Name]) AS 'Machine Name',
ARP.[Name] AS 'Application Name’,ARP.[Version]       
FROM vComputer        
INNER JOIN [Inv_AeX_OS_Add_Remove_Programs] ARP 
ON ARP.[_ResourceGuid] = vComputer.Guid   
WHERE UPPER(vComputer.[Name]) 
IN (Enter_machinenames_in_single_quotes_SeparatedBy_comma) 
ORDER BY vComputer.[Name]

If we combine the above reports, will get a report that displays [Primary User/Machine Name/List of software installed] on the machine. 

SELECT	DISTINCT(PU.[User]),   
 		vComputer.[Name] AS 'Machine Name',   
		ACI.[Domain] AS 'Domain',  
		ARP.[Name] AS 'Application Name',   
		ARP.[Version]         
FROM 		vComputer          
INNER JOIN 	inv_aex_ac_primary_user PU 
ON PU._ResourceGuid = vComputer.Guid          
JOIN 		[Inv_AeX_AC_Identification] ACI 
ON ACI.[_ResourceGuid] = vComputer.Guid     
JOIN 		[Inv_AeX_OS_Add_Remove_Programs] ARP 
ON ARP.[_ResourceGuid] = ACI.[_ResourceGuid]    
WHERE 	PU.[Month] = datename(month, getdate())     
AND 		UPPER(PU.[User]) IN (Enter_usernames_in_single_quotes_SeparatedBy_comma)   
ORDER BY 	PU.[User]
Note: If a user is logged into different PCs, more than one machine could appear in the report.


Symantec Juice

+28 (28 votes)
vake_dhingra's picture
vake_dhingra
1 year 2 weeks ago

How to apply the report

Should I be using this SQL code in a collection? Or should I build a report with it. I'm getting syntax errors when I try to do either.

+2 (2 votes)
KSchroeder's picture
KSchroeder
1 year 2 weeks ago

You have to replace the variable listed...

Vake,
You need to replace the string "enter list of computer names" with an appropriate list formatted as it states, i.e.:

'user1', 'user2', 'user3'

or:
'machine1', 'computer2', 'computer3'

This is the format that the IN () operator wants. It is actually preferable if you're running this on the NS console to create a parameter in your report (i.e. named _UserList) and substitute it into the report. The parameter should be set with a Parameter Type of "Basic" and a Value type of "None" so that the single quotes are passed through correctly. Then update the report code as follows:

WHERE .... IN (%_UserList%)

Thanks,
Kyle
Symantec Trusted Advisor
If your question has been resolved, please be sure to click "Mark as Solution"! Thank you.

+22 (22 votes)
Eshwar's picture
Eshwar
1 year 2 weeks ago

Thanks...

KSchroeder ,
Thank you for your help on this.
I appreciate it.

Thanks,
Eshwar

Thanks,
Eshwar

+17 (17 votes)
artizhino's picture
artizhino
33 weeks 5 days ago

That is what I was looking

That is what I was looking for. Excellent tip... I could not find why it could parse the single quotes, your tip helped to resolve my report.

thank you.

Art

+5 (5 votes)
dfussell's picture
dfussell
32 weeks 6 days ago

Hello, I made the changes in

Hello,
I made the changes in the bottom of the post so that NS could handle the parameter. My code looks like this:

WHERE  PU.[Month] = datename(month, getdate())      
AND   UPPER(PU.[User]) IN (%_UserList%)    
ORDER BY  PU.[User]
But I am still getting errors

I gave the Parameter the nmae of _UserList it is basic and the vcalue none. But I am getting this error:

Sql error in query. Error: System.Data.SqlClient.SqlError: Invalid column name 'dfussell'.Sql CommandText: -- MAX ROWCOUNT SET ROWCOUNT 50000 -- SELECT DISTINCT(PU.[User]), vComputer.[Name] AS 'Machine Name', ACI.[Domain] AS 'Domain', ARP.[Name] AS 'Application Name', ARP.[Version] FROM vComputer INNER JOIN inv_aex_ac_primary_user PU ON PU._ResourceGuid = vComputer.Guid JOIN [Inv_AeX_AC_Identification] ACI ON ACI.[_ResourceGuid] = vComputer.Guid JOIN [Inv_AeX_OS_Add_Remove_Programs] ARP ON ARP.[_ResourceGuid] = ACI.[_ResourceGuid] WHERE PU.[Month] = datename(month, getdate()) AND UPPER(PU.[User]) IN (dfussell) ORDER BY PU.[User]

I am trying to have this report allow me to enter any user I want  and return results.

+2 (2 votes)
KSchroeder's picture
KSchroeder
32 weeks 5 days ago

It looks like the parameter

It looks like the parameter is not being passed in properly; the section with 

AND UPPER(PU.[User]) IN (dfussell)

is not including the single quote marks. When you type your parameter in to the report prompt, you must type the single quotes also as above:

'dfussell', 'someuser', 'anotherguy'

Be careful if you're using Word to generate the list of userIDs; it will autoformat the single quotes with "smart quotes" (aka curly quotes) which don't parse properly in the SQL statement.

Thanks,
Kyle
Symantec Trusted Advisor
If your question has been resolved, please be sure to click "Mark as Solution"! Thank you.

+20 (20 votes)
aspen's picture
aspen
5 days 6 hours ago

Nice

This is a great little report. With some tweaking it provides essential functions for any altiris admin.

0 votes