Collections are basement of SCCM administration. I want to share some useful WQL for get more detailed environment.
Enjoy!
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.