Skip to content
Trending
Simplify Windows Devices to Run Only the Required Applications using Intune Enhance macOS Compliance with Custom Security and Compliance Checks to Improve Device Security using Microsoft Intune Manage Samsung Galaxy Firmware Versions to Improve Security and Compliance using Microsoft Intune MS Intune Adds Windows Registry Data Collection to Device Inventory for Single Values All Key Values and Subkeys Updated Windows Device Sync Experience Enhances Per-Device Troubleshooting Configure Windows Sandbox vGPU Policy using Intune for Better Graphics Performance or Reduced Attack Surface Simplify Windows Devices to Run Only the Required Applications using Intune Enhance macOS Compliance with Custom Security and Compliance Checks to Improve Device Security using Microsoft Intune Manage Samsung Galaxy Firmware Versions to Improve Security and Compliance using Microsoft Intune MS Intune Adds Windows Registry Data Collection to Device Inventory for Single Values All Key Values and Subkeys Updated Windows Device Sync Experience Enhances Per-Device Troubleshooting Configure Windows Sandbox vGPU Policy using Intune for Better Graphics Performance or Reduced Attack Surface
SQL Query for SCCM Configuration Baselines Results | ConfigMgr

SQL Query for SCCM Configuration Baselines Results | ConfigMgr

Written By Debabrata Pati
Last Updated August 23, 2024
Posted In SCCM
SHARE

Hi folks, In this post, we’ll discuss the SQL Query for SCCM Configuration Baselines Results. I have already written one blog post about creating Configuration Items and Configuration Baselines.

I’ll share my ideas here on viewing reports for Configuration Baselines in the ConfigMgr console and SQL.

A configuration item is a container in ConfigMgr that stores specific information. It is widely used in SCCM to identify software updates, application models, etc.

The Configuration Item and Baseline check compliance for users and devices. In a large Windows 10 environment migration, they assess OS and driver versions, application versions, and user information and address noncompliance.

Patch My PC

The previous blog post, How to Create SCCM Configuration Items and Configuration Baselines | ConfigMgr, provides more details about the configuration item and configuration baseline creation process.

Configuration Baselines report using ConfigMgr Console

You know how to create the Configuration Items. Now let’s see how to create SQL reports to learn more about Configuration Items and Baseline deployments.

Open the Configuration Manager console. Go to Monitoring -> Deployments.

SQL Query for SCCM Configuration Baselines Results | ConfigMgr - Fig.1
SQL Query for SCCM Configuration Baselines Results | ConfigMgr – Fig.1

Search the baselines you deployed to the collection.

SQL Query for SCCM Configuration Baselines Results | ConfigMgr - Fig.2
SQL Query for SCCM Configuration Baselines Results | ConfigMgr – Fig.2

Double-click on the deployment and see the results of how many machines are compliant and non-compliant.

SQL Query for SCCM Configuration Baselines Results | ConfigMgr - Fig.3
SQL Query for SCCM Configuration Baselines Results | ConfigMgr – Fig.3

Configuration Baselines report using SQL Query

Let’s find out the SQL query to get the configuration baseline report.

SQL Query for SCCM Configuration Baselines Results | ConfigMgr - Fig.4
SQL Query for SCCM Configuration Baselines Results | ConfigMgr – Fig.4
select distinct VRS.Netbios_Name0, CI.ComplianceStateName from v_R_System VRS
right join v_FullCollectionMembership_Valid FM on VRS.ResourceID=FM.ResourceID
right join fn_ListCI_ComplianceState(1033) CI on VRS.ResourceID=CI.ResourceID
where CI.CI_ID='xxx508' --Specify CI ID
and FM.CollectionID='xxx003' --Specify Collection ID

How to find CI ID

Go to Assets and Compliance -> Compliance Settings -> Configuration Baselines. To view, CI ID follow the below snapshot.

SQL Query for SCCM Configuration Baselines Results | ConfigMgr - Fig.5
SQL Query for SCCM Configuration Baselines Results | ConfigMgr – Fig.5

Resources

We are on WhatsApp now. To get the latest step-by-step guides, news, and updates, Join our Channel. Click here – HTMD WhatsApp

Author

Debabrata Pati has more than 8+ years of experience in IT. Skilled in MEMCM, Azure, and Powershell. More than Six (6) years of experience in MEMCM (SCCM) administration, OSD, and Troubleshooting for the environment with more than 100K client devices.

Written by

Debabrata Pati has more than 8+ years of experience in IT. Skilled in MEMCM, Azure, and Powershell. More than Six (6) years of experience in MEMCM (SCCM) administration, OSD, and Troubleshooting for the environment with more than 100K client devices.

Discussion · 4 comments

  1. Thanks for your post!

    And what about compliance for selected device (without specifying particular CB)? I slightly modified your select, but noticed, that CI are returned multiple times with different results (compliant, error,..). So it seems, that it show even the history?

    select distinct VRS.Netbios_Name0 as ComputerName, CI.DisplayName, CI.ComplianceStateName from v_R_System
    VRS
    right join v_FullCollectionMembership_Valid FM on VRS.ResourceID=FM.ResourceID
    right join fn_ListCI_ComplianceState(1033) CI on VRS.ResourceID=CI.ResourceID
    where VRS.Netbios_Name0=’xxx’ –enter device name

    Reply

  2. Thanks Debabrata, I was looking for somehting similar to this and the fn_ListCI_ComplianceState(1033) is exactly what I wanted. I did modify it slightly to go by the name of the CI instead of the CI ID. This way we don’t need to lookup and cross reference a number. In case anyone wants to see what I am talking about please see below

    select distinct
    sys.Netbios_Name0 ‘Hostname’
    , CI.ComplianceStateName ‘Compliance’
    ,CI.DisplayName
    from v_R_System sys
    right join fn_ListCI_ComplianceState(1033) CI on sys.ResourceID=CI.ResourceID
    where CI.displayname like ‘Network – Palo Alto Global Protect 2021 Cert’
    Order by [Compliance]

    Reply

Join the discussion

Your email address will not be published. Required fields are marked *

Related guides

Intune

Windows 11 KB5101650 KB5099414 July 2026 Patch and 3 Zero Day Vulnerabilities and 570 Flaws

Key Takeaways Windows 11 KB5101650 KB5099414 July 2026 Patch and 3 Zero Day Vulnerabilities and 570 Flaws! In the July 2026 Patch, Microsoft introduced new features designed to improve the overall Windows experience. The update adds enhancements to Windows Update for more flexible update management and introduces Point-in-Time Restore, providing an additional recovery option for […]

AC Anoop C Nair 9 min read
Intune

2026 June KB5094126 KB5093998 Windows 11 Patch | 3 Zero Day Vulnerabilities and 200 Flaws

Key Takeaways 2026 June KB5094126 KB5093998 Windows 11 Patch | 3 Zero Day Vulnerabilities and 200 Flaws! The June 2026 Windows 11 Patch Tuesday update brings several improvements to File Explorer. It adds support for additional archive formats, including UU, CPIO, XAR, and NuGet Packages (NUPKG). The update also preserves View and Sort preferences in […]

AC Anoop C Nair 10 min read
Intune

2026 May KB5089549 KB5087420 Windows 11 Patch | 0 Zero Day Vulnerabilities and 120 Flaws

Key Takeaways The Windows 11 May 2026 Patch KB5089549 KB5087420 Update brings important security fixes, performance improvements, and reliability enhancements across the operating system. The update introduces new features such as Xbox Mode for gaming, File Explorer improvements, enhanced input and sharing experiences, better taskbar and Windows Hello reliability, and additional enterprise management capabilities for […]

AC Anoop C Nair 8 min read
SCCM

ConfigMgr 2603 Introduces New Early Update Enrollment Process

Key Takeaways In this post we are discussing the ConfigMgr 2603 Introduces New Early Update Enrollment Process. Microsoft has officially released Configuration Manager version 2603 to the Early Update Ring, giving organizations an opportunity to test upcoming improvements before the global production rollout. The release is targeted at enterprises running ConfigMgr version 2409 or later […]

AC Anoop C Nair 3 min read