Video Screencast Help

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

Created: 20 Oct 2008 • Updated: 20 Oct 2008 • 7 comments
Eshwar's picture
+28 28 Votes
Login to vote

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

Comments 7 CommentsJump to latest comment

vake_dhingra's picture

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
Login to vote
KSchroeder's picture

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

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

+22
Login to vote
Eshwar's picture

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

Thanks,
Eshwar

Cheers,

Eshwar

+17
Login to vote
artizhino's picture

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
Login to vote
dfussell's picture

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
Login to vote
KSchroeder's picture

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

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

+20
Login to vote
aspen's picture

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

0
Login to vote