:::: MENU ::::

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


Report to list of all users laptops

SELECT distinct dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name],
dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type],
dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
FROM dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN
dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ‘8’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ‘9’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’10’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’11’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’12’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’14’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’18’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’21’)


SCCM Collections WQL Query—–Include or Exclude other collections

select distinct c.name as [Collection Name],
c.collectionid,
cdepend.SourceCollectionID as ‘Collection Dependency’,
cc.Name as ‘Collection Dependency Name’,
Case When
cdepend.relationshiptype = 1 then ‘Limited To ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
when cdepend.relationshiptype = 2 then ‘Include ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
when cdepend.relationshiptype = 3 then ‘Exclude ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
end as ‘Type of Relationship’
from v_Collection c
join vSMS_CollectionDependencies cdepend on cdepend.DependentCollectionID=c.CollectionID
join v_Collection cc on cc.CollectionID=cdepend.SourceCollectionID
where c.CollectionID = ‘XXX0077A’


All application list

 

select * from fn_ListLatestApplicationCIs(1033)

select DateCreated, DateLastModified, DisplayName, Manufacturer, SoftwareVersion, CreatedBy, LastModifiedBy 

from fn_ListLatestApplicationCIs(1033)
GROUP BY DateCreated,DateLastModified,DisplayName,Manufacturer,SoftwareVersion,CreatedBy, LastModifiedBy
ORDER BY DateCreated DESC

 

For deployment types ::: fn_ListDeploymentTypeCIs(1033)


All “Microsoft Office%” Versions

SELECT DISTINCT
c.Name0 AS [Machine Name], a.DisplayName0 AS [ARP Entry], a.Version0 AS Version, c.Operating_System_Name_and0, dbo.v_GS_OPERATING_SYSTEM.Caption0,
c.User_Domain0, c.User_Name0, c.AD_Site_Name0
FROM dbo.v_GS_ADD_REMOVE_PROGRAMS AS a INNER JOIN
dbo.v_R_System AS c ON a.ResourceID = c.ResourceID INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON c.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
WHERE (a.DisplayName0 LIKE ‘Microsoft Office%’) AND (NOT (a.DisplayName0 LIKE ‘%update%’)) AND (NOT (a.DisplayName0 LIKE ‘%Microsoft Office XP Web Components’))
AND (NOT (a.DisplayName0 LIKE ‘%server%’))


All OS with Versions

 

 

SELECT DISTINCT
dbo.v_R_System.Netbios_Name0 as [Machine Name],dbo.v_R_System.User_Name0 as [User Name],dbo.v_R_System.AD_Site_Name0 as [AD Site],
dbo.v_R_System.User_Domain0 as [Domain]
, dbo.v_GS_OPERATING_SYSTEM.Caption0 as [OS Name],
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 as [SP Name], dbo.v_R_System.Operating_System_Name_and0 as [OS NT Version],dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 as [Build Number]
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID


Use of Detection Method In App Model

Detection methods allow the administrator to check software installs to ensure that the application is not already installed. It can also prevent an install of an application if it conflicts with another application that is already installed.

Usually in our batch file we put the query method to detect the new version or the old version. Here in the App Model we have Detection Method which we can use while creating the package in App-model.

 

Scenario

I want to install Microsoft .Net 4.5 on my machine and it should upgrade if the machine is having any old version of .Net

  • In General tab of the Deployment Type Page Select Script Installer.

Note: For Files like .exe,.vbs,.cmd we have to choose Script Installer.

 

Detection

 

  • In Program Page browse the file with the switches.

Detection.jpg

 

  • In Detection Page Click on Add Clause Button on the right side of the Panel.

Detection2

 

 

Registry

  • For detecting the new version of the application, here we can choose Registry in Setting Type.

DetectionRule

 

  • Expand the Registry and choose the Microsoft .Net 4.5 Product Code and Click on OK.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\ProductCode

 

detection_reg

reg

 

  • Select the Data Type as String and click OK

Detection Rule

  • Set the below User Experience field and click OK.Detection Rule 2
  • This way we can detect the new version of the application. It will skip to run the package if it is already present in the machine otherwise it will upgrade over the older one.

We have another two methods for detecting the installed application.

 

File System

  • The first method is file system. This method detects whether a file or folder is present on the system. If the file system object is not present, the application is marked as not installed.
  • You can use the “Browse” button to find the file on your computer. This will pull in all relevant information, such as the file version or modified date.

 

Detection Rule 3.jpg

  • One important thing to watch is the “This file or folder is associated with a 32-bit application on 64-bit systems” check box. By default, if you are on a 64-bit system, SCCM will only verify against “C:\Program Files” or “C:\Windows\system32” for files or folders and not “Program Files (x86)” or “SysWOW64”. If the application installs to “C:\ProgramFiles (x86)” or puts something in “C:\Windows\SysWOW64” and you queue off of that file, SCCM will not find it. When the user attempts to run the application, it will install again, possibly corrupting it or making it unusable.

Windows Installer

  • This method is automatically filled in when using an MSI install type. This method detects whether the MSI product code exists on the system. This method should only be used when dealing with an MSI. If you do not use the MSI install type, you can use the “Browse” button and find the MSI installer to automatically pull the product code.

Detection Rule 4

 

  • For products that update, but keep the same product code, you can use the “This MSI product code must exist on the target system and the following condition must be met to indicate the presence of this application” options to specify the version of the MSI.

SCCM 2012 – Glossary

Term Meaning
Collection A set of resources in the Configuration Manager. 
Deployment  An application state associated with the software deployment. 
Distribution point A site system role that contains source files for clients to download including application content, software packages, software updates, operating system images and so on.. 
Distribution point group  A set of distribution points that can be managed as a single unit. It provides a logical grouping of distribution points and collections for content distribution. 
Application  An object that contains the content files and instructions for distributing Deployment types, software updates, operating system images, and drivers to clients of the Configuration Manager. 
Global Condition Global condition is used to specify the conditions that must be met before a deployment type can be installed on a client device.
Deployment Type This program deploys software to a computer system. 
Detection method A detection method in Configuration Manager contains rules that are used to check whether an application is already installed on a device. This detection occurs before the application is installed, immediately after the application is installed, and at regular intervals afterwards. This can prevent Configuration Manager from needlessly reinstalling the application and can also detect if the application is already uninstalled by the user.