Alert SQL Queries and perspectives

Hello Everyone,

I’m looking into creating additional perspectives on a distributed application where you can query the data warehouse for the following:

  • How many alerts that DA has generated in the last month (scalar SQL query)
  • Which alerts were generated by that that DA. (filtering on monitor/rule critical/warning/informational) (Regular SQL)
  • State changes done by that DA. (Regular SQL)
This would allow managers to see the SLA of the application, which alerts have occurred and how many.

Problem is I do not have the SQL queries at hand, so I’m wondering if anyone else has done something similar to get me started?

Br,

Jasper

You could start here for some tips for SQL queries:

SCOM SQL queries
https://blogs.technet.microsoft.com/kevinholman/2016/11/11/scom-sql-queries/

http://www.itserviceanalytics.com/2015/10/querying-distributed-application-data-from-scom-data-warehouse-2/

Hey Jannep,

Thanks for the tip. I am currently looking at reverse engineering the generic alerts reports. Apparently it triggers a stored procedure which contains the SQL query I need. Just need to make some changes to the parameters (like MonitoredEntityRowId) that can hopefully be fulled in through Squared Up.

Br,
Jasper