Contextual alert reports from within Squared Up.

Hello everyone,

I don’t like creating reports in SCOM, never have. It is tedious and time consuming.
Ever since I started working with Squared Up as a 3rd party tool I have tried to create dashboards instead of reports to please customers.
This has worked well so far, and has saved me a lot of hours of manwork. However Squared Up still has some limitations as you cannot retrieve alerts older than 7 days by default.

To solve my annoying issue, I had the idea to reverse engineer the SQL query that is run by the standard ‘Alerts’ report from the Microsoft Generic Report Library. As you can run contextual SQL queries in Squared Up and retrieve the results, I would then use that SQL query to show alert history of that specific object.
This not only works for objects, but also for groups, DA’s etc.

Using SQL profiler I figured out that the alert history report runs a stored procedure called Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet.
This then runs Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse to figure out which objects are contained in the scope of the report.

This query is run with a few parameters:

• From date
• To Date
• Objectlist (ManagedEntityRowID based)
• Severity
• Priority

However, the ManagedEntityRowID is not a value we can populate contextually in Squared Up (more on that later). So we cannot use this value to construct our query.

Configuration

SQL

With the help of the SQL team we created 3 new stored procedures which allowed us to use the SCOM objectid instead. This allows us to use these queries in Squared Up.
The stored procedures can be found here.

Simply run these queries on your SCOM data warehouse in SQL Management Studio and they should be created.

Squared Up

Next step is creating the perspectives. Drill down into any object and create a new perspective.

Give it a name, set to all objects.

There are two different types of queries you can use:

• One which retrieves the count of alerts over a specific amount of time (SQL Scalar tile)
• One which retrieves the alert details themselves.

How to construct the queries can be found on my github here.

You can change the priority, severity and date range however you like in the query itself.

Here’s how to set up the Alert details tile, hiding a few unnecessary columns:

Repeat the same step with the SQL Scalar tile and the other query to retrieve the amount of alerts.

The end result looks like this, and works on any object you drill down into.

After completing these steps Squared Up will automatically generate alert history for each monitored object in SCOM. A time saver for sure!

If you have trouble setting this up, feel free to leave a comment!

Br,

Jasper