cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Highlighted
Level 17

Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

nahashondiaz-nappy-.jpg

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=aHR0cHM6Ly9vcmlvbmRlbW8uc29...).

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=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
Leon Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

10 Replies
Highlighted

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

Great stuff, adatole​. I am a big fan of finding/rebuilding default widgets/resources in SWQL. I'll be adding this one to my list of things. Thanks!

Highlighted
Level 15

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

Thanks for the post Leon

Highlighted

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

Awesome! This post contains using what's already in Orion, with everyone's favorite "friendly neighborhood Spider-Man" of Orion: Custom Properties! I'll be stealing this for future use! Thanks for sharing, adatole​!


-Jez Marsh
Highlighted

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

Thank goodness you provided links to episodes 1, 2, and 3 in your 4th installment.  I somehow missed these earlier submissions.

Highlighted
Level 13

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

Thanks Leon.  I wouldn't have found this without your Geek Speak post.

Highlighted
Level 9

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

I think I did not have the chance to get back to you on this report, but there is one column I had to replace so that the NOTES would show up.

So from using 'AlertActive.AcknowledgedNote', I had to replace it with 'AlertObjects.AlertNote'. And the notes would show up.

And thinking into it, using the 'AlertObjects.AlertNote' column makes a lot of sense just by looking at the behavior of Alert Notes when using the Web console. All the previous notes stay no matter how many times the alert triggers and closes and re-triggers again.

We made good use of this Notes, by letting our NOC put in the date and username whenever they add new entries to it. So when the next shift comes in, they would already have an idea on what was done during the previous shift if the alert re-triggers.

Highlighted
Level 17

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

That's a fantastic addition. I'm making a note of it. (#SeeWhatIDidThere #CouldntHelpMyself)

Leon Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

0 Kudos
Highlighted

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

You could even go one step further than that if you liked by using a CASE Statement:

CASE
     WHEN AlertActive.AcknowledgedNote IS NULL THEN AlertObjects.AlertNote
     ELSE AlertActive.AcknowledgedNote
     END AS [Notes]
- David Smith
Highlighted
Level 17

Re: Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras

that is a sweet and simple little bit of code. Thank you for sharing!

Leon Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

0 Kudos