Get SCOM Agents

scom-get-agents.sql

USE OperationsManager
GO
SELECT
	CASE
		WHEN LOWER(ManagedEntityGenericView.DisplayName) LIKE LOWER('%.%')
		THEN REPLACE(SUBSTRING(ManagedEntityGenericView.DisplayName,1,CHARINDEX('.',ManagedEntityGenericView.DisplayName)),'.','')
		ELSE ManagedEntityGenericView.DisplayName
	END AS host,
	CASE
		WHEN InMaintenanceMode = 1 THEN 'Yes'
		WHEN InMaintenanceMode = 0 THEN 'No'
		ELSE CONVERT(VARCHAR(2), InMaintenanceMode)
	END AS scom_agent_maintenance,
	CASE
		WHEN IsAvailable = 1 THEN 'Yes'
		WHEN IsAvailable = 0 THEN 'No'
		ELSE CONVERT(VARCHAR(2), IsAvailable)
	END AS scom_agent_active,
	hs.Version AS scom_agent_Version,
	hs.PatchList AS scom_agent_patch_list
FROM ManagedEntityGenericView
	INNER JOIN ManagedTypeView ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id
	INNER JOIN (
		SELECT
			BaseManagedEntityId,
			PatchList,
			Version
		FROM MT_HealthService
	) hs ON ManagedEntityGenericView.BaseManagedEntityId = hs.BaseManagedEntityId
WHERE (ManagedTypeView.Name = 'Microsoft.SystemCenter.Agent')
ORDER BY host