0

I have to make a query in sccm to get all Clients with a specific Software installed. But i have no idea how to get this Job done. I've tried this on but, it's not accurate enough.

select SMS_G_System_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "program.exe" and SMS_G_System_SoftwareFile.FileVersion > "version" and SMS_R_System.Client = 1

I would like to check the programs in the control Panel. I have this tried with this query:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System  inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId  where SMS_G_System_COMPUTER_SYSTEM.Name not in  (select distinct SMS_G_System_COMPUTER_SYSTEM.Name  from SMS_R_System  inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId  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 like "%program%")

But this one gives me no result.

Thanks for the help!

N.R.
  • 230
  • 3
  • 11

1 Answers1

0

SCCM's built in reporting has a super easy way to see all systems with a particular application installed on them. Would that not work for you?

If you need a query for it, here's an example based on Google Chrome

select SCCM.Name0
,CAT.[ProductName0]
,CAT.[ProductVersion0]
,CAT.[Publisher0]
,CAT.[NormalizedName]</code>


FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED as CAT
join v_R_System as SCCM on CAT.ResourceID = SCCM.ResourceID
where CAT.[ProductName0] like '%Google Chrome%'

This will return the name of the system, and then a bunch of info about the application.

FoxDeploy
  • 12,569
  • 2
  • 33
  • 48