Here's a winner from OpsMgr guru and bloggerAndreas "Tenchuu" Zuckerhut.

Q: Can I retrieve a list of uncommunicative (grey) agents with Powershell?

If you search the Internet for this topic, you'll find T-SQL queries everywhere you look, but no Powershell alternative. T-SQL is an options, but generally never as a first choice. Powershell is faster and safer given there's no chance you will inadvertently take something offline or delete in SQL Mgmt Studio.

A: The answer appears very simple – 3 lines of Powershell.

$WCC = get-monitoringclass -name "Microsoft.SystemCenter.Agent"

$MO = Get-MonitoringObject -monitoringclass:$WCC | where {$_.IsAvailable -eq $false} 

$MO | select DisplayName

The SQL (listed here) works, but with a Powershell solution in hand, should be avoided.

SELECT ManagedEntityGenericView.DisplayName, ManagedEntityGenericView.AvailabilityLastModified

FROM ManagedEntityGenericView

INNER JOIN ManagedTypeView ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id

WHERE (ManagedTypeView.Name = 'microsoft.systemCenter.agent') AND (ManagedEntityGenericView.IsAvailable = 0)

ORDER BY ManagedEntityGenericView.DisplayName

NOTE: Several of us have tested this side-by-side with the T-SQL and seen identical results. We'd be interested to hear your experience when comparing the results of the Powershell versus the T-SQL query.

Well Done Andreas!