SCOM Operations DB query to get agent health status with OS categorization

Question

Hi All,

We have a requirement to get health status of all servers using a sql query. Our idea is to create a table with columns Servers, Operating System (Linux/Windows) and Health status of each using a SQL query to display in a dashboard.

The dashboard should be live, so I assume we need to run in OperationsDB. Kindly assist in providing proper query, as I checked could not find any query to get OS details.

Regards

Fadil

Answer 1

Hi,

Here’s a SQL query for the SCOM Operations DB that retrieves the health status of agents along with their operating system categorization:

SELECT 
    hs.DisplayName AS 'Agent Name',
    hs.HealthState AS 'Health Status',
    os.Caption AS 'Operating System'
FROM 
    vManagedEntity AS me
JOIN 
    vHealthService AS hs ON me.ManagedEntityId = hs.BaseManagedEntityId
JOIN 
    vOperatingSystem AS os ON me.ManagedEntityId = os.BaseManagedEntityId
WHERE 
    me.IsDeleted = 0
ORDER BY 
    hs.DisplayName;

This query joins the vManagedEntity, vHealthService, and vOperatingSystem tables to fetch the agent name, health status, and operating system details. Adjust the table and column names if they differ in your SCOM database schema.

This is a list of queries that helpful in report writing or understanding the SCOM DB schema’s to get useful info.

https://kevinholman.com/2016/11/11/scom-sql-queries/

If you need further customization or have any other questions, feel free to ask!