Wednesday, July 10, 2013

SCCM SQL Query: Multiple program versions in Add/Remove (with Publisher and full Username)

Management needed me to put together a SQL report for multiple versions of the same application. This particular case proved tricky because the logic involved including an "or" for the application versions and an "and" for the publisher in the "where" section. This query did the trick for me:

select distinct SYS.Name0 as 'Machine Name', SYS.User_Name0 as 'Login ID', SYS.User_Domain0 as 'Domain', USR.Full_User_Name0 as 'Full Name', ARP.DisplayName0 as 'Product Name', ARP.Publisher0 as 'Publisher', ARP.Version0 as 'Version' from v_R_System SYS join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID join v_Collection COL on FCM.CollectionID = COL.CollectionID join v_R_User USR on SYS.User_Name0 = USR.User_Name0 join v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID where COL.Name = 'All Systems' and ARP.Publisher0 = 'Solidworks Corporation' and ( ARP.DisplayName0 like 'SolidWorks 2010%' or ARP.DisplayName0 like 'SolidWorks 2011%' or ARP.DisplayName0 like 'SolidWorks 2012%' )
Hope you find it useful.

No comments:

Post a Comment