Skip to content

Leon Adato

DevRel is Technically Marketing

Menu
  • Home
  • My Work “Out There”
  • Speaking
  • About
    • About Leon
    • About AdatoSystems
  • Contact
  • Lists & Trackers
    • About the Weekly Job list
    • Job Listing Posts
    • CFP Tracker (full list)
    • CFP Listing Posts
Menu

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

Posted on December 30, 2019March 6, 2023 by Leon

(“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  

Share this:

  • Click to email a link to a friend (Opens in new window) Email
  • Click to print (Opens in new window) Print
  • Click to share on X (Opens in new window) X
  • Click to share on Facebook (Opens in new window) Facebook
  • Click to share on LinkedIn (Opens in new window) LinkedIn
  • Click to share on Reddit (Opens in new window) Reddit

Like this:

Like Loading...
  • Bluesky
  • LinkedIn
  • Mastodon
  • GitHub
  • RSS Feed

Seek and you shall find

Subscribe for emails about new jobs, CFPs, and more.

Show Your Support

Let me clear: I don't want ANYONE paying if they don't want to, or if money is tight. I do this because it makes me happy to help. At the same time, some folks have asked how they can show appreciation. So here are some options if you are so moved.

  • Make a donation to my synagogue
  • Make a donation to the cause of your choice, and just mention me in your donation note.
  • Schedule yourself to donate blood.
  • Buy me Kofi

General Interest

  • My resume
  • Speaking information

What I’m Working On

  • Buy Now!
    • - Amazon
    • - Barnes&Noble
    • - Smashwords
    • - OverDrive Library

  • Listen to the Technically Religious Podcast

Post Categories

©2025 Leon Adato | Design: Newspaperly WordPress Theme
%d