Tag: WQL

Find all PC with HW and SW inventory dates greater than 180 days

This WQL query will show you all PCs with a HW and SW Scan date of greater than 180 days. select SMS_R_System.Name, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId where […]

Finding all PCs with MSAcess.exe in the All Systems collection

Use this query to find all PCs with MS Access in the All systems collection.   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_FullCollectionMembership on SMS_FullCollectionMembership.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = “msaccess.exe” and SMS_FullCollectionMembership.CollectionID = ‘SMS00001’

IE7 Collection

For full detail please see forum post   select * from SMS_R_System left join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = “ie7” or SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID = “ie7”

Query for computers with single partition

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/4d84e5f9-fd48-4fcb-b490-d0a64052aa08 select SMS_R_System.Name from SMS_R_System where SMS_R_System.ResourceId not in ( select SMS_G_System_PARTITION.ResourceId from SMS_G_System_PARTITION where SMS_G_System_PARTITION.DeviceID = “Disk #0, Partition #1” )

How to exclude a collection from sccm collection query

For full details, see this forums post. http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/75288b97-1fe6-4634-a20a-54091bba8d91   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 “%\\Program Files\\Internet Explorer\\” or SMS_G_System_SoftwareFile.FilePath like “%\\Program\\Internet Explorer\\”) and SMS_G_System_SoftwareFile.FileName = “iexplore.exe” and SMS_G_System_SoftwareFile.FileVersion like “7.%” and SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_SMS000ES)

SCCM Query for Memory & CPU

See Forum post for fill details. select distinct SMS_R_System.Name, SMS_R_System.Client, SMS_R_System.LastLogonUserName, SMS_R_System.ADSiteName, SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, SMS_G_System_PROCESSOR.NormSpeed from SMS_R_System inner join SMS_G_System_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_PROCESSOR on SMS_G_System_PROCESSOR.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 is like “Adobe Reader X%” and SMS_R_System.Client = 1

WQL Query for both x86 and x64 version of Software

  select distinct SMS_R_System.Name, SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.ResourceId in ( select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Lync 2010”) or SMS_R_System.ResourceId in ( select SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS_64 where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = “Microsoft Lync 2010”)

Collection by Gateway

select     SMS_R_System.Name from      SMS_R_System     inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceId = SMS_R_System.ResourceId where     SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway = “192.168.1.1”

Client Version and model

select     SMS_R_System.Name,     SMS_R_System.ResourceId,     SMS_G_System_COMPUTER_SYSTEM.Manufacturer,     SMS_G_System_COMPUTER_SYSTEM.Model from      SMS_R_System     inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where     SMS_R_System.ClientVersion = “4.00.6487.2000”     and SMS_G_System_COMPUTER_SYSTEM.Model = “Virtual Machine”