SOLVED SQL query to find application version on collection

[dj3094]

Hello experts,

Can someone help me with the below requirement, I want to run report on collection lets say all workstation collection and see google chrome installed and what version it is, workstation name and active directory site. I found this

Select Distinct
sys.Netbios_Name0,
sys.User_Domain0,
sys.User_Name0,
sys.AD_Site_Name0

FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE
sys.ResourceID in (select sys.ResourceID
from
v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
where
DisplayName0 like '%AppName%'
and Version0 like 'version')

how to add collection and version

Regards
Anche

 

[dj3094]

I found inbuilt reports computers with specific software registered in add remove programs.

how to do same with wql? to create collection based on version. I mean google chrome version less than 75

 

[Edy]

search sccm wql query instead of sql query. eg like below, you would then find the table name for application version and include it on your wql query. Most likely be SMS_G_System_ADD_REMOVE_PROGRAMS.Version.

in sql V_R_System and in wql its SMS_R_....

select distinct 
      SMS_R_System.Name,   
      SMS_R_System.OperatingSystemNameandVersion,   
      SMS_R_System.ClientVersion   
 from SMS_R_System 
      inner join SMS_G_System_ADD_REMOVE_PROGRAMS 
      on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId 
      where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2010" 
      order by SMS_R_System.Name

 

[dj3094]

thanks Edy, but I ran the query for google chrome, what to add to get the version also for google chrome

 

[Edy]

Select
distinct comp.Name0 as [Machine Name],
comp.UserName0 as [User Name],
soft.ProductName0 as [Product Name],
soft.ProductVersion0 as [Product Version],
vrs.Last_Logon_Timestamp0 as [Last Logon Timestamp],
ops.Caption0 as [Operating System]
from
v_GS_COMPUTER_SYSTEM comp inner join
v_GS_OPERATING_SYSTEM ops on ops.ResourceID=comp.ResourceID inner join
v_GS_INSTALLED_SOFTWARE soft ON soft.ResourceID = comp.ResourceID inner join
v_GS_PC_BIOS bios on bios.ResourceID=comp.ResourceID inner join
v_GS_COMPUTER_SYSTEM_PRODUCT prod on comp.ResourceID=prod.ResourceID inner join
v_R_System vrs on comp.ResourceID=vrs.ResourceID inner join
v_FullCollectionMembership on vrs.ResourceID=v_FullCollectionMembership.ResourceID inner join
v_Collection on v_FullCollectionMembership.CollectionID=v_Collection.CollectionID
where soft.ProductName0 like '%Adobe flash%'
ORDER BY soft.productname0, soft.ProductVersion0