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

Add simple HTML to SWQL query

Jump to solution

Hi All,

 

I've looked around to find a way to format my result from a SWQL query (font size and color modification) so it pops out more on one of our monitoring pages.  I saw this article... https://thwack.solarwinds.com/t5/NPM-Documents/Custom-Query-resource-with-colors-and-styles/ta-p/528... but was hoping there was a way it could be done without modifying the CustomQuery.js file on the Orion server as I am not able to do so in fear of what it would do to all our other reports that are in production at this time.  Here is my SWQL query...

SELECT top 1 ActiveSessions
FROM Orion.ASA.RemoteAccessDetail Where NodeID=63 ORDER BY ObservationTimestamp DESC

The result were are looking for and obtained is a single numeric value for number of active sessions on VPN.  Problem is that the number is so tiny it doesn't pop out on our page so I would like to make the font larger and maybe a different color as well. 

Can someone (perhaps @adatole since I've seen you do some HTML stuff with SWQL already) recommend a way that this can be performed?

 

0 Kudos
1 Solution

Accepted Solutions
Highlighted

Re: Add simple HTML to SWQL query

Jump to solution
So the method to get swql data into the custom html resources is to use javascript. That's probably kind of overkill for this scenario though.

Another trick is to embed html formatting into a custom table resource and then check the box to enable html, similar to how it works in this example, https://thwack.solarwinds.com/t5/NPM-Documents/Node-Details-SQL/ta-p/512112
- Marc Netterfield, Github

View solution in original post

6 Replies
Highlighted

Re: Add simple HTML to SWQL query

Jump to solution
So the method to get swql data into the custom html resources is to use javascript. That's probably kind of overkill for this scenario though.

Another trick is to embed html formatting into a custom table resource and then check the box to enable html, similar to how it works in this example, https://thwack.solarwinds.com/t5/NPM-Documents/Node-Details-SQL/ta-p/512112
- Marc Netterfield, Github

View solution in original post

Highlighted
Level 9

Re: Add simple HTML to SWQL query

Jump to solution

Thanks Marc @mesverrum !!!  I'm assuming you are talking about the CONCAT function.  I will give that a spin and let you know how it goes.  Unfortunately I'm not very good with SQL or SWQL so I'l probably be hacking for a bit before you hear back from me.  But this link and your response definitely give me hope.  Stay tuned to find out what happens to our noob superhero...

0 Kudos
Highlighted
Level 9

Re: Add simple HTML to SWQL query

Jump to solution

Hey Marc and community,

I promised I'd post back when I squared away the SWQL based on the article provided.  As I mentioned since I'm not a SQL/SWQL guy I might have to do a little of trial and error. It took a bit of time to get exactly what I was looking to do but we did it.  So to save anyone else some time that might be in my same shoes looking to add a little simple HTML to their query this is what worked for me.

Here is the code I used to make this one value a different color and size and style.

 

SELECT Top 1 concat('<b style="color:SpringGreen;font-size:250%;">',ActiveSessions,'</b>') AS ActiveSessions
FROM Orion.ASA.RemoteAccessDetail Where NodeID=69 ORDER BY ObservationTimestamp DESC

 

To start I used <b> instead of <p> because <p> eats up too much room above and below the line of text.  I wanted to keep the table as small as possible so <b> did the trick.  The <b> also made the value bold which was one of the objectives. 

Also to use CONCAT() I learned from trial and error that I had to add "AS something" at the end of the line as you can see above.

Finally, any HTML needs to be within single quotes and the value (in my case ActiveSessions) has to be outside of those single quotes and have commas on either side.

Pardon my SQL ignorance if I did not use the right terminology in describing the above.  Just thought I should document this for anyone else who might be having a hard time with doing something like this.

 

A HUGE THANK YOU to @mesverrum for pointing me in the right direction and to this awesome community who posted other relevant articles that helped me through this and many other SolarWinds questions!!!

Highlighted

Re: Add simple HTML to SWQL query

Jump to solution

If you want to go further down the rabbit hole you could also use CASE WHEN ELSE END type logic to make the way it displays conditional, so have the font change size and color to reflect the severity of the threshold breach and turn green when things are fine.

- Marc Netterfield, Github
Highlighted
Level 9

Re: Add simple HTML to SWQL query

Jump to solution

Actually....  That may be a rabbit hole I may be asked to venture down based on conversations I'm hearing.  Do you happen to have any links to examples that I can peruse through to get an idea on how to go about doing that?

0 Kudos
Highlighted

Re: Add simple HTML to SWQL query

Jump to solution
I don't have a good thread to refer to as an example but its fairly simple to describe with your query. So inside the part where you are concatting the html together you can add a case statement like this to produce different html settings based on the value of that activesessions counter. You can string these cases together as elaborately as you want to get the desired outcomes.

SELECT Top 1
concat('<b style="color:"', case when ActiveSessions > 1000 then 'Red' else 'SpringGreen' end, '";font-size:250%;">',ActiveSessions,'</b>')
AS ActiveSessions
FROM Orion.ASA.RemoteAccessDetail
Where NodeID=69
ORDER BY ObservationTimestamp DESC
- Marc Netterfield, Github
0 Kudos