Client Management Suite

One thing that is always in my list of saved SQL queries is the ability to search for a piece of software via Add Remove Programs. This is found in the table [Inv_AddRemoveProgram].

With this you can find many objects and open up many reporting possibilities. 

I see many requests on the forums for how to find a piece of software. With the following Article it should meet more than your needs from the Application Name to How many of a specific version and finally what machines it is installed on.

SQL Code:

DISTINCT VERSIONS

-- shows count of all versions on that host
Select Distinct t0.[DisplayName] as ' Application', t0.[displayVersion] as 'Version'
From [Inv_AddRemoveProgram] t0 
    join Inv_AeX_AC_Identification t1 on t0._ResourceGuid = t1._ResourceGuid
--Change below line to find app and also you can specify a version to show just them
where [displayName] like '%AppName%' and t0.DisplayVersion like '%'
order by t0.[DisplayName], t0.[displayVersion]

Software Count Code:

-- shows count of all versions
Select t0.[DisplayName] as ' Application', t0.[displayVersion] as 'Version', Count(t0.[displayName])  as 'Count'
From [Inv_AddRemoveProgram] t0 
--Change below line to find app
where [displayName] like '%AppName%'
Group by t0.[displayName], t0.[displayVersion]

Software by Machine Code:

-- shows count of all versions on that host
Select Distinct t1.Name, t0.[DisplayName] as ' Application', t0.[displayVersion] as 'Version', t1._ResourceGuid
From [Inv_AddRemoveProgram] t0 
    join Inv_AeX_AC_Identification t1 on t0._ResourceGuid = t1._ResourceGuid
--Change below line to find app and also you can specify a version to show just them
where [displayName] like '%AppName%' and t0.DisplayVersion like '%AppVer%'
order by t1.Name

Reports:

Report for DISTINCT VERSIONS

1. From Report section of the Console right click and choose New> Report> SQL Report
2. Click Report Parameters Tab
3. Click Add> New Paramater
4. Name: AppName
5. Description: Software Name:
6. Default Value: %
7. Value Provider: Click dropdown and choose Basic Parameter Value Edit Control
8. Click Ok
9. Click Data Source Tab
10. Click Query Parameters Tab
11. Click Add and choose Software Name:
12. Click Parameterised Query and paste in SQL code for Distinct versions from above
13. Give it a name: Distinct Software and Versions
14. Report can now be ran and you can select software name to shorten the list

Report for Software Count Code

1. From Report section of the Console right click and choose New> Report> SQL Report
2. Click Report Parameters Tab
3. Click Add> New Paramater
4. Name: AppName
5. Description: Software Name:
6. Default Value: %
7. Value Provider: Click dropdown and choose Basic Parameter Value Edit Control
8. Click Ok
9. Click Data Source Tab
10. Click Query Parameters Tab
11. Click Add and choose Software Name:
12. Click Parameterised Query and paste in SQL code for Software Count from above
13. Give it a name: Software Count 
14. Report can now be ran and you can select software name to shorten the list

Next report utilizes the Distinct Software and Version report

 Software by Machine:

1. From Report section of the Console right click and choose New> Report> SQL Report
2. Click Report Parameters Tab
3. Click Add> New Paramater
4. Name: AppName
5. Description: Software Name:
6. Default Value: %
7. Value Provider: Click dropdown and choose Basic Parameter Value Edit Control
8. Click Ok
9. Click Add> New Paramater
10. Name: AppVer
11. Description: Application Version:
12. Default Value: %
13. Value Provider: Click dropdown and choose Basic Parameter Value Edit Control
14. Click Ok
15. Click Data Source Tab
16. Click Query Parameters Tab
17. Click Add and choose Software Name:
18. Click Add and choose Application Version:
19. Click Parameterised Query and paste in SQL code for Software by Machine from above
20. Click Drilldowns Tab
21. Click Remove
22. Click Add
23. Name: ShowContextMenu
24. Event: choose Right Click
25. Performs: Show Context Menu
26. Action Configuration: select _ResourceGuid
27. Check off Display additional resource tool items in the menu
28. Give the report a name:  Software by Machine 
29. Report can now be ran and you can select software name to shorten the list. Last section (ShowContextMenu) allows right click menu for the PC

Hope these 3 reports can help you too.

You can take this even further by creating a report that shows an application and all versions.

Then take that and use that version field as a dynamic dropdown list.

We do this for certain Applications. It is a bit more of a setup as you have to create 2 reports for each app but in the long run when a new version inventories in it will automatically get added to the Version dropdown list. 

If you would like to see that one done let me know and I will add it too.

Thanks for viewing...

TeleFragger