cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
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
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

10 Replies

You have to be using the custom table widget with a custom SQL or SWQL data source, it's the only SWQL one with an option to tick the box to allow HTML to render correctly.  Something like this would work as an example:

SELECT case when ComponentStatisticData > 90 then concat('<img src="/Orion/images/StatusIcons/Small-Critical.gif">',ComponentStatisticData)

else concat('<img src="/Orion/images/StatusIcons/Small-Up.gif">',ComponentStatisticData)

end as [StatisticData]
FROM Orion.APM.CurrentStatistics
Where ApplicationID = 1038

- Marc Netterfield, Github
0 Kudos
@mesverrum
I was hoping to do it using the custom html widget so I would have more options for formatting.. We are displaying the value on a display board and I was planning to make the for quite large. Is there a way to accomplish it on with Java script?

Thanks for the quick reply

0 Kudos

If you are strong in javascript then you can just make ajax calls to the solarwinds API and then display it however you like. 

 

This is one good example of feeding a javascript resource with SWQL data from @wluther who has done more of this than anyone I have talked to.

https://thwack.solarwinds.com/t5/NPM-Documents/Using-Your-Custom-HTML-Resource-To-Properly-Display-S...

- Marc Netterfield, Github
0 Kudos
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

@mesverrum 

I have been trying to figure getting a value returned from a SWQL query to display on a Custom HTML page.

Do you have a template I can use?  I have tried a few different ways and it never returns anything

My query is 

SELECT ComponentStatisticData
FROM Orion.APM.CurrentStatistics
Where ApplicationID = 1038

Thanks for all your help

 

0 Kudos

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!!!

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

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

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