Displaying OpsMgr Events Data in Squared Up Dashboards

For those who’s been using Squared Up dashboards for your OpsMgr environments, you’d probably know that currently Squared Up does not have a plug-in for OpsMgr event data, thus you cannot display event data collected by OpsMgr on a Squared Up dashboard natively.

However, since Squared Up does have a SQL plugin and the OpsMgr event data is stored in OpsMgr databases, I’d like to show you a way of displaying event data using the SQL Plugin today.

When developing event collection rules for an OpsMgr management pack, MP developers generally would configure the collection rule to store the collected event in both operational DB (OperationsManager) and the Data Warehouse DB (OperationsManagerDW).

So why storing the data in 2 different databases? As you may know, in the native OpsMgr console, all the event views are accessing the event data from the operational DB, but when you are using reports to retrieve event data, most likely you are accessing the data from the DW DB. Another difference is, the retention period in Operational DB is a lot shorter than the Data Warehouse DB. For example the data retention period for event data is 7 days in Operational DB and 100 days in the DW DB.

With the Squared Up SQL Plugin, there’s a variable you can use for referencing the Data Warehouse DB (global:dw). I have developed couple of similar SQL query that you can run against the DW DB to retrieve event data:

Get the 30 most recent events logged with a specific source (publisher):

Get all events with a specific Event Number:

Note: For these 2 queries, you will need to place the event publisher name and event ID into the queries accordingly.

As an example, I have created a Squared Up dashboard for Forefront Endpoint Protection (FEP) MP, where I used the SQL plugin to retrieve the recent 30 security events logged by the MP:

image

The result displayed by the SQL query matches the Event view shipped with the FEP MP:

SNAGHTML87024d3

SNAGHTML8711770

and I have used the Data Warehouse DB variable in the SQL plugin configuration:

image

You can download this dashboard from Squared Up’s community site: https://community.squaredup.com/browse/download-info/forefront-endpoint-protection-2/

Lastly, please always test and tweak to query to need your requirements in SQL Management Studio first.

Leave a Reply

%d bloggers like this: