:::: MENU ::::
Monthly Archives: February 2016

Pull Distribution Points with Source Distribution Points SQL Query

SELECT DISTINCT
dbo.v_DistributionPoints.ServerName AS [Source DP for Pull], dbo.v_DistributionPoints.IsPeerDP, dbo.v_DistributionPoints.IsPullDP,
dbo.vPullDPFullMap.PullDPNALPath AS [All Types of DP List], dbo.v_DistributionPoints.IsPXE, dbo.v_DistributionPoints.Description
FROM dbo.vPullDPFullMap INNER JOIN
dbo.v_DistributionPoints ON dbo.vPullDPFullMap.SourceDPNALPath = dbo.v_DistributionPoints.NALPath


//////////******* List of Views /Tables with fields**********////////////

////////////////****************************** List of Tables with fields************************///////////////////////////

SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE], CAST(P.PRECISION AS VARCHAR) +’/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE] FROM SYS.OBJECTS AS T JOIN SYS.COLUMNS AS C ON T.OBJECT_ID=C.OBJECT_ID JOIN SYS.TYPES AS P ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID WHERE T.TYPE_DESC=’USER_TABLE’;

 

////////////////****************************** List of Views with fields************************///////////////////////////

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS


RDP Enable using PSEXEC

psexec \\XXXXXXXXXX reg add “hklm\system\currentcontrolset\control\terminal server” /f /v fDenyTSConnections /t REG_DWORD /d 0




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’)