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.
- Open the Configuration Manager console. Go to Monitoring -> Deployments.
- Search the baselines you deployed to the collection.
- Double click on the deployment and see the results how many machines are compliant and non compliant.
Configuration Baselines report using SQL Query
- Let’s find out the SQL query to get configuration baselines report.
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.
Resources
- How to Create SCCM Configuration Items Configuration Baselines | ConfigMgr
- Create configuration items in Configuration Manager
- ConfigMgr SCCM DCM Implementation and Troubleshooting Guide
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
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]