Get SCCM Agents

sccm-get-agents.sql

SELECT distinct
	SYS.Name0 AS 'host',
	SYS.User_Name0 AS 'sccm_login_name',
	CASE
		WHEN SYS.User_Domain0 = SYS.Name0 THEN 'WORKGROUP'
		ELSE SYS.User_Domain0
	END AS 'sccm_domain',
	CASE
		WHEN SYS.Client0 = 1 THEN 'Yes'
		WHEN SYS.Client0 = 0 THEN 'No'
		ELSE CONVERT(VARCHAR(2), SYS.Client0)
	END AS 'sccm_agent_installed',
	SYS.Client_Version0 AS 'sccm_agent_version',
	CASE
		WHEN CS.ClientActiveStatus='1' THEN 'Yes'
		WHEN CS.ClientActiveStatus='0' THEN 'No'
	END AS 'sccm_agent_active'
FROM v_R_System SYS
	JOIN v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
	JOIN v_Collection CN ON FCM.CollectionID = CN.CollectionID
	JOIN v_R_User USR ON SYS.User_Name0 = USR.User_Name0
	INNER JOIN (
		SELECT
			ResourceID,
			ClientActiveStatus
		FROM v_CH_ClientSummary
	) CS ON SYS.ResourceID = CS.ResourceID
WHERE CN.Name = 'All Systems'