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

How-To: Insert Google Charts within Custom HTML

So a member of my team was working on a customer request where they wanted the default 'Network Wide Availability Chart' to show the values plotted on the chart so that when it was exported to PDF for management presentations they could see the actual numbers. Now, this isn't something SolarWinds is able to do out of the box. So we had to think "outside the box". After a quick chat with a fellow MVP wluther​ we came to the conclusion we could use Google Charts to accomplish this.

The end result should look something like this:

Multiple Charts Per Widget:

pastedImage_6.png

Multiple Widgets Per Page

pastedImage_3.png

If you too would like to be able to have something similar to this then I have some good news for you, adding this to your own platform is pretty simple. Here is how:

This mod was performed on Orion Platform 2018.4 HF3, NPM12.4

WHAT DO YOU NEED?

  1. Access to manage views in your Orion environment
  2. The "Custom HTML" resource added to a view
  3. A working method to copy text from one of the attached files
  4. A working method to paste text, copied in #3 above, into a custom HTML resource, from #2 above.

Before we begin, (while the following is certainly a good practice, it actually doesn't apply to this customization, for once.)

PLEASE don't edit the system files/database without backing them up first.

     STEPS:

     -Download/Open the attached file.

     -Log in to your Orion environment.

          -Navigate to the "Custom HTML" resource you want to display the calendar.

               -Click Edit.

     -Copy the contents of the attached file.

     -Paste the contents of the attached file into the "Custom HTML" resource you have opened to edit.

     -Click to save the resource edit.

     -Enjoy your new Availability dashboard.

** If you decide to use the 2 individual widget charts and you want them on the same page, you need to remove Line 3 (Below) from any additional widgets after the first one (You only need it once per page).

<SCRIPT TYPE="text/javascript" src="https://www.gstatic.com/charts/loader.js"></SCRIPT>

This is in essence, just a simple Vertical Bar Chart using an SWQL Source to populate the data. If you want to present different data then just go ahead and update the query, and the chart should reflect it too. The query I used was situational, and could easily be updated to add filters or provide additional or totally different data. The two queries I used are as follows:

SELECT
SUBSTRING(TOSTRING(DATETRUNC('Day',DATETIME)),0,8) AS [Day]
,ROUND(AVG(Availability),2) AS [Average]
,TOSTRING(ROUND(AVG(Availability),0)) AS [Label]
,CASE WHEN AVG(Availability) <= 85 THEN 'Red' WHEN AVG(Availability) < 90 THEN 'Yellow' ELSE 'Green' END AS [State]
    FROM Orion.ResponseTime WHERE (MONTHDIFF(DATETIME,(GETUTCDATE()))=1)
    GROUP BY DATETRUNC('DAY',DATETIME) ORDER BY [Day]

AND

SELECT
CONCAT(SUBSTRING(TOSTRING(DATETRUNC('MONTH',DATETIME)),1,4),SUBSTRING(TOSTRING(DATETRUNC('MONTH',DATETIME)),8,4)) AS [Month]
,ROUND(AVG(Availability),2) AS [Average], TOSTRING(ROUND(AVG(Availability),2)) AS [Label]
,CASE WHEN AVG(Availability) <= 85 THEN 'Red' WHEN AVG(Availability) < 90 THEN 'Yellow' ELSE 'Green' END AS [State]
    FROM Orion.ResponseTime WHERE (YEARDIFF(DATETIME,(GETUTCDATE()))=0)
    GROUP BY DATETRUNC('MONTH',DATETIME) ORDER BY DATETRUNC('MONTH',DATETIME) ASC

SELECT

Again I would like to say a massive thank you to wluther​ for working on this for me, and also KMSigma​ for dropping a small bombshell on us both to help with a small change that can make a large improvement - This was how to enable multiple widgets to load on a single view. Not to mention of course the entire MVP Crew for supporting a new MVP like me

LINKS TO OTHER GOODNESS:

Here are a few links to other related posts that contain similar work which will hopefully help inspire you as they did me:

Here are various examples from wluther

The very first version of Google Charts examples, which is a calendar with dynamic pie charts and table data. Using Your Custom HTML Resource To Properly Display SWQL Query Results

Here is an example of just the Timeline chart. Using Your Custom HTML Resource To View Events On A Timeline

Here is an example of showing simple table data. How-To: Insert Google Charts (Table Data) within Custom HTML

Here is an example of a Google Pie chart. How-To: Insert Google Charts (Pie Chart) within Custom HTML

Here is an example from jhaas, showing how to track outages on a Calendar and dynamic Timeline chart. Interactive Node Outage tracker 

Here is an example from elevate, showing how to do a Google Pie chart. Google Charts | Pie Charts

  

For more ways to customize your SolarWinds environment, make sure to check out this link, by CourtesyIT​​ - How to do various customizations with your Solarwinds

Thank you

-David

Attachments
Comments

I think this is a super great addition to THWACK, dgsmith80​. You kept everything nice, neat, and easy to follow. Job well done, sir!

Thank you,

-Will

Look at you dgsmith80

Will be giving this a try on my system tomorrow.

This Looks very Nice. I was just hoping if Solarwinds can make their default charts similar to this .

i'm snarfing this . it's awesome

This is cool and really should be a standard feature within the reporting and views.

I know you can hover over charts to get precise figures, but when managers want to print out charts to display on their wall or during a meeting, they don't have the ability to hover over their paper copies.

I have been challenged a few times by project managers and by capacity managers who ask me why Solarwinds doesn't show figures on charts like this...

I have always sat down with the users and showed them how to hover over the chart and get the figures, often i've created a table to accompany/support the chart with the figures in it.

This way you have combined both in one chart which would solve the problem. I'll steal this.

Internet access is the biggest caveat. But yes I previously was putting a table below the chart.

You can always ping meech​, kbongi​, or any of the other UX folks, as well as the product managers... And just keep on pinging them... That's what I do.

Beautiful!  Thank you for all the work!

Thank you for creating this.

How can this be modified to only see the graphs for Vendor=Cisco?

I do have also an issue here.

Msg 195, Level 15, State 10, Line 2

'DATETRUNC' is not a recognized built-in function name.

Update the WHERE statement on line 10, i did this for something similar but what you need it to say is:

FROM Orion.ResponseTime WHERE (MONTHDIFF(DATETIME,(GETUTCDATE()))=1) AND Vendor = 'Cisco'\

Not sure about your issue as it worked fine for me.

This is not working, because the vendor is not located in the same table.

it's located in nodes table, so i probably need to use join, but i don't know how.

What a neat solution. Great work!

Oops sorry forgot i have a join in there, replace the whole query with this:

var swql="SELECT SUBSTRING(TOSTRING(DATETRUNC('Day',DATETIME)),0,8) AS [Day]\

,ROUND(AVG(RT.Availability),3) AS [Average]\

,TOSTRING(ROUND(AVG(RT.Availability),2)) AS [Label]\

,CASE WHEN AVG(RT.Availability) <= 95 THEN 'Red' WHEN AVG(RT.Availability) < 98 THEN 'Yellow' ELSE 'Green' END AS [State]\

FROM Orion.ResponseTime RT LEFT JOIN Orion.Nodes N ON N.NodeID = RT.NodeID\

                                       WHERE (DAYDIFF(DATETIME,(GETUTCDATE()))<31) AND N.Vendor = 'Cisco'\

GROUP BY DATETRUNC('DAY',DATETIME) ORDER BY [Day]\

"

This is exactly what i need thank you very much

Technically, with this being a SWQL query, you can actually use the native implied join, which would just be:

AND ResponseTime.Node.Vendor = 'Cisco'

pastedImage_0.png

That should get you the same results, just with less typing. But, either way will get you where you need to be.


Thank you,

-Will

Would it be possible to use this on a Group Summary page using the Group Name to filter the results?

pgaryga​ I think something like this should work.

SELECT SUBSTRING(TOSTRING(DATETRUNC('Day',DATETIME)),0,8) AS Date, ROUND(AVG(PercentMembersAvailability),2) AS [Average], TOSTRING(ROUND(AVG(PercentMembersAvailability),2)) AS Name\
,CASE WHEN AVG(PercentMembersAvailability) < 95 THEN 'Red' WHEN AVG(PercentMembersAvailability) < 97 THEN 'Orange' WHEN AVG(PercentMembersAvailability) < 99 THEN 'Yellow' ELSE 'Green' END AS [State]\
FROM Orion.ContainerStatus WHERE (MONTHDIFF(DATETIME,(GETUTCDATE()))=1) AND ContainerID=${ID}\
GROUP BY DATETRUNC('DAY',DATETIME) ORDER BY Date"

Though, you may need to tweak/change the calculations, as I didn't have much time to verify accuracy. But the ContainerID=${ID} part should pull the stats for the group currently loaded.

i went with the traditional join as i needed to leverage custom properties values and didn't fancy going with Orion.Responsetime.Node.customproperties but agree that is another way to do it.

Wow awesome to come back only a couple of days later and see so many different use cases being opened up Love it. Well done all keep it up.

Hi David,

your document is really helpful.

Wondering this availability graph is for all monitored devices availability or it's also calculating the Application & Hardware availability?

Thanks,

Alankar

Maybe someone can help me - I am trying to do something very simple create a page with interface utilisation and errors and discards graphs from various different devices all pulled together in to one page. I successfully did this a year ago by putting HTML like this into the custom HTML widget and it worked perfectly...

<HTML>

<img src="https://alpvssolpol/Orion/NetPerfMon/Chart.aspx?

ChartName=Discards&NetObject=I:80389&ResourceID=2531&NetObjectPrefix=I&Rows=&Title=&SubTitle=&SubTitle2=&Period=Last

%2024%20Hours&CustomPollerID=&SampleSize=5M&ShowTrend=True&FontSize=1&PlotStyle=&SubsetColor=&RYSubsetColor=&Width=605&timeStamp=636428011803324475&ReturnTo=L09yaW9uL0ludGVyZmFjZXMvSW50ZXJmYWNlRGV0YWlscy5hc3B4P05ldE9iamVjdD1JOjgwMzg5" />

</HTML>

Now I am trying to substitute the img src, beteween " & " and I am getting that broken symobl rather than a chart ehich the URL is pointing to. If I paste the URL into my browser it brings me to the chart but it won't work in this widget and it is driving me mad! Any ideas????

noobes​ Did you break the line? (I cannot tell from your reply)

<HTML>

<img src="https://alpvssolpol/Orion/NetPerfMon/Chart.aspx?ChartName=Discards&NetObject=I:80389&ResourceID=2531..." />

</HTML>

I'm very interested in this but I'm getting a blank widget window that is sized to contain content, but doesn't have anything. No errors. I'm using the HTML content as provided without modifications. Can someone advise on what the problem may be? I'm running 2020.2. @dgsmith80 @wluther 

@LatteLarry Sorry for the delay, I just noticed your post here. I have recently upgraded to 2020.2, so we should both be running/testing against the same version. I was able to copy/paste the contents of the "Vertical_Chart_Daily_Availability_Single.txt" file, attached to the OP at top, into a "Custom HTML" widget on a view.

I was able to generate the graph, so I know this still works on the following version(s):
Orion Platform HF1, SCM, NCM, NPM HF1, DPAIM, VMAN, SAM HF1: 2020.2

In regards to functionality, your SolarWinds products are actually not doing much more than providing the data (from the SWQL query within the HTML/JavaScript code of the attached files), and providing a place to load/process the HTML/JavaScript code.

wluther_0-1598553399375.png


Since the actual graph and graphing functions are coming from an external source (in this case, Google), and double check to make sure you're able to access those site from your Orion server/web servers.

Additionally, the SWQL queries, as they are in the files, are possibly going to request a large set of data, depending on your environment. I'd recommend you add in a "TOP 50", just after the initial "SELECT" part of the query, so you can start with just a limited amount of data.

Also, which file did you copy the contents from, and what else is on the page/view with it? If your view has any other JS code (other than the default stuff), you may need to remove the starting JS references.

 

You can also check the console of the browser (F12 in Chrome, or right click and choose "Inspect"), and verify it has the data you want. If it does, but you don't have graphs, then there may be an issue with your web servers reaching the Google charts stuff. If there isn't any data, then you're not making it to/back from your DB.

 

 

Not sure why this screenshot didn't make it along with the previous post, but here it is now. For those unfamiliar with the browser console, it just shows the raw data which would/could be loaded onto the page.

wluther_0-1598554368242.png

 

Version history
Revision #:
1 of 1
Last update:
‎05-20-2019 12:11 PM
Updated by: