Historical Alerts Report

I have been requested to attempt to create a report or a listing of alerts that have been interacted with. I need the alert name, node name, person who acknowledged, when the alert was created, and when the alert was close. I also have an alert custom property where i only care about non test alerts. I can seem to figure out how to connect the information in the tables to pull out what i need historically, eg past 7 day, past 30 days. I would prefer a swql query or a way to do this in a report. Thanks in advanced

Find more posts tagged with

Orion Platform

Alerts

orion sdk

Accepted answers

All comments

stuartd

Setup a new report, select custom table and in the add content section change the dropdown to:

In the box that appears, leave the SWQL selected and paste in the following query:

SELECT TOP 100 AlertDefID, ActiveObject, ObjectType, ObjectName, AlertMessage, TriggerTimeStamp, AcknowledgedBy, AcknowledgedTime, LastUpdate, AlertNotes, Notes, AlertObjectID
FROM Orion.AlertStatus
WHERE AcknowledgedTime NOT LIKE '%1899%'

If the "WHERE AcknowledgedTime NOT LIKE '%1899%'" doesn't suit then edit according to taste or your results. You could also use the AcknowlegedBy column and in our case we would set it to "WHERE AcknowledgedBy LIKE 'OUR_DOMAIN_NAME%' "

Click preview results to make sure the data is being found and then 'add to layout'.

Finally, click on Edit table and select the actual columns you want, then go through and set any email or other actions you want.

As to your custom property - without knowing its name or likely results seen in the DB then I don't know what to suggest. Most likely you will need to do a JOIN to the NodesCustomPrioperties table

hallstev5

the query that you have presented above gives the me the active alerts that have been acknowledged. What i need is a way to list all of the times that each alert has triggered in the past month, or given time frame. I am pretty sure i can't get the person who acknowledged the alert but i would assume i can get what triggered the alert, when it was opened, when it was closed at minimum.

stuartd

Clearly I misunderstood what you were after as when you said you wanted "those alerts that had been interacted with" I read that to mean alerts that had been acknowledged.

If that's not what you want then you need to be clearer for me please.

You could open up SWQL Studio, copy and run the code and then edit to your end goal.

For e.g.

SELECT AlertDefID, ActiveObject, ObjectType, ObjectName, AlertMessage, TriggerTimeStamp, AcknowledgedBy, AcknowledgedTime, LastUpdate, AlertNotes, Notes, AlertObjectID
FROM Orion.AlertStatus
WHERE TriggerTimeSTamp > (GetDate()-7)
ORDER BY ObjectName

if you run this, it shows you all alerts in the last 7 days and then orders them by the objectname column. You could adjust that to any of the columns of choice.

If this table isn't suiting needs, then do a search in SWQL Studio on say 'Alert' and see what other options come up that may be of use. The table, Orion.AlertActive might be a better fit for your needs?

Have you made a start on a report you could share to help?