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

Using Your Custom HTML Resource To Properly Display SWQL Query Results

**2019.05.29 | Updated Attachment: In addition to the 2 previous versions(WITH and WITHOUT the node custom properties for Area & Department), I have added a 3rd option which shows recent events below the pie charts.**

     *Also, I have corrected/updated SWQL queries for the pie charts, which should now properly group results. (previously pie charts would have multiple results with same value(s))*

Please see this post for an example and/or further details:  https://thwack.solarwinds.com/docs/DOC-202667/upload?draftID=351232#comment-325250

**2018.07.13 | Updated Attachment to include a version WITH and WITHOUT the node custom properties for Area & Department, as those CPs were causing some confusion for some users**

     *Also, I have corrected/updated SWQL queries for the pie charts, which should now properly group results. (previously pie charts would have multiple results with same value(s))*

     *Copy/Use lines 1-199 to show only 2 graphs (MachineType & Vendor)*

     *Copy/Use lines 200+ to show all 4 graphs (NodeCustomProperties.Area & Department, as well as MachineType & Vendor)*

Please see this post (below) for further details: https://thwack.solarwinds.com/docs/DOC-202667#comment-304566

Trying something different on this post, so let's make everything easy, and just jump right into it.

If you want to display data like this:

pastedImage_0.png

Then keep reading...

Over the past many years, I have wanted to be able to view my data in a few ways, specific to how my brain malfunctions. I have searched long and hard, thinking I would never make it to the promised land. A few days ago, conguir​ mentioned something in regards to having to use HTML/REST API queries to achieve a display they need. (Re: Simple NetPath Summary Widget​​ )

Now, I'm always looking for different ways to pull the soul out of my SolarWinds data, and show it on my screen. My coworker, mblackburn​​, has helped me with various iterations of displaying data in such ways. We have gone from having a separate server running code, pulling it into Orion via iframe, to building out multiple combinations of .net pages using variations of highchart files/versions, dumping data here and there, as well as a few other inefficient methods. Each time we revisit this project, we seem to somehow trim the fat, and figure out a better (for us) way to accomplish the task. This latest revision, the one you are reading about now, is our closet attempt yet. While we still have some things to improve, we are finally able to view our data in such a way, customized to us, being able to easily view the events occurring within our Orion environment.

The best part about this is there is nothing to install, restart, break, bend, boil... Just a simple copy and paste into a default resource, functioning exactly as it was meant to function.

ESTIMATED TIME TO INSTALL/PERFORM MODIFICATION: <5 Minutes

DIFFICULTY LEVEL: 1-Youngling

  1. Youngling(Easiest/Most Basic; no coding experience required, no config wizard required, no system restart required, no system downtime.)
  2. Padawan (Easy/Basic; no coding experience required, possible config wizard required, possible system/services restart required, limited/no downtime.)
  3. Jedi Knight (Moderately Difficult/Advanced; some coding experience required/recommended, config wizard required, possible system/services restart required, limited/short duration downtime.)
  4. Jedi Master (Most Difficult/Advanced; advanced coding experience required, config wizard required, system/services restarts required, 30+ minutes downtime/maintenance window recommended, and other things that I do not even know I would need to know, required...)

This mod was performed on the following SolarWinds environment/versions: (It may, or may not work on other versions)

Orion Platform 2017.3.5 SP5, NCM 7.7, DPAIM 11.1.0, NPM 12.2, VMAN 8.2.0, NetPath 1.1.2, QoE 2.4, CloudMonitoring 2.0.0, SAM 6.6.0 © 1999-2018 SolarWinds Worldwide, LLC. All Rights Reserved.

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 the attached file
  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.

If you see a friend or co-worker making changes without backing up first, please alert the authorities.

Friends don't let friends mod without backups.

"If it's not broke, then fix it until it is."

     -The smartest person ever

STEPS:

     -Download/Open the attached file.

     -Login to you 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 you new calendar view.

**The various SWQL queries, within the attached file, mostly reference default table/column names. There are, however, references to NODE Custom Properties "Area" & "Department". You can either go into the file and change the column, or create the columns in your environment. (you don't have to use them, but they will need to exist)**

The queries, as they are in the attached text, simply pull ALL alerts for the current year. I know the SWQL is not as efficient as it could/should be, however, we built it this way to work for us in a manner that would be flexible enough for us while we tested. Feel free to adjust/correct to your liking.

When the page loads, it starts with only the calendar displayed. (This calendar displays the days vertically, instead of the traditional horizontal layout) The calendar is showing ALL alerts triggered (up, down, left, right, node, meow, int, vader, etc.), broken down and grouped by day. The more total alerts per day, the closer (darker?) the color changes to red.

pastedImage_24.png

Once you click on a day, the data will be rolled down into pie charts, grouping each by a different metric. (Area, Department, MachineType, Vendor) This allows us to easily view the global trouble maker(s), showing us who/what did the most damage that day.

pastedImage_25.png

Clicking on different days will automatically refresh the pie charts to reflect the data from whichever day you select. (I still need to correct/adjust the grouping part of the SWQL queries for the pie charts, as they should not show multiple entries for the same results. ie. vendor should only show 1 of each vendor, but it currently shows some vendors multiple times.)

Anyway, that is just about it. Copy, paste, adjust the SWQL to meet your needs, and enjoy. There is really nothing else to it.

The following people have helped build this, knowingly or otherwise. mblackburn​, conguir​, mesverrum​, zackm​, nickzourdos​, CourtesyIT​, antonis.athanasiou​, tdanner​, mrxinu​, KMSigma​, jnathlich12​, equalswql​, monovex​, Yossarian​ (Yikes, I'm probably leaving some folks out here... Sorry.)

Here are a few links to some Thwack posts I have read over the years, each filling in another piece of the puzzle for me.

Generating Graphs Dynamically

Custom SQL Dynamic Graphing Resource

How to create a simple custom view of multiple interfaces' bandwidth utilization

Custom Graphing: Fun With Highcharts

Re: Multi-column Custom HTML Page

Orion SDK - Is there a way to generate a interface graph as a JPG/PNG/GIF via the SDK?

Custom Sparkline Charts

Re: How to modify size of interface utilization charts?

Re: 1 Page - All interface Graphs Wanted ... Help me kill MRTG

Re: HTML/Javacript

Re: Javascript in a Custom HTML Resource and the API

Interface aggregate chart (swql)

Using SWis query to get interface utilization

Moving Content from View to View

Creating views using OrionSDK REST API's

Re: Showing Graph from different nodes

Possible to embed SWQL inside of custom HTML

Well, here you go, meechaLTeReGocobrienserena

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,

-Will

Attachments
Comments

wluther​, Wowaweewah! You are definitely earning your keep as a ThwackMVP!!!

I haven't even read full through the code yet, but I'm already really impressed. I'm looking forward to digging into the details and using this as a good example of what can be accomplished through the Custom HTML resource. Thank you for this, and for the mention as well!

This looks amazing, congratulations.

Now time for me to add another resource showing alerts by alert definition

What a cool customization!  I'm going to feature this content for a while.  Thanks!

Not bad.

mblackburn​ Yeah, it would have been better if we had an actual programmer instead of some nerf herding moof milker...

wluther​ maybe one day he will evolve into one.

wluthermblackburn where do we edit which custom properties filter the alerts? When I click, I don't get pie charts lol. Also, very awesome.

All of he data displayed here is done via swql query to get the data and in some cases manipulated by javascript.  This example runs a swql query displays the calendar with dates.  Each time you click a date it runs three additional queries and then draws three pi charts in three separate div tags.  This fiuld be done with one query probably but my main idea was to get it working first then improve it.  I have a long list of things that I want to create now.  One idea is create a set of queries that will result in interesting interfaces grouped by function or network area etc then loop through the queries and add a new interface utilization bandwidth graph every few seconds for a scrolling network utilization dashboard type view.

More cool cool stuff to come.  Typing on my phone now so sorry for any typos etc.

designerfx  there should be 4 different SWQL queries in the attached file, one for each graph. I believe you would need to update all of them to match your environment. Is that what you are asking, or did you do that and it not work?

I think I meant to say 5, 1 query for each graph.

Hey wluther  thank you this is amazing. Is there a specific screen size that it is optimised for as I noticed when viewing on my laptop it didn't resize automatically.

dgsmith80​ I mostly just did the high level research and the SolarWinds stuff. mblackburn​ did the deep dive and actual JS work, so he would probably know better than me. Having said that, I believe it might be a combination of the div settings (towards the end/bottom of the file contents, line ~253) and the "calendar: { cellsize: 30 }," line(s) (toward the top/beginning, line ~45).

If you change the following line:

calendar: { cellsize: 30 },

To something like
calendar: { cellsize: 15 },

I think it will reduce the size of the actual calendar graphic part, which would still live within the div settings. Afterwards, I think you would probably need to go in and adjust the "<div id="calendar_basic" style="width: 100%; height: 450px;"></div>" line (located around line ~253) which should allow you to adjust the bubble of space the graphic uses within the div... Or something like that.

Let us know how it goes, and if you have any issues, please.

Thank you,

-Will

I didn't modify any of the queries. I do have machinetype but clicking on a date gives me no pie charts wluther

making more sense now, fiddling with this a little

*EDITED*

<div id="container" style width:2000px; height:100%>

this line right here will tell it to be pretty wide.  If width was your issue perhaps tweak it.  In case I am looking at a newer copy and the code wluther posted has:

<div id="container" style width:1000px; height:100%>----since I was too lazy to check.

Same thing..lol

cellsize makes the cells in the calendar bigger because our initial default values were very tiny and we did not like it.

This:

<div id="calendar_basic" style="width: 100%; height: 450px;"></div>  only impacts the div container that the calendar is in not the pie charts and since it is at 100% it will fill whatever it is in widthwise (<div id="container" style width:2000px; height:100%>)  which is what you need to change to make it less wide.  You can impact the height in this div but you will need to tweak the ratio between div height and calendar size settings so that your container is full but not wasting space.

Hope that helps.

Did you make a div tag for each pie chart?  the charts script needs a div as a container for the chart with unique id.  It needs to be inside a function tied to the click event.  Send me your code and ill look.

Nice idea !! but i can't see the calendar on my LAB environment.... I missed something ?

pastedImage_0.png

Does your query return results?  Did you formate your date correctly?  Look at browser console for errors if your query returned results

Actually i see that you did get results.  I would verify your date formating to make sure the calendar understands the date column.  Also if the div height is not sufficient it wont properly display the calendary.

Thanks, I look this and i return you soon

send me your code if you want and ill look

So I was playing around a little today and I made a custom html report page that allows me to pull the top xx (20 in this example)  by the following categories:

MaxInBpsToday

MaxOutBpsToday

Outbps

Inbps,

Bps

OutPercentUtil

InPercentUtil

PercentUtil

OutPps

InPps

OutUcastPps

OutMcastPps

InUcastPps,

InMcastPps

InDiscardsThisHour

InDiscardsToday

InErrorsThisHour

InErrorsToday

OutDiscardsThisHour

OutDiscardsToday

OutErrorsThisHour

OutErrorsToday

CRCAlignErrorsThisHour

CRCAlignErrorsToday

LateCollisionsThisHour

LateCollisionsToday

Just because I found those interesting to me.  Anyway it is a form with a select box you select one and hit submit and you get a nice bar chart showing the top 20 by that stat.  I am going to add options for custom properties so that this view could be easily filtered and also allow the user to set the top xx number by giving options like 10,20,50,100 or whatever.  Maybe let them just type a number but defining some presets is probably best.  See screenshots attached and tell me what you think.  Each time you select a new option and hit submit the bar chart is quickly redrawn.  I filtered the interface names to protect proprietary information.  You should get the idea from the screenshots.

Enjoy~Screen Shot 2018-07-05 at 9.10.48 PM.pngScreen Shot 2018-07-05 at 9.11.24 PM.pngScreen Shot 2018-07-05 at 9.11.48 PM.png

mblackburn​ will check today

I'm also not getting any of the pie charts.  I copied and pasted it as posted and hadn't tinkered with it yet.

Very nice! You gonna share

Wouldn't it be cool if instead of a pie chart that a US based geomap was painted, say at the State level. For example. a US MAP shows all 50 states, and the states are lit up with color intensity based on the number of alerts by the same calendar selection or more simply by choosing a 7 day, 30 day, or 3mo selection. Similar to the pie chart that is shown for "Area" but instead, replace with "State" as a CustomProperties, and replace the Google.chart with a US-GeoMap instead of a pie chart.

I attempted this, and while i was successful with creating a geomap of the US as a custom HTML google.chart in Solarwinds, it was based on static content within the html code. I have failed to incorporate the alert data from a sql database and represent it the same way in a Geomap like done in a pie chart.  Its got to be a combination of logic and syntax within the html code and the sql data that is collected that i am failing with.

Sorry to have to punt here but i've hit a wall. I'm calling the powers that be in the Thwack community to see if anyone smarter than me can solve this.

Wouldn't it be cool if instead of a pie chart that a US based geomap was painted, say at the State level. For example. a US MAP shows all 50 states, and the states are lit up with color intensity based on the number of alerts by the same calendar selection or more simply by choosing a 7 day, 30 day, or 3mo selection. Similar to the pie chart that is shown for "Area" but instead, replace with "State" as a CustomProperties, and replace the Google.chart with a US-GeoMap instead of a pie chart.

I attempted this, and while i was successful with creating a geomap of the US as a custom HTML google.chart in Solarwinds, it was based on static content within the html code. I have failed to incorporate the alert data from a sql database and represent it the same way in a Geomap like done in a pie chart.  Its got to be a combination of logic and syntax within the html code and the sql data that is collected that i am failing with.

Sorry to have to punt here but i've hit a wall. I'm calling the powers that be in the Thwack community to see if anyone smarter than me can solve this.

**2018.07.13 | Updated Attachment to include a version WITH and WITHOUT the node custom properties for Area & Department, as those CPs were causing some confusion for some users**

     *Also, I have corrected/updated SWQL queries for the pie charts, which should now properly group results. (previously pie charts would have multiple results with same value(s))*

     *Copy/Use lines 1-199 to show only 2 graphs (MachineType & Vendor)*

     *Copy/Use lines 200+ to show all 4 graphs (NodeCustomProperties.Area & Department, as well as MachineType & Vendor)*

You should see something similar to the screenshot below, when using lines 1-199 of the updated file.

pastedImage_0.png

You should see something similar to this screenshot when you use lines 200+ from the updated file.

(You MUST have "Area" and "Department" NODE CUSTOM PROPERTIES built for this option to work properly)

pastedImage_4.png

Thank you,

-Will

bharris​ I updated the attachment, which now includes 2 different versions, as well as adjusted the SWQL queries to refine the results. The attachment now includes a version without the custom properties (only 2 pie charts), as well as the original (4 pie charts).

Try to use the option without custom properties, and see if it works for you.

Also, it will only display the calendar at first load. You will need to click on a day to create and populate the pie charts.

Thank you,

-Will

designerfx​ Did you get this working?

wluther​ way more work emergencies, not a lot of a chance to test. Gonna put on HF3 and then re-look at this. lol.

Nice, opens up a few paths for presenting data more efficiently as a full stack developer/half stack network guy!

Very nice job !  It works great Thanks a lot

Got it working. Thanks!

I figured it out. I modified the original code to draw a US MAP instead of a Pie Chart when clicking on the calendar. So what i have here is a total number of alerts by a custom attribute that i assigned to the nodes called State. I then modified the SQL query to match Node.CustomProperties.State. I then replaced the PieChart code with a Regional MAP code. The trick was to use the same loop code and column code from the pie chart so that the chart can run through all the alerts and make a row for each state and count the alerts for that state.

The result is a US Map that shows all the states, and color in each state with varying levels of color intensity based on the alert count.

Its a fairly large output but would look good in NOC View so I am working on replacing the calendar graph with a simple radio button selection to show the alerts for 7-days, 30-days, 90-days, 1yr. Something simple and common from a date perspective.

pastedImage_0.png

Very Awesome!  Definitely opened my eyes about what kinds of things you can do with just the Custom HTML resource.  Thank you for sharing.

​EDIT: I figured it out on my own.  Thought I'd share:

The problem is in the SWQL Queries.  In the WHERE clause you filter Month to equal the selected month and Day to equal the selected Day and you have the TimeStamp field for both of those wrapped in the TOLOCAL() SWQL Function.  The problem is you don't wrap the TimeStamp fields in your SELECT clause and in your GROUP BY clause in the TOLOCAL() function as well, meaning we're filtering the data to a specific Month/Day timestamp using the local timezone, but you're displaying and grouping the timestamp field in the default SWQL timezone, which is UTC if I'm not mistaken.  That causes there to potentially be results for the same grouping on multiple days.  The solution is to wrap ​all ​of the TimeStamp fields in the TOLOCAL() function (or you could remove the TOLOCAL() function from the WHERE clause, but then you'd be using UTC time for everything and that could be confusing to your users...).  Here's an example of a corrected query for the Department Custom Property grouping:

SELECT SUBSTRING(TOSTRING(DATETRUNC('Day',TOLOCAL(TimeStamp))),0,12) AS Day ,COUNT(*) AS Qty,ISNULL(AlertHistory.AlertObjects.Node.CustomProperties.Department,'Unknown') AS Department FROM Orion.AlertHistory WHERE YEAR(TOLOCAL(TimeStamp)) = YEAR(GETDATE()) AND MONTH(TOLOCAL(TimeStamp)) = "+querymonth+ " AND DAY(TOLOCAL(TimeStamp)) = "+querydate+ " GROUP BY DATETRUNC('Day',TOLOCAL(TimeStamp)),AlertHistory.AlertObjects.Node.CustomProperties.Department ORDER BY DATETRUNC('Day',TOLOCAL(TimeStamp)) DESC,QTY DESC

​This correction needs to be made to the queries for Vendor, MachineType, Department, and Area (or whatever custom property you've replaced Department/Area with, if any).

My original comment:

Got a question about a little bug I'm seeing on my end and that I even see in one of your screenshots.  The pie charts are showing duplicate entries of the same value.  For instance, on your screenshot the pie chart for Vendor on Friday, June 29th 2018 shows duplicate Linux values.  One is Green and one is blue.  We are seeing this in almost all of our pie charts, whether it be the Vendor, MachineType, or the custom properties we use.  I double-checked in the Orion database to see if there were duplicate values that were seemingly similar but maybe different by something small, like an extra space at the end or something like that, but I verified that it's not the case.  It's something in the HTML code that's causing it but I'm just not experienced enough with javascript/ajax/html to figure it out on my own.  Any idea what maybe causing that and how to fix it?

I've even included a few screenshots for clarification:

Here is a clip from your screenshot in your original post:

pastedImage_0.png

And here's a clip from our console, one showing duplicates in one of our Custom Property charts (and I did double-check to make sure there weren't duplicate values in our DB for that property) and one showing duplicates in the MachineType chart:

pastedImage_1.png

xtraspecialj​ I'm glad to hear this is working well for you. In regards to the duplicate results, I believe this was due to the poorly assembled query I put in there. I updated the content last week, so try re-downloading the attachment and try it again.

https://thwack.solarwinds.com/docs/DOC-202667#comment-304566

Thank you,

-Will

bavautom5​ That is very nice. I'll need to look into how to do this on a city/street level.

Oops, I was editing my post to say this at the same time you were replying to me it seems.  Thank you!  I actually just found and downloaded this today, so I have the most current version.  The bugged queries are still there I'm afraid.  I did put a fixed version of the query in my comment above though, so people can copy/paste from there if they want. 

Hey, another question.  Because the calendar always starts at January and ends on December, at the beginning of the year this won't allow for historical alert reporting, correct?  Is there a way to make this more dynamic?  Like, have the beginning of the calendar start 12 months prior to the current month maybe?  So, if the current month was February 2019 for example, the first month on the calendar would be March 2018 to February 2019 at the end.  Would that be easy to do, or is the Google calendar chart not flexible that way?

Same here. We have multiple locations within states. the key for me was simply using Custom properties on my nodes. I set Country, City, State, etc as custom node properties and then used those properties in my sql/group query.

Another angle i'm working on is to replace the calendar with a simple selection of "Today" , "1 Week" , "1 month" , etc to select for a date filter. This would minimize the screen real estate, especially in a NOC view.

Also, am working on a drill down of alerts by device type when selecting a state. I was looking to use your same google pie chart code to further present alerts by device type, vendor, etc when selecting a state.

Lastly, i changed the code to highlight the map in a gradient scale from yellow to red. Boss said green indicates good , not a color you would typically associate with alerts...

pastedImage_1.png

bavautom5​ Yes, we did something similar with our event logs. We use the calendar to pick the day, which populates the pie charts, showing us a break down of where/how things were happening that day. Additionally, it also populates a paged list of the event logs in a simple table format.

Custom_JS_Template_Done - Custom_JS_Template_002_Calendar_Events_20180731_2033.png

This, for whatever reason, loads the events way faster than using the message center (with a few trade-offs, of course). And, it has the added benefit of using the pie charts to help understand the various things occurring that day.

Our main goal on this journey is to find a better way to troubleshoot real events as they are happening. We are working on a few dynamic top 10 pages, which use instant drop down selections to shuffle element data through various graphs, each showing different subsets of whatever metrics are needed at that time.

Thank you,

-Will

xtraspecialj​ I think that is a preset Google thing. I did not find any options for this, as I was definitely looking. I'm sure there is a way to do it, just sitting there out on the interwebs of life, waiting to be utilized. I don't really care to have the entire year up there either, but I have not yet found a way to do it differently. If you find something, please post your method.

Thank you,

-Will

Downloaded it, swapped Area and Department for Building and City, was a total Rock Star HERO to my boss!

Your work here is exemplary--thank you for your efforts and lost brain cells!  You're a winner!

Kudos +++ !!!

rschroeder​ I'm glad to hear you got it up and working with ease. While I'd love to take credit for this, my coworker, mblackburn​, actually did all the heavy lifting. For the most part, the only thing I did was post it on Thwack. mblackburn​ is really the person deserving all the praise.

All praise to mblackburn​ for awesome heavy lifting and altruism!  The world needs more just like you!

This is THE coolest!  Can't wait to try this one out

decent

This looks fantastic and something I'd love to get working in our environment, however I just cannot seem to get it working.

My current environment is detailed below.

SolarWindsOrion Platform 2017.1.3 SP3, CloudMonitoring 1.0.0, IPAM 4.5.1, SAM 6.4.0, DPAIM 11.0.0, NCM 7.6, VIM 7.1.0, QoE 2.3, NetPath 1.1.0, NPM 12.1 © 1999-2018 SolarWinds Worldwide, LLC. All Rights Reserved.

When I try and create the custom HTML all I get is a blank box - see attached.

Custom HTML.JPG

If I create a custom query and run the first SWQL script I get output - also attached, so the query obviously works, I just must be doing something wrong.

Custom SWQL.JPG

Is it perhaps down to the version of Solarwinds that we are running, we are unable to upgrade to the latest version yet due to hardware / software limitations.

dunky2k​ Which version of the script are you running? Also, which browser are you using, and does that browser have any plugins/extensions installed? When the page loads, do you see any errors in the browser console log? (Chrome = F12/inspect page, then click on console to look at errors)

If you are using the version with 4 pie charts (this one uses custom properties, which may be differently named than yours), then try using the 2 pie chart version. (this one does NOT use custom properties, so it should "just work" in more systems than not)

Thank you,

-Will

Version history
Revision #:
1 of 1
Last update:
‎06-29-2018 11:39 AM
Updated by: