Home > Collection, Genel, General, SCCM, WQL > Useful 10 WQLs Query for SCCM Collections #1

Useful 10 WQLs Query for SCCM Collections #1

 

Collections are basement of SCCM administration. I want to share some useful WQL for get more detailed environment.

Enjoy!

sccm-logo

Domain = Microsoft,

CPU = x64,

Computer names start with MS or EU ,

Windows 7 installed,

Only SCCM Client Agent installed machines,

 

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_PROCESSOR on SMS_G_System_PROCESSOR.ResourceId = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion = “Microsoft Windows NT Workstation 6.1” and SMS_G_System_PROCESSOR.AddressWidth = 64 and SMS_R_System.Client = “1” and SMS_R_System.ResourceDomainORWorkgroup = “Microsoft” and (SMS_R_System.Name like “MS%” or SMS_R_System.Name like “EU%”)

 

—————————————————————————————————————————————–

 

64 bit

Windows 7

Intel 6300 wireless

14.x.x version

 

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PROCESSOR on

SMS_G_System_PROCESSOR.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FilePath like “%Windir%\\System32\\drivers\\” and

SMS_G_System_SoftwareFile.FileName like “NETwNs64.sys” and

SMS_G_System_SoftwareFile.FileVersion like “14.1.1.13” and

SMS_G_System_OPERATING_SYSTEM.Version like “6.1%” and

SMS_G_System_PROCESSOR.AddressWidth = 64

 

—————————————————————————————————————————————–

 

Internet explorer 8

Windows 7

 

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FilePath like “%\\Program Files\\Internet Explorer\\” and SMS_G_System_SoftwareFile.FileName like “iexplore.exe” and SMS_G_System_SoftwareFile.FileVersion like “8.%” and SMS_G_System_OPERATING_SYSTEM.Version like “6.1%”

 

—————————————————————————————————————————————–

 

Internet explorer 9

Windows 7

 

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceID inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_SoftwareFile.FilePath like “%\\Program Files\\Internet Explorer\\” and SMS_G_System_SoftwareFile.FileName like “iexplore.exe” and SMS_G_System_SoftwareFile.FileVersion like “9.%” and SMS_G_System_OPERATING_SYSTEM.Version like “6.1%”

 

—————————————————————————————————————————————–

 

Machine model 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.Model = “4242PE7”

 

—————————————————————————————————————————————–

 

Wireless driver versiou 14.x.x

CPU x64

 

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PROCESSOR on SMS_G_System_PROCESSOR.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FilePath like “c:\\windows\\System32\\” and SMS_G_System_PROCESSOR.Is64Bit = 1 and SMS_G_System_SoftwareFile.FileName like “NETwNc64.dll” and SMS_G_System_SoftwareFile.FileVersion like “14.%”

 

—————————————————————————————————————————————–

 

Wireless driver version 13.x.x

CPU x86

 

NETw5c32.dll

 

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PROCESSOR on SMS_G_System_PROCESSOR.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FilePath like “c:\\windows\\System32\\” and SMS_G_System_SoftwareFile.FileName like “NETw5c32.dll” and SMS_G_System_SoftwareFile.FileVersion like “13.%” and SMS_G_System_PROCESSOR.AddressWidth = 32

 

—————————————————————————————————————————————–

 

Computer model

 

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.Model = “HP Compaq dc7800 Convertible Minitower”  or      SMS_G_System_COMPUTER_SYSTEM.Model = “HP Compaq dc7800p Convertible Minitower” or  SMS_G_System_COMPUTER_SYSTEM.Model = “HP Compaq dc7800 Small Form Factor”  or      SMS_G_System_COMPUTER_SYSTEM.Model = “HP Compaq dc7800p Small Form Factor”

 

—————————————————————————————————————————————–

 

Check the file exist on selected machines

 

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 = “EpePcEncryptionProviderPlugin.dll”) and SMS_R_System.NetbiosName not like “%777” and SMS_R_System.NetbiosName not like “%616” and SMS_R_System.NetbiosName not like “%AUT%” and SMS_R_System.NetbiosName not like “%AKM%” and SMS_R_System.NetbiosName not like “%SSM%” and   SMS_R_System.NetbiosName not like “%EGT%” and SMS_R_System.LastLogonUserName not like “KR%” and SMS_R_System.LastLogonUserName not like “TT%” and SMS_R_System.LastLogonUserName not like “KL%” and SMS_R_System.LastLogonUserName not like “TE%” and SMS_R_System.LastLogonUserName not like “TK%”

 

—————————————————————————————————————————————–

 

Check the file exist ***** Working

 

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 = “cmproxy.dll”)

 

BONUS **** NOT WORKING

Tips for now working WQL. You can try.

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_SoftwareFile.FilePath like “C:\\ProgramData\\Microsoft\\Network\\Connections\\Cm\\%” and SMS_G_System_SoftwareFile.FileName != “cmproxy.dll”) or (SMS_G_System_SoftwareFile.FilePath like “C:\\Users\\%” and SMS_G_System_SoftwareFile.FileName != “cmproxy.dll”)

 

Thanks.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: