Identifying Gray (Uncommunicative Agents) in OpsMgr Directly from the OpsMgr Database

Courtesy of Pete Zerger - https://www.systemcenterforum.org/news/tipidentifying-gray-uncommunicative-agents-in-opsmgr-directly-from-the-opsdb/ 

Source: MS Newsgroups (AdamZ)

I posted a tip a few months ago about where to see which agents are not communicating with their management server (CLICK HERE) within the Operations Console. These appear as the checkmark icon that has turned gray (indicating agent is not longer communicating).

AdamZ thought that was a bit to involved, and instead offered up this query to retreive this data from the OperationsManager database. (Gave this a quick test, and seems to be accurate in my lab).

SELECT ManagedEntityGenericView.DisplayName,
ManagedEntityGenericView.AvailabilityLastModified
FROM ManagedEntityGenericView INNER JOIN
ManagedTypeView ON
ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.IdWHERE (ManagedEntityGenericView.IsAvailable = ‘false’) AND
(ManagedTypeView.Name = ‘Microsoft.SystemCenter.Agent’)
ORDER BY ManagedEntityGenericView.AvailabilityLastModified