Advanced Alert Reporting part 3: Hyperlinks in Data Output

As I mentioned in a previous post (Advanced Reporting part 2: Making Web-Based Reports Do Your Bidding ), I was asked by a customer to help them create a screen that showed the active alerts, could be sorted by custom fields, and had an “acknowledge” button onscreen for the NOC to use. In the course of creating a solution, I realized that the skills needed to address this need weren’t particularly complex or specialized, but they relied on the monitoring specialist to be familiar with certain fundamental aspects of the Orion Platform. Once again, here’s a map of what I intend to cover in this series:

1. Making customizations to the data that feeds a web-based report (that was the last post)
2. Adding HTML links to the data returned in a report, so items are clickable (you are here!)
3. Creation an “action” column in a report that lets you perform a management task, such as acknowledging an alert (coming soon)

In my first deep-dive on this process, (Advanced Reporting Part 1: Re-Creating the "All Alerts" Resource With Extras ) I covered the techniques you need to customize a report by adding new data sources (tables), and fields. Now it’s time for me to show you how to add HTML code into your SWQL query so that the resulting data set includes clickable elements.

What Are We Trying To Do?

While there are a lot of data points that could potentially use a clickable link in a report, I’m going to go with a fairly simple and obvious one for starters. Once you have the concept, you’ll see how easily it can be applied in so many other situations. For this example, I’m going to make the Node Name clickable, so that clicking it takes you to the Node Details page for that device.

“First, Get a Million Dollars...” - Steve Martin

“You can be a millionaire and never pay taxes!

You say... “Steve, how can I be a millionaire and never pay taxes?”

“First, get a million dollars...”

•          from Steve Martin’s “Cruel Shoes”

(https://www.youtube.com/watch?v=zXmQW_aqBks)

As with my previous post, I’m going to be modifying the “All Alerts” report. Right now, the column called “caption” is actually the name of whatever element (interface, disk, node, application, etc.) that triggered the alert. So, the first part of this process, somewhat obviously, is to add the node name to the report.

As with the “Importance” field, the first thing I need to do is figure out which table has the field I want. Unlike the previous post, this one is a no-brainer: the Orion.Nodes table.

Once again, I’m going to test this out in SWQL Studio before copy/pasting the final version into the edited report.

I’m going to add a JOIN to include the Nodes table to the query.

And add the node name (i.e., “Caption”) field to the output. Note that I’m going to relabel it “NodeName” so it’s differentiated from the existing “Caption” field.

After adding the new column to my report, everything is working fine. Now let’s dig in to how to make this clickable.

Key Concepts

Before diving into the specific steps, I want to clarify the basic skills and knowledge I’m drawing on to make this happen:

1. HTML. You should understand how tags work, specifically the <A HREF=””> tag (HTML a href Attribute)
2. Literals in a SQL query (How to Use Literal Character Strings in SQL SELECT Statement for MySQL)
3. Concatenating multiple elements in a SQL query (+ (String Concatenation) (Transact-SQL) - SQL Server | Microsoft Docs)

  style="margin-bottom:.0001pt"

• Add both of those fields to the report
• Click the “Advanced” arrow for the DetailsURL column and click the “Hide” checkbox
• Now, click the “Advanced” arrow for the NodeName column
• In the Display Settings drop-down, select “Details Page Link”

Because we only have one DetailsURL link, Orion Report Writer knows what we mean, and the Node names will be clickable.

And honestly, this is the right way to do things if all you want is a clickable column. I’m only showing you the more roundabout way because I want you to understand the techniques to add HTML to your query output, because we’re going to use that in the next post.

Meanwhile, Back at the SQL

OK, so just as a reminder, what we’re doing here is creating a field that lists the node name and is clickable in the report.

• The node name field is “Nodes.Caption” (or more specifically, “Nodes.Caption AS NodeName”)
• The field that has the Node url is Nodes.DetailsURL
• A web link uses the <A HTTP=”URL”>
• Surround literal elements in a SQL query with a single quote
• Combining elements together into a single output (i.e., “concatenation”) in a SQL query uses the + symbol

With all that said, the following line should make sense:

‘<A HREF=”’+AlertObjects.EntityDetailsURL+’” target=”_blank”>’+Nodes.Caption+’</a>’ AS NodeName,

Adding that to the All Alerts Report query we’ve been building. At the same time, you can remove the Nodes.Caption AS NodeName and Nodes.DetailsURL fields since we’ve effectively combined both of them into a single field output.

Adding the new column to the table will yield a report that looks like this:

Obviously, we have a problem with display. Click the “Advanced” arrow for the new NodeName column and check the “Allow HTML Tags” checkbox.

This will cause Orion to interpret the HTML tags correctly and give you a report that looks more like this:

The Mostly Un-Necessary Summary

At this point you know how to add fields from multiple data sources and make the data elements clickable if you want. Stay with me for the last part of this journey, where we’ll add a column for a clickable “Acknowledge Alert” action, so this All Alerts report is truly interactive.