Advanced Reporting Part 1: Re-Creating the “All Alerts” Resource With Extras
(“In Case You Missed It Monday” is my chance to showcase something that I wrote and published in another venue, but is still relevant. This week’s post originally appeared on THWACK)
A customer recently reached out to me with an interesting request: they needed a screen for their NOC that listed active alerts, with the “Acknowledge” option available, which was sorted by a custom property (in this case, the importance of the server).
While the first two items (open alerts and the acknowledge button) are already present in the default Alerts & Activity, Alerts menu option (which takes you to the “All Active Alerts” page). You can see an example of this in the online demo: https://oriondemo.solarwinds.com/Orion/NetPerfMon/Alerts.aspx
But the need to sort by a custom property throws a wrench into things. It shouldn’t, but it does. The All Active Alerts page is a resource, not a report. There’s no option to edit this object or include additional elements.
The next logical option is to create a report. There, we’d be able to add more fields. In fact, the “All Active Alerts” report, included in all Orion® installations, is a perfect starting place (again, here is a sample from the online demo: https://oriondemo.solarwinds.com/Orion/Report.aspx?ReportID=116&ReturnTo=aHR0cHM6Ly9vcmlvbmRlbW8uc29sYXJ3aW5kcy5jb20vb3J…).
However, the challenge you quickly run into is that there’s no “acknowledge” button that can be included in a report.
This describes the situation my customer was in, and why they’d reached out to me. They asked, “Do you know anyone on the development team who could make this happen?”
In short, I did know someone who could make it happen: me. Now that’s not to say I’m some magical developer unicorn who flies in on my hovercraft, dumps sparkle-laden code upon a problem, and all is solved. In fact, I’m not much of a programmer at all. Nobody will ever weep with joy at the sublime and elegant beauty of my code. In fact, the most complimentary thing anyone’s ever said about something I wrote was “Well, it ran. That time.” (And their tone implied they weren’t quite sure HOW it ran in the first place.)
Now, I think of myself less as a DevOps unicorn and more of a DevOps ferret—a creature that scrabbles around, seeking out hidden corners and openings, and delves into them to see what secret treasures they hold. Often, all you come out with is a rusty tin can or a dead mouse. But every once in a while, you find a valuable gem.
And in this case, my past ferreting through the Orion Platform’s dark corners had left me with just the right stockpile of tricks and tools to provide a solution.
While I’m going to delve into the specifics over the next 3 days, I want to provide an overview here, along with enough of a shorthand that you may be able to use this as the starting point for your own needs.
First: there’s no need to do all the hard work. I’ve posted my version of this report here: Report: All Active Alerts with Acknowledge action, ready for you to download and adapt. Remember that famous T.S. Eliot quote,
“Immature poets imitate; mature poets steal.”
And therefore, I advise you, much like Abbie Hoffman, to “Steal This Report.”
I’ve posted the SWQL code below, as well. A few notes about the specifics of what this report is doing/expecting:
- It is largely based on the default “All Active Alerts” report. I do not mean to imply in any way that I was the author of that wonderful resource
- It pulls a Node custom property named “importance,” which is intended for sorting of the actual report
- In addition to the “Acknowledge Alert” link, the name of the object that triggered the alert is clickable as well
The SWQL Query
SELECT DISTINCT NodeCP.Importance, '<A HREF="'+AlertObjects.EntityDetailsURL+'" target=”_blank">'+Nodes.Caption+'</a>' AS NodeName, '<A HREF="/Orion/Netperfmon/AckAlert.aspx?AlertDefID='+tostring(AlertObjects.AlertObjectID)+'" target="_blank">ClickToAck</a>' AS AcknowledgeIt, AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType, AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption, ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message, AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote, Case When Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+'d '+ ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+ ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ') When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+ ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ') When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ') Else '' End AS ActiveTime FROM Orion.AlertObjects (nolock=true) AlertObjects INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActiveSELECT DISTINCT NodeCP.Importance, '<A HREF="'+AlertObjects.EntityDetailsURL+'" target=”_blank">'+Nodes.Caption+'</a>' AS NodeName, '<A HREF="/Orion/Netperfmon/AckAlert.aspx?AlertDefID='+tostring(AlertObjects.AlertObjectID)+'" target="_blank">ClickToAck</a>' AS AcknowledgeIt, AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType, AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption, ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message, AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote, Case When Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+'d '+ ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+ ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ') When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+ ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ') When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ') Else '' End AS ActiveTime FROM Orion.AlertObjects (nolock=true) AlertObjects INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID INNER JOIN Orion.NodesCustomProperties (nolock=true) NodeCP ON AlertObjects.RelatedNodeID = NodeCP.NodeID INNER JOIN Orion.Nodes (nolock=true) Nodes ON AlertObjects.RelatedNodeID = Nodes.NodeID Order By AlertConfigurations.Name, AlertObjects.EntityCaption .AlertObjectID INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID INNER JOIN Orion.NodesCustomProperties (nolock=true) NodeCP ON AlertObjects.RelatedNodeID = NodeCP.NodeID INNER JOIN Orion.Nodes (nolock=true) Nodes ON AlertObjects.RelatedNodeID = Nodes.NodeID Order By AlertConfigurations.Name, AlertObjects.EntityCaption