SELECT-only account required. The SQL account used here must have read-only (SELECT) permission on the site database. This application never issues INSERT, UPDATE, DELETE, TRUNCATE, ALTER, CREATE or DROP statements against a Configuration Manager database, and never creates indexes on it.
Overall Compliance
Device counts per compliance category for the whole estate or a collection.
Views used: v_R_System, v_FullCollectionMembership, v_Update_ComplianceStatusAll, v_UpdateInfo · Parameters: @CollectionID, @IncludeSuperseded, @IncludeExpired
/* ============================================================
Verentra SCCM Patching Dashboard - Overall Compliance
Purpose : Device counts per compliance category for the whole estate or a collection.
SCCM views : v_R_System, v_FullCollectionMembership, v_Update_ComplianceStatusAll, v_UpdateInfo
Parameters : @CollectionID, @IncludeSuperseded, @IncludeExpired
Mappings : Status 1=Compliant, 2=Required, 3=Error, 0/NULL=Unknown.
Performance : Filter by CollectionID first; aggregate with a CTE to avoid repeated scans.
Columns : ComplianceCategory, DeviceCount
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @CollectionID VARCHAR(8) = @CollectionID;
DECLARE @IncludeSuperseded BIT = @IncludeSuperseded;
DECLARE @IncludeExpired BIT = @IncludeExpired;
WITH TargetDevices AS (
SELECT sys.ResourceID, sys.Netbios_Name0 AS DeviceName
FROM v_R_System sys
JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = sys.ResourceID
WHERE fcm.CollectionID = @CollectionID
AND sys.Client0 = 1
AND sys.Obsolete0 = 0
),
DeviceState AS (
SELECT td.ResourceID,
MAX(CASE WHEN cs.Status = 3 THEN 1 ELSE 0 END) AS HasError,
MAX(CASE WHEN cs.Status = 2 THEN 1 ELSE 0 END) AS HasRequired,
MAX(CASE WHEN cs.Status IS NULL OR cs.Status = 0 THEN 1 ELSE 0 END) AS HasUnknown
FROM TargetDevices td
LEFT JOIN v_Update_ComplianceStatusAll cs ON cs.ResourceID = td.ResourceID
LEFT JOIN v_UpdateInfo ui ON ui.CI_ID = cs.CI_ID
WHERE (@IncludeSuperseded = 1 OR ISNULL(ui.IsSuperseded, 0) = 0)
AND (@IncludeExpired = 1 OR ISNULL(ui.IsExpired, 0) = 0)
GROUP BY td.ResourceID
)
SELECT ComplianceCategory =
CASE WHEN HasError = 1 THEN 'Error'
WHEN HasRequired = 1 THEN 'Required'
WHEN HasUnknown = 1 THEN 'Unknown'
ELSE 'Compliant' END,
DeviceCount = COUNT(*)
FROM DeviceState
GROUP BY CASE WHEN HasError = 1 THEN 'Error'
WHEN HasRequired = 1 THEN 'Required'
WHEN HasUnknown = 1 THEN 'Unknown'
ELSE 'Compliant' END
ORDER BY ComplianceCategory;
Compliance by Collection
Compliance percentage for every device collection.
Views used: v_Collection, v_FullCollectionMembership, v_Update_ComplianceStatusAll · Parameters: @MinMembers
/* ============================================================
Verentra SCCM Patching Dashboard - Compliance by Collection
Purpose : Compliance percentage for every device collection.
SCCM views : v_Collection, v_FullCollectionMembership, v_Update_ComplianceStatusAll
Parameters : @MinMembers
Mappings : Compliant = devices with no Required, Error or Unknown update states.
Performance : Large estates should schedule this query; it touches every collection membership row.
Columns : CollectionID, CollectionName, TotalDevices, CompliantDevices, CompliancePercent
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @MinMembers INT = @MinMembers;
SELECT col.CollectionID,
col.Name AS CollectionName,
TotalDevices = COUNT(DISTINCT fcm.ResourceID),
CompliantDevices = COUNT(DISTINCT CASE WHEN cs.Status = 1 THEN fcm.ResourceID END),
CompliancePercent = CAST(100.0 * COUNT(DISTINCT CASE WHEN cs.Status = 1 THEN fcm.ResourceID END)
/ NULLIF(COUNT(DISTINCT fcm.ResourceID), 0) AS DECIMAL(5,1))
FROM v_Collection col
JOIN v_FullCollectionMembership fcm ON fcm.CollectionID = col.CollectionID
LEFT JOIN v_Update_ComplianceStatusAll cs ON cs.ResourceID = fcm.ResourceID
WHERE col.CollectionType = 2
GROUP BY col.CollectionID, col.Name
HAVING COUNT(DISTINCT fcm.ResourceID) >= @MinMembers
ORDER BY CompliancePercent ASC;
Device Compliance Details
Per-device inventory, scan freshness and update counts for the device table.
Views used: v_R_System, v_GS_OPERATING_SYSTEM, v_CH_ClientSummary, v_UpdateScanStatus, v_Update_ComplianceStatusAll · Parameters: @CollectionID, @DeviceName, @InactiveDays
/* ============================================================
Verentra SCCM Patching Dashboard - Device Compliance Details
Purpose : Per-device inventory, scan freshness and update counts for the device table.
SCCM views : v_R_System, v_GS_OPERATING_SYSTEM, v_CH_ClientSummary, v_UpdateScanStatus, v_Update_ComplianceStatusAll
Parameters : @CollectionID, @DeviceName, @InactiveDays
Mappings : PendingRestart derived from enforcement state 1006 on the client's assignments.
Performance : Apply @DeviceName as a leading-wildcard-free LIKE to keep index seeks.
Columns : ResourceID, DeviceName, ClientActiveStatus, OperatingSystem, LastHWScan, LastScanTime, InstalledUpdates, RequiredUpdates, FailedUpdates, UnknownUpdates, CompliancePercent
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @CollectionID VARCHAR(8) = @CollectionID;
DECLARE @DeviceName NVARCHAR(64) = @DeviceName;
DECLARE @InactiveDays INT = @InactiveDays;
SELECT sys.ResourceID,
DeviceName = sys.Netbios_Name0,
ClientActiveStatus = ch.ClientActiveStatus,
OperatingSystem = os.Caption0,
OSBuild = os.Version0,
LastHWScan = ws.LastHWScan,
LastScanTime = uss.LastScanTime,
LastPolicyRequest = ch.LastPolicyRequest,
InstalledUpdates = SUM(CASE WHEN cs.Status = 1 THEN 1 ELSE 0 END),
RequiredUpdates = SUM(CASE WHEN cs.Status = 2 THEN 1 ELSE 0 END),
FailedUpdates = SUM(CASE WHEN cs.Status = 3 THEN 1 ELSE 0 END),
UnknownUpdates = SUM(CASE WHEN ISNULL(cs.Status, 0) = 0 THEN 1 ELSE 0 END),
CompliancePercent = CAST(100.0 * SUM(CASE WHEN cs.Status = 1 THEN 1 ELSE 0 END)
/ NULLIF(COUNT(cs.CI_ID), 0) AS DECIMAL(5,1))
FROM v_R_System sys
JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = sys.ResourceID AND fcm.CollectionID = @CollectionID
LEFT JOIN v_GS_OPERATING_SYSTEM os ON os.ResourceID = sys.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS ws ON ws.ResourceID = sys.ResourceID
LEFT JOIN v_CH_ClientSummary ch ON ch.ResourceID = sys.ResourceID
LEFT JOIN v_UpdateScanStatus uss ON uss.ResourceID = sys.ResourceID
LEFT JOIN v_Update_ComplianceStatusAll cs ON cs.ResourceID = sys.ResourceID
WHERE sys.Obsolete0 = 0
AND (@DeviceName IS NULL OR sys.Netbios_Name0 LIKE @DeviceName + '%')
AND (@InactiveDays IS NULL OR uss.LastScanTime < DATEADD(DAY, -@InactiveDays, GETDATE()))
GROUP BY sys.ResourceID, sys.Netbios_Name0, ch.ClientActiveStatus, os.Caption0, os.Version0,
ws.LastHWScan, uss.LastScanTime, ch.LastPolicyRequest
ORDER BY CompliancePercent ASC;
Missing Updates by Device
Every required (missing) update per device, excluding expired and superseded by default.
Views used: v_Update_ComplianceStatusAll, v_UpdateInfo, v_R_System, v_CategoryInfo · Parameters: @CollectionID, @Severity, @IncludeSuperseded, @IncludeExpired
/* ============================================================
Verentra SCCM Patching Dashboard - Missing Updates by Device
Purpose : Every required (missing) update per device, excluding expired and superseded by default.
SCCM views : v_Update_ComplianceStatusAll, v_UpdateInfo, v_R_System, v_CategoryInfo
Parameters : @CollectionID, @Severity, @IncludeSuperseded, @IncludeExpired
Mappings : Status = 2 -> Required (missing).
Performance : Returns one row per device/update; always constrain by collection.
Columns : DeviceName, ArticleID, BulletinID, Title, Severity, DateRevised
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @CollectionID VARCHAR(8) = @CollectionID;
DECLARE @Severity INT = @Severity;
DECLARE @IncludeSuperseded BIT = @IncludeSuperseded;
DECLARE @IncludeExpired BIT = @IncludeExpired;
SELECT DeviceName = sys.Netbios_Name0,
ArticleID = ui.ArticleID,
BulletinID = ui.BulletinID,
Title = ui.Title,
Severity = CASE ISNULL(ui.Severity, 0) WHEN 10 THEN 'Critical' WHEN 8 THEN 'Important' WHEN 6 THEN 'Moderate' WHEN 2 THEN 'Low' ELSE 'None' END,
DateRevised = ui.DateRevised
FROM v_Update_ComplianceStatusAll cs
JOIN v_UpdateInfo ui ON ui.CI_ID = cs.CI_ID
JOIN v_R_System sys ON sys.ResourceID = cs.ResourceID
JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = sys.ResourceID AND fcm.CollectionID = @CollectionID
WHERE cs.Status = 2
AND (@Severity IS NULL OR ui.Severity = @Severity)
AND (@IncludeSuperseded = 1 OR ui.IsSuperseded = 0)
AND (@IncludeExpired = 1 OR ui.IsExpired = 0)
ORDER BY sys.Netbios_Name0, ui.ArticleID;
Failed Updates by Device
Updates that failed to install, with the reported error code.
Views used: v_Update_ComplianceStatusAll, v_UpdateInfo, v_R_System, v_StateNames · Parameters: @CollectionID
/* ============================================================
Verentra SCCM Patching Dashboard - Failed Updates by Device
Purpose : Updates that failed to install, with the reported error code.
SCCM views : v_Update_ComplianceStatusAll, v_UpdateInfo, v_R_System, v_StateNames
Parameters : @CollectionID
Mappings : Status = 3 -> Error. Error codes resolved through v_StateNames where available.
Performance : Small result set; safe to run interactively.
Columns : DeviceName, ArticleID, Title, ErrorCode, LastStatusChangeTime
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @CollectionID VARCHAR(8) = @CollectionID;
SELECT DeviceName = sys.Netbios_Name0,
ArticleID = ui.ArticleID,
Title = ui.Title,
ErrorCode = CONVERT(VARCHAR(20), master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(4), cs.LastErrorCode))),
LastStatusChangeTime = cs.LastStatusChangeTime
FROM v_Update_ComplianceStatusAll cs
JOIN v_UpdateInfo ui ON ui.CI_ID = cs.CI_ID
JOIN v_R_System sys ON sys.ResourceID = cs.ResourceID
JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = sys.ResourceID AND fcm.CollectionID = @CollectionID
WHERE cs.Status = 3
ORDER BY cs.LastStatusChangeTime DESC;
Compliance by Deployment
Enforcement summary per software update deployment.
Views used: v_CIAssignment, v_DeploymentSummary, v_Collection · Parameters: @DeploymentID
/* ============================================================
Verentra SCCM Patching Dashboard - Compliance by Deployment
Purpose : Enforcement summary per software update deployment.
SCCM views : v_CIAssignment, v_DeploymentSummary, v_Collection
Parameters : @DeploymentID
Mappings : v_DeploymentSummary is joined on DeploymentID = Assignment_UniqueID (validated on CB 2509).
Performance : Reads pre-summarized assignment data; very fast.
Columns : AssignmentID, AssignmentName, CollectionName, Targeted, Compliant, Failed
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @DeploymentID INT = @DeploymentID;
SELECT AssignmentID = a.AssignmentID,
AssignmentName = a.AssignmentName,
CollectionName = col.Name,
StartTime = a.StartTime,
Deadline = a.EnforcementDeadline,
Targeted = ISNULL(s.NumberTotal, 0),
Compliant = ISNULL(s.NumberSuccess, 0),
InProgress = ISNULL(s.NumberInProgress, 0),
Unknown = ISNULL(s.NumberUnknown, 0),
Failed = ISNULL(s.NumberErrors, 0),
NotApplicable = ISNULL(s.NumberOther, 0),
LastSummarization = s.SummarizationTime,
CompliancePercent = CAST(100.0 * ISNULL(s.NumberSuccess, 0) / NULLIF(s.NumberTotal, 0) AS DECIMAL(5,1))
FROM v_CIAssignment a
JOIN v_Collection col ON col.CollectionID = a.CollectionID
LEFT JOIN v_DeploymentSummary s ON s.DeploymentID = a.Assignment_UniqueID
WHERE (@DeploymentID IS NULL OR a.AssignmentID = @DeploymentID)
ORDER BY CompliancePercent ASC;
Pending Restart Devices
Devices that installed updates but await a restart.
Views used: v_CombinedDeviceResources, v_R_System, v_FullCollectionMembership · Parameters: @CollectionID
/* ============================================================
Verentra SCCM Patching Dashboard - Pending Restart Devices
Purpose : Devices that installed updates but await a restart.
SCCM views : v_CombinedDeviceResources, v_R_System, v_FullCollectionMembership
Parameters : @CollectionID
Mappings : ClientState bitmask: 1 Configuration Manager, 2 file rename, 4 Windows Update, 8 add/remove feature.
Performance : Fast; single pass over the combined device resource view.
Columns : DeviceName, PendingReason, LastPolicyRequest
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @CollectionID VARCHAR(8) = @CollectionID;
SELECT DeviceName = sys.Netbios_Name0,
PendingReason = STUFF(
CASE WHEN cdr.ClientState & 1 = 1 THEN ', Configuration Manager' ELSE '' END +
CASE WHEN cdr.ClientState & 2 = 2 THEN ', File rename' ELSE '' END +
CASE WHEN cdr.ClientState & 4 = 4 THEN ', Windows Update' ELSE '' END +
CASE WHEN cdr.ClientState & 8 = 8 THEN ', Add/remove feature' ELSE '' END, 1, 2, ''),
LastPolicyRequest = cdr.LastPolicyRequest
FROM v_CombinedDeviceResources cdr
JOIN v_R_System sys ON sys.ResourceID = cdr.MachineID
JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = sys.ResourceID AND fcm.CollectionID = @CollectionID
WHERE ISNULL(cdr.ClientState, 0) > 0
ORDER BY cdr.LastPolicyRequest DESC;
Inactive or Stale Clients
Clients that have not reported policy, inventory or scans recently.
Views used: v_CH_ClientSummary, v_R_System, v_UpdateScanStatus · Parameters: @InactiveDays
/* ============================================================
Verentra SCCM Patching Dashboard - Inactive or Stale Clients
Purpose : Clients that have not reported policy, inventory or scans recently.
SCCM views : v_CH_ClientSummary, v_R_System, v_UpdateScanStatus
Parameters : @InactiveDays
Mappings : Inactive clients are always categorised as Unknown, never Compliant.
Performance : Fast; consider scheduling for very large estates.
Columns : DeviceName, ClientActiveStatus, LastPolicyRequest, LastScanTime, DaysSinceScan
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @InactiveDays INT = @InactiveDays;
SELECT DeviceName = sys.Netbios_Name0,
ClientActiveStatus = ch.ClientActiveStatus,
LastPolicyRequest = ch.LastPolicyRequest,
LastScanTime = uss.LastScanTime,
DaysSinceScan = DATEDIFF(DAY, uss.LastScanTime, GETDATE())
FROM v_R_System sys
LEFT JOIN v_CH_ClientSummary ch ON ch.ResourceID = sys.ResourceID
LEFT JOIN v_UpdateScanStatus uss ON uss.ResourceID = sys.ResourceID
WHERE sys.Client0 = 1
AND sys.Obsolete0 = 0
AND (ch.ClientActiveStatus = 0 OR uss.LastScanTime < DATEADD(DAY, -@InactiveDays, GETDATE()))
ORDER BY DaysSinceScan DESC;
Compliance by Operating System
Compliance split by operating system caption and build.
Views used: v_GS_OPERATING_SYSTEM, v_R_System, v_Update_ComplianceStatusAll · Parameters: @CollectionID
/* ============================================================
Verentra SCCM Patching Dashboard - Compliance by Operating System
Purpose : Compliance split by operating system caption and build.
SCCM views : v_GS_OPERATING_SYSTEM, v_R_System, v_Update_ComplianceStatusAll
Parameters : @CollectionID
Mappings : Standard status mapping.
Performance : Group on Caption0 to avoid high-cardinality build grouping.
Columns : OperatingSystem, TotalDevices, CompliantDevices, CompliancePercent
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @CollectionID VARCHAR(8) = @CollectionID;
SELECT OperatingSystem = os.Caption0,
TotalDevices = COUNT(DISTINCT sys.ResourceID),
CompliantDevices = COUNT(DISTINCT CASE WHEN cs.Status = 1 THEN sys.ResourceID END),
CompliancePercent = CAST(100.0 * COUNT(DISTINCT CASE WHEN cs.Status = 1 THEN sys.ResourceID END)
/ NULLIF(COUNT(DISTINCT sys.ResourceID), 0) AS DECIMAL(5,1))
FROM v_R_System sys
JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = sys.ResourceID AND fcm.CollectionID = @CollectionID
LEFT JOIN v_GS_OPERATING_SYSTEM os ON os.ResourceID = sys.ResourceID
LEFT JOIN v_Update_ComplianceStatusAll cs ON cs.ResourceID = sys.ResourceID
WHERE sys.Obsolete0 = 0
GROUP BY os.Caption0
ORDER BY CompliancePercent ASC;
Compliance Trends
Monthly compliance trend from the application's own trend history table.
Views used: Application database table dbo.ComplianceSnapshot (NOT an SCCM table) · Parameters: @StartDate, @EndDate, @CollectionID
/* ============================================================
Verentra SCCM Patching Dashboard - Compliance Trends
Purpose : Monthly compliance trend from the application's own trend history table.
SCCM views : Application database table dbo.ComplianceSnapshot (NOT an SCCM table)
Parameters : @StartDate, @EndDate, @CollectionID
Mappings : Snapshots are written by the connector into the application database only.
Performance : Index the snapshot table in the APPLICATION database. Never index SCCM.
Columns : SnapshotMonth, CompliancePercent, CriticalPercent, SecurityPercent
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @StartDate DATETIME = @StartDate;
DECLARE @EndDate DATETIME = @EndDate;
SELECT SnapshotMonth = CONVERT(CHAR(7), SnapshotDate, 126),
CompliancePercent = AVG(CompliancePercent),
CriticalPercent = AVG(CriticalPercent),
SecurityPercent = AVG(SecurityPercent)
FROM dbo.ComplianceSnapshot
WHERE SnapshotDate BETWEEN @StartDate AND @EndDate
AND (@CollectionID IS NULL OR CollectionID = @CollectionID)
GROUP BY CONVERT(CHAR(7), SnapshotDate, 126)
ORDER BY SnapshotMonth;
Top Missing KB Articles
The updates missing from the largest number of devices.
Views used: v_Update_ComplianceStatusAll, v_UpdateInfo · Parameters: @CollectionID, @TopN
/* ============================================================
Verentra SCCM Patching Dashboard - Top Missing KB Articles
Purpose : The updates missing from the largest number of devices.
SCCM views : v_Update_ComplianceStatusAll, v_UpdateInfo
Parameters : @CollectionID, @TopN
Mappings : Status = 2 -> Required.
Performance : Uses TOP with an ordered aggregate; fast on summarized data.
Columns : ArticleID, Title, Severity, DevicesMissing
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @CollectionID VARCHAR(8) = @CollectionID;
DECLARE @TopN INT = @TopN;
SELECT TOP (@TopN)
ArticleID = ui.ArticleID,
Title = ui.Title,
Severity = CASE ISNULL(ui.Severity, 0) WHEN 10 THEN 'Critical' WHEN 8 THEN 'Important' WHEN 6 THEN 'Moderate' WHEN 2 THEN 'Low' ELSE 'None' END,
DevicesMissing = COUNT(DISTINCT cs.ResourceID)
FROM v_Update_ComplianceStatusAll cs
JOIN v_UpdateInfo ui ON ui.CI_ID = cs.CI_ID
JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = cs.ResourceID AND fcm.CollectionID = @CollectionID
WHERE cs.Status = 2 AND ui.IsExpired = 0 AND ui.IsSuperseded = 0
GROUP BY ui.ArticleID, ui.Title, CASE ISNULL(ui.Severity, 0) WHEN 10 THEN 'Critical' WHEN 8 THEN 'Important' WHEN 6 THEN 'Moderate' WHEN 2 THEN 'Low' ELSE 'None' END
ORDER BY DevicesMissing DESC;
Update Scan Errors
Clients whose software update scan cycle is failing.
Views used: v_UpdateScanStatus, v_R_System, v_StateNames · Parameters: @CollectionID
/* ============================================================
Verentra SCCM Patching Dashboard - Update Scan Errors
Purpose : Clients whose software update scan cycle is failing.
SCCM views : v_UpdateScanStatus, v_R_System, v_StateNames
Parameters : @CollectionID
Mappings : LastScanState 6/7 -> Error; these devices cannot report accurate compliance.
Performance : Fast; one row per client.
Columns : DeviceName, LastScanState, LastErrorCode, LastScanTime
READ-ONLY : SELECT only. Requires db_datareader on the site database.
============================================================ */
DECLARE @CollectionID VARCHAR(8) = @CollectionID;
SELECT DeviceName = sys.Netbios_Name0,
LastScanState = uss.LastScanState,
LastErrorCode = uss.LastErrorCode,
LastScanTime = uss.LastScanTime,
LastScanPackageLocation = uss.LastScanPackageLocation
FROM v_UpdateScanStatus uss
JOIN v_R_System sys ON sys.ResourceID = uss.ResourceID
JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = sys.ResourceID AND fcm.CollectionID = @CollectionID
WHERE uss.LastErrorCode <> 0 OR uss.LastScanState IN (6, 7)
ORDER BY uss.LastScanTime DESC;