Find systems with software installed or missing

Examples of WQL search for systems with or without a particular software version installed. Internet Explorer used for example.

[WQL code]
===[ use a subquery to find the negative (not installed ]=============================================
===[ Find system WITHOUT Internet Explorer 9.x ]===
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 
where SMS_R_System.ResourceId
not in (
select SMS_R_System.ResourceId from SMS_R_System 
inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
 where SMS_G_System_SoftwareFile.FileName = "iexplore.exe" and SMS_G_System_SoftwareFile.FileVersion like "9.%"
 )

===[ Find all copies of Internet Explorer on all systems ]===
select distinct 
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_G_System_SoftwareFile.FileName,
SMS_G_System_SoftwareFile.FileVersion,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client
from SMS_R_System 
join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SoftwareFile.FileName = "iexplore.exe" 
and 
(SMS_G_System_SoftwareFile.FileVersion like "9.%"
or SMS_G_System_SoftwareFile.FileVersion like "10.%"
)
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s