SQL Query for SCCM Configuration Baselines Results | ConfigMgr

Hi folks, let’s discuss in this post about SQL Query for SCCM Configuration Baselines Results. I have already written one blog post to create Configuration Items and Configuration Baselines. I’ll share my ideas here on viewing reports for Configuration Baseline in ConfigMgr console and SQL.

A configuration item is a container in ConfigMgr that stores specific information. You can read more details about Configuration Item and Configuration Baseline creation process from the previous blog post. How to Create SCCM Configuration Items Configuration Baselines | ConfigMgr.

Configuration Baselines report using ConfigMgr Console

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

Patch My PC
  • Open the Configuration Manager console. Go to Monitoring -> Deployments.

SQL Query for SCCM Configuration Baselines Results | ConfigMgr
CB Reporting – SQL Query for SCCM Configuration Baselines Results | ConfigMgr
  • Search the baselines you deployed to the collection.
SQL Query for SCCM Configuration Baselines Results | ConfigMgr
CB Deployments Reporting – SQL Query for SCCM Configuration Baselines Results | ConfigMgr
  • Double click on the deployment and see the results how many machines are compliant and non compliant.
SQL Query for SCCM Configuration Baselines Results | ConfigMgr
CB Reporting Monitoring Node – SQL Query for SCCM Configuration Baselines Results | ConfigMgr

Configuration Baselines report using SQL Query

  • Let’s find out the SQL query to get configuration baselines report.
SQL Query for SCCM Configuration Baselines Results | ConfigMgr
SQL Query – SQL Query for SCCM Configuration Baselines Results | ConfigMgr
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
Configuration Baselines CI ID – SQL Query for SCCM Configuration Baselines Results | ConfigMgr

Resources

2 thoughts on “SQL Query for SCCM Configuration Baselines Results | ConfigMgr”

  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

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.