:::: MENU ::::
Browsing posts in: SCCM Collections

%Microsoft Visio Professional%


 

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "%Microsoft Visio Professional%"

 


How to find SCCM 2012 collection location

<strong>select c.SiteID as 'Collection ID',c.CollectionName,f.Name </strong>
<strong>as 'Folder Name' from vCollections c</strong>
<strong>inner join FolderMembers fm on fm.InstanceKey=c.SiteID</strong>
<strong>inner join folders f on f.ContainerNodeID=fm.ContainerNodeID</strong>

List of Users to add in a SCCM User Collection

 

select SMS_R_USER.ResourceID,SMS_R_USER.ResourceType,SMS_R_USER.Name,SMS_R_USER.UniqueUserName,SMS_R_USER.WindowsNTDomain from SMS_R_User where SMS_R_User.UserName in (‘User1′,’User2’)


SCCM Client Health Related Collections

Collection Name WQL Query
CCMEval – No Status 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 resourceid in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result = 1 and DATEDIFF(day,SMS_CH_EvalResult.EvalTime,GetDate())<=7)
CCMEval – Failed Status 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 resourceid in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result in (3,4,5) and DATEDIFF(day,SMS_CH_EvalResult.EvalTime,GetDate())<=7)
Client Installed but Not Approved for Use 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 in (select ResourceID from SMS_FullCollectionMembership where IsApproved = 0 or IsApproved IS NULL) and SMS_R_System.ResourceId in (select SMS_R_SYSTEM.ResourceID from SMS_R_System Where SMS_R_System.Client = 1)
Client Not Installed and Not Assigned* 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 where SMS_R_System.Client = 1) and SMS_R_System.SMSAssignedSites is null  and SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where AgentName in (“SMS_AD_SYSTEM_DISCOVERY_AGENT”) and DATEDIFF(day,AgentTime,GetDate())<7)
Client Not Installed* 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 where SMS_R_System.Client = 1) and SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where AgentName in (“SMS_AD_SYSTEM_DISCOVERY_AGENT”) and DATEDIFF(day,AgentTime,GetDate())<7)
Client Not Installed and Error 112 No Disk Space during Client Push* 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.Name in (Select   InsStrValue from SMS_StatusMessage as stat   left outer join SMS_StatMsgAttributes as att   on stat.recordid = att.recordid   left outer join SMS_StatMsgInsStrings as ins   on stat.recordid = ins.recordid   WHERE (COMPONENT=’SMS_CLIENT_CONFIG_MANAGER’)   AND MessageID = 3014  AND Win32Error = 112 AND DATEDIFF(day,stat.Time,GetDate())<7) and SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1)
Client Not Installed and Error 5 Access Denied during Client Push* 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.Name in (Select   InsStrValue from SMS_StatusMessage as stat   left outer join SMS_StatMsgAttributes as att   on stat.recordid = att.recordid   left outer join SMS_StatMsgInsStrings as ins   on stat.recordid = ins.recordid   WHERE (COMPONENT=’SMS_CLIENT_CONFIG_MANAGER’)   AND MessageID = 3014  AND Win32Error = 5 AND DATEDIFF(day,stat.Time,GetDate())<7) and SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1)
PFE Remediation Script – Agent Not Installed 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 ResourceID from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <14) and AgentName = ‘PFE Remediation’))
PFE Remediation Script – Outdated Script* 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.PFE_ScriptVer != ‘14.7.3’ and ResourceID in ( select ResourceID from SMS_R_SYSTEM where DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <7 and AgentName = ‘PFE Remediation’)
HINV Missing Information 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 is null and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
HINV Missing Information 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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like “%Windows%”) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
Duplicate GUID* 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 in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where (SMS_R_System.Name != SMS_G_System_SYSTEM.Name and SMS_G_System_SYSTEM.Name is not null )) and SMS_R_System.ResourceId in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1)
HINV Last Scan Date Missing or Greater Than 30 Days* 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_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where DATEDIFF(day,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate())<30) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
Software Updates Compliance Status Missing 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_UpdateComplianceStatus on SMS_UpdateComplianceStatus.machineid = sms_r_system.resourceid   Join SMS_Softwareupdate on  SMS_Softwareupdate.CI_ID = SMS_UpdateComplianceStatus.CI_ID  where   SMS_UpdateComplianceStatus.Status = 0 or SMS_UpdateComplianceStatus.Status = 1 or SMS_UpdateComplianceStatus.Status = 2 or SMS_UpdateComplianceStatus.Status = 3) and SMS_R_System.ResourceId in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1)
HINV MIF Max File Size Exceeded 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.Name in (select SMS_StatusMessage.MachineName from SMS_StatusMessage LEFT OUTER JOIN SMS_StatMsgAttributes ON SMS_StatusMessage.RecordID = SMS_StatMsgAttributes.RecordID LEFT OUTER JOIN SMS_StatMsgInsStrings ON SMS_StatusMessage.RecordID = SMS_StatMsgInsStrings.RecordID where SMS_StatusMessage.MessageID = ‘2719’ AND  SMS_StatusMessage.Component = ‘SMS_INVENTORY_DATA_LOADER’ and DateDiff(dd,SMS_StatusMessage.Time, GetDate()) <7)
SINV Incomplete Inventory 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 = “notepad.exe”) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
SINV Last Scan Date Missing or Greater Than 30 Days 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 ResourceID from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) < 30) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
Client Heartbeat Missing or Greater Than 14 days* 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 in (select ResourceID from  SMS_R_System where (DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >14) and AgentName = ‘Heartbeat Discovery’) and SMS_R_System.ResourceId NOT in (select ResourceID from  SMS_R_System where (DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <14) and AgentName = ‘Heartbeat Discovery’) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
Client Activity = Inactive* 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_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0
CM Client Version Outdated* 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.ClientVersion < “5.00.8239.1000” and SMS_R_System.ResourceId in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1)
Clients Not Receiving Policy (Invalid Signature) 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.SMSUniqueIdentifier in (select instr.insstrvalue from SMS_StatusMessage as sm join SMS_StatInsStr as instr on sm.recordid=instr.recordid where sm.messageid = 5448 and instr.insstrvalue like ‘GUID%’ and DateDiff(dd,sm.Time, GetDate()) < 7)
CI – DP Latency Threshold Exceeded 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – DP Latency Threshold” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – CCM Cache Size Configured Incorrectly 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – CCM Cache Size” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – CCMEVAL – No Recent Execution 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – CCMEVAL Recent Execution” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
Unsupported Domain or Workgroup 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.ResourceDomainORWorkgroup not in (“Energy”,”RegencyGas”,”SECHOU”,”ETC”,”PVR”,”EagleRock”,”PVRPartners”) and SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <=7) and AgentName = “Heartbeat Discovery”))
CI – Admin$ Share is Not Accessible 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – Admin$ Is Accessible” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – BITS Throttling – Not Configured Properly 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId inner join SMS_CI_ComplianceHistory on SMS_CI_ComplianceHistory.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – BITS Throttling Policy” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_CI_ComplianceHistory.ComplianceStartDate,GetDate())<=7
CI – Group Policy Processing Issues 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – GPO Processing Check” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Pending Reboot (Pending File Rename Operation)* 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – No Pending Reboot Due To File Rename Operation” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Pending Reboot (Software Updates)* 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – No Pending Reboot Due To Software Updates” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Windows Update Scan Errors* 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – Windows Update Scan” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Service Status – Microsoft Anti-Virus Not Running 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – Service Status – Microsoft Anti-Virus Running” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-CompliantCustomerSpecific” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Bitlocker Encryption Not Enabled on C: 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – BitLocker Encryption Status” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Anti-Spyware State Not Recommended 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Anti-Spyware State” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Anti-Virus State Not Recommended 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Anti-Virus State” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Auto-Update Settings Not Recommended 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Auto-Update Settings” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Firewall State Not Recommended 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Firewall State” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Internet Settings Not Recommended 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Internet Settings” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – User Account Control (UAC) Settings Not Recommended 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – User Account Control (UAC) Settings” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Windows Security Center Service State Not Running 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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Windows Security Center Service (WSC) State” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7

Collection based on AD Group

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 SystemGroupName = “ZZZ\\IND-XXXXX”


Collection based on Heartbeat “N” number of days

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 ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=14) and AgentName = “Heartbeat Discovery”)


List of Machines to add in a SCCM Collection

When we have the machines in an Excel or handy we need to create a collection without using Right-click tools or any other third-party tools by using below WQL query we can create the 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 where SMS_R_System.name in(‘machine01′,’machine02’)


Please use the below query for creating the collection for Re-run advertisements based on scheduled re-occurrence.

Please use the below query for creating the collection for Re-run advertisements based on scheduled re-occurrence (This query will remove re-run advertisement installation succeeded machines from collection automatically)

 

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_ClientAdvertisementStatus JOIN

SMS_R_System ON SMS_R_System.ResourceID = SMS_ClientAdvertisementStatus.ResourceID

WHERE (SMS_ClientAdvertisementStatus.AdvertisementID =’Master Advertisement ID’)

 

AND

(SMS_ClientAdvertisementStatus.LastStateName=’Failed’

OR

SMS_ClientAdvertisementStatus.LastStateName=’Accepted – No Further Status’

OR

SMS_ClientAdvertisementStatus.LastStateName= ‘Retrying’

OR

SMS_ClientAdvertisementStatus.LastStateName= ‘Waiting’)

AND

SMS_R_System.ResourceID NOT IN(SELECT SMS_ClientAdvertisementStatus.ResourceID FROM SMS_ClientAdvertisementStatus

WHERE SMS_ClientAdvertisementStatus.AdvertisementID =’Re-run Advertisement ID’

AND SMS_ClientAdvertisementStatus.LastStateName =’Succeeded’)


Collection Query Based on Advertisement Last staus

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_ClientAdvertisementStatus join SMS_R_System on SMS_R_System.ResourceID = SMS_ClientAdvertisementStatus.ResourceID where SMS_ClientAdvertisementStatus.AdvertisementID in(‘A0120005’) and SMS_ClientAdvertisementStatus.LastStateName In(‘Succeeded’)