Help Creating a Software Inventory with Primary Login

Pacman_1905 · 06-10-2009, 02:28 AM

Hi All

I have spent some time looking through other posts but I didnt find anything that could help me here.

I'm fairly new to ALtiris and begun to expose myself with the wonderful world of report writing and SQL. I am trying to generate a report that displays all computers that have Adobe Photoshop installed. This works fine. However when I try to add a Primary User coumn to the report the number of rows increase 2 or 3 fold. It appears that the rimary User column duplicates the number of products found.

Im using the simple report builder and the SQL I want to add a user to is displayed here:

SELECT T0.[Name] AS 'Name', T1.[Name] AS 'Name', T0.[Guid] FROM [vResourceEx] T0 INNER JOIN [Inv_AeX_OS_Add_Remove_Programs] T1 ON T0.[Guid] = T1.[_ResourceGuid] WHERE T1.[Name] like 'Adobe Photoshop%' AND T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f'

Could anybody assist with adding a Primary User next to each of the rows returned?

Much appreciated!

gwilmington · 06-10-2009, 01:28 PM

This is what I came up with.

Code:

SELECT DISTINCT 
                      AeXInv_AeX_AC_Primary_User.[User] AS Username, AeXInv_AeX_AC_TCPIP.[Host Name] AS [Computer Name], 
                      AeXInv_AeX_OS_Add_Remove_Programs.Name AS [Program Name]
FROM         AeXInv_AeX_AC_Primary_User INNER JOIN
                      AeXInv_AeX_AC_TCPIP ON AeXInv_AeX_AC_Primary_User.WrkstaId = AeXInv_AeX_AC_TCPIP.WrkstaId INNER JOIN
                      AeXInv_AeX_OS_Add_Remove_Programs ON AeXInv_AeX_AC_Primary_User.WrkstaId = AeXInv_AeX_OS_Add_Remove_Programs.WrkstaId
WHERE     (AeXInv_AeX_OS_Add_Remove_Programs.Name LIKE N'%Photoshop%')

Remember, if you have primary user as an output that you need distinct because primary user is recalculated every month or you need to add the Month column, deselect it as an output and just hard code it with the current month. If you're users change machines frequently then you'll want to put in a month. If they don't, you still may get a few duplicates if they have more than one machine with the application installed.

Pacman_1905 · 06-10-2009, 10:30 PM

Oh thats brilliant this SQL query worked a treat. Much appreciated!

Cheers