SCCM - SQL Query to find Configuration Item Compliance Status for Specific Collection

 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= '456414' --Specify CI ID

and FM.CollectionID='CM100011' --Specify Collection ID