(Episode #91) Customizing the Orion Platform with the Orion API and SWQL

KMSigma over 2 years ago Less than one minute read time

In this follow up to "Orion SDK 101: Intro to PowerShell and Orion API," Kevin M. Sparenberg, technical content manager for Community, will continue with his deep dive into the SolarWinds Query Language (SWQL). Kevin will show you how to represent existing data from within your monitoring ecosystem using traditional elements (e.g., reports, widgets, etc.) and in the new, modern dashboards, as well. A working knowledge of basic T-SQL queries is highly recommended.

In this session, you’ll learn:

How to get started with dashboards
What makes a good or bad dashboard
How to build your own widgets
How to execute proper dashboard research, design, and implementation
Where to find more help

SolarWinds Lab Episode #91 – Customizing the Orion Platform With the SolarWinds API and SWQL

Additional Resources

Lab91Queries.pdf

Top Comments

dgsmith80 over 2 years ago +3

I'm only about 10 minutes in, but I need that desktop background! haha. The timing of this video is amazing as I'm just in the process of onboarding some new devices for our Network Automation team who…
KMSigma over 2 years ago +2

kgrahamjr You say "report" a few times, but do you mean Custom Query Widget? If so, then what you're asking for shouldn't be too crazy difficult. Did you find the field names you want…
omavel over 2 years ago +1

Anyone asked about SAML auth in API/SDK? I missed this live

KMSigma over 2 years ago

From one network guy to another, you are most welcome.

Be sure to share a picture and the code behind on cool things you make in the Orion SDK forum so people can ~~steal it~~ be inspired.
- Cancel
- Up +1 Down
- Reply
- More
- Cancel
kgrahamjr over 2 years ago

PERFECT! I added "ROUND(SystemUpTime / 60 / 60 / 24, 0) AS [TotalDaysUp] and got exactly what I wanted!
Thanks for helping a Network guy!
- Cancel
- Up +1 Down
- Reply
- More
- Cancel
KMSigma over 2 years ago
kgrahamjr - you are 100% correct.
```
SELECT Caption, IPAddress, SystemUpTime / 60 / 60 / 24 AS [TotalDaysUp]
FROM Orion.Nodes
WHERE SystemUpTime / 60 / 60 / 24 >= 90
```
Something like this would probably work (I'm just going from memory above - ignore typos and syntax errors). This is why naming the calculated fields logically is a best practice. There are also functions to do other arithmetic operations (like rounding numbers) so you don't have 2.1023873272901 as a result.
- Cancel
- Up +1 Down
- Reply
- More
- Cancel
kgrahamjr over 2 years ago

Just re-watched... good to do and catch things you missed before!
Excellent, about the modern dashboards. I had to pause and rewind a few times, but great stuff there.
Is there a way to return days, rather than seconds in the Uptime? I see you calculate SystemUpTime/60/60/24 and then compare to 90 days, but can the result be what is returned. Maybe something like DaysUpTime=SystemUpTime/60/60/24?
- Cancel
- Up +1 Down
- Reply
- More
- Cancel

KMSigma over 2 years ago

This is the custom HTML to build the time zones breakdown. It's long, so I wrapped it in a spoiler tag.

<script language="JavaScript">
<!--
function worldClock(zone, region){
var dst = 0
var time = new Date()
var gmtMS = time.getTime() + (time.getTimezoneOffset() * 60000)
var gmtTime = new Date(gmtMS)
var day = gmtTime.getDate()
var month = gmtTime.getMonth()
var year = gmtTime.getYear()
if(year < 1000){
year += 1900
}
var monthArray = new Array("January", "February", "March", "April", "May", "June", "July", "August", 
				"September", "October", "November", "December")
var monthDays = new Array("31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31")
if (year%4 == 0){
monthDays = new Array("31", "29", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31")
}
if(year%100 == 0 && year%400 != 0){
monthDays = new Array("31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31")
}

var hr = gmtTime.getHours() + zone
var min = gmtTime.getMinutes()
var sec = gmtTime.getSeconds()

if (hr >= 24){
hr = hr-24
day -= -1
}
if (hr < 0){
hr -= -24
day -= 1
}
if (hr < 10){
hr = " " + hr
}
if (min < 10){
min = "0" + min
}
if (sec < 10){
sec = "0" + sec
}
if (day <= 0){
if (month == 0){
	month = 11
	year -= 1
	}
	else{
	month = month -1
	}
day = monthDays[month]
}
if(day > monthDays[month]){
	day = 1
	if(month == 11){
	month = 0
	year -= -1
	}
	else{
	month -= -1
	}
}
if (region == "NAmerica"){
	var startDST = new Date()
	var endDST = new Date()
	startDST.setMonth(3)
	startDST.setHours(2)
	startDST.setDate(1)
	var dayDST = startDST.getDay()
	if (dayDST != 0){
		startDST.setDate(8-dayDST)
		}
		else{
		startDST.setDate(1)
		}
	endDST.setMonth(9)
	endDST.setHours(1)
	endDST.setDate(31)
	dayDST = endDST.getDay()
	endDST.setDate(31-dayDST)
	var currentTime = new Date()
	currentTime.setMonth(month)
	currentTime.setYear(year)
	currentTime.setDate(day)
	currentTime.setHours(hr)
	if(currentTime >= startDST && currentTime < endDST){
		dst = 1
		}
}
if (region == "Europe"){
	var startDST = new Date()
	var endDST = new Date()
	startDST.setMonth(2)
	startDST.setHours(1)
	startDST.setDate(31)
	var dayDST = startDST.getDay()
	startDST.setDate(31-dayDST)
	endDST.setMonth(9)
	endDST.setHours(0)
	endDST.setDate(31)
	dayDST = endDST.getDay()
	endDST.setDate(31-dayDST)
	var currentTime = new Date()
	currentTime.setMonth(month)
	currentTime.setYear(year)
	currentTime.setDate(day)
	currentTime.setHours(hr)
	if(currentTime >= startDST && currentTime < endDST){
		dst = 1
		}
}

if (region == "SAmerica"){
	var startDST = new Date()
	var endDST = new Date()
	startDST.setMonth(9)
	startDST.setHours(0)
	startDST.setDate(1)
	var dayDST = startDST.getDay()
	if (dayDST != 0){
		startDST.setDate(22-dayDST)
		}
		else{
		startDST.setDate(15)
		}
	endDST.setMonth(1)
	endDST.setHours(11)
	endDST.setDate(1)
	dayDST = endDST.getDay()
	if (dayDST != 0){
		endDST.setDate(21-dayDST)
		}
		else{
		endDST.setDate(14)
		}
	var currentTime = new Date()
	currentTime.setMonth(month)
	currentTime.setYear(year)
	currentTime.setDate(day)
	currentTime.setHours(hr)
	if(currentTime >= startDST || currentTime < endDST){
		dst = 1
		}
}
if (region == "Cairo"){
	var startDST = new Date()
	var endDST = new Date()
	startDST.setMonth(3)
	startDST.setHours(0)
	startDST.setDate(30)
	var dayDST = startDST.getDay()
	if (dayDST < 5){
		startDST.setDate(28-dayDST)
		}
		else {
		startDST.setDate(35-dayDST)
		}
	endDST.setMonth(8)
	endDST.setHours(11)
	endDST.setDate(30)
	dayDST = endDST.getDay()
	if (dayDST < 4){
		endDST.setDate(27-dayDST)
		}
		else{
		endDST.setDate(34-dayDST)
		}
	var currentTime = new Date()
	currentTime.setMonth(month)
	currentTime.setYear(year)
	currentTime.setDate(day)
	currentTime.setHours(hr)
	if(currentTime >= startDST && currentTime < endDST){
		dst = 1
		}
}
if (region == "Israel"){
	var startDST = new Date()
	var endDST = new Date()
	startDST.setMonth(3)
	startDST.setHours(2)
	startDST.setDate(1)
	endDST.setMonth(8)
	endDST.setHours(2)
	endDST.setDate(25)
	dayDST = endDST.getDay()
	if (dayDST != 0){
	endDST.setDate(32-dayDST)
	}
	else{
	endDST.setDate(1)
	endDST.setMonth(9)
	}
	var currentTime = new Date()
	currentTime.setMonth(month)
	currentTime.setYear(year)
	currentTime.setDate(day)
	currentTime.setHours(hr)
	if(currentTime >= startDST && currentTime < endDST){
		dst = 1
		}
}
if (region == "Beirut"){
	var startDST = new Date()
	var endDST = new Date()
	startDST.setMonth(2)
	startDST.setHours(0)
	startDST.setDate(31)
	var dayDST = startDST.getDay()
	startDST.setDate(31-dayDST)
	endDST.setMonth(9)
	endDST.setHours(11)
	endDST.setDate(31)
	dayDST = endDST.getDay()
	endDST.setDate(30-dayDST)
	var currentTime = new Date()
	currentTime.setMonth(month)
	currentTime.setYear(year)
	currentTime.setDate(day)
	currentTime.setHours(hr)
	if(currentTime >= startDST && currentTime < endDST){
		dst = 1
		}
}
if (region == "Baghdad"){
	var startDST = new Date()
	var endDST = new Date()
	startDST.setMonth(3)
	startDST.setHours(3)
	startDST.setDate(1)
	endDST.setMonth(9)
	endDST.setHours(3)
	endDST.setDate(1)
	dayDST = endDST.getDay()
		var currentTime = new Date()
	currentTime.setMonth(month)
	currentTime.setYear(year)
	currentTime.setDate(day)
	currentTime.setHours(hr)
	if(currentTime >= startDST && currentTime < endDST){
		dst = 1
		}
}
if (region == "Australia"){
	var startDST = new Date()
	var endDST = new Date()
	startDST.setMonth(9)
	startDST.setHours(2)
	startDST.setDate(31)
	var dayDST = startDST.getDay()
	startDST.setDate(31-dayDST)
	endDST.setMonth(2)
	endDST.setHours(2)
	endDST.setDate(31)
	dayDST = endDST.getDay()
	endDST.setDate(31-dayDST)
	var currentTime = new Date()
	currentTime.setMonth(month)
	currentTime.setYear(year)
	currentTime.setDate(day)
	currentTime.setHours(hr)
	if(currentTime >= startDST || currentTime < endDST){
		dst = 1
		}
}

	
if (dst == 1){
	hr -= -1
	if (hr >= 24){
	hr = hr-24
	day -= -1
	}
	if (hr < 10){
	hr = " " + hr
	}
	if(day > monthDays[month]){
	day = 1
	if(month == 11){
	month = 0
	year -= -1
	}
	else{
	month -= -1
	}
	}
return monthArray[month] + " " + day + ", " + year + "<br>" + hr + ":" + min + ":" + sec + " DST"
}
else{
return monthArray[month] + " " + day + ", " + year + "<br>" + hr + ":" + min + ":" + sec
}
}

function worldClockZone(){
document.getElementById("GMT").innerHTML = worldClock(0, "Greenwich")
document.getElementById("Vancouver").innerHTML = worldClock(-8, "NAmerica")
document.getElementById("SanFrancisco").innerHTML = worldClock(-8, "NAmerica")
document.getElementById("Seattle").innerHTML = worldClock(-8, "NAmerica")
document.getElementById("LosAngeles").innerHTML = worldClock(-8, "NAmerica")
document.getElementById("Denver").innerHTML = worldClock(-7, "NAmerica")
document.getElementById("MexicoCity").innerHTML = worldClock(-6, "NAmerica")
document.getElementById("Houston").innerHTML = worldClock(-6, "NAmerica")
document.getElementById("Minneapolis").innerHTML = worldClock(-6, "NAmerica")
document.getElementById("NewOrleans").innerHTML = worldClock(-6, "NAmerica")
document.getElementById("Chicago").innerHTML = worldClock(-6, "NAmerica")
document.getElementById("Montgomery").innerHTML = worldClock(-6, "NAmerica")
document.getElementById("Indianapolis").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("Atlanta").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("Detroit").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("Miami").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("WashingtonDC").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("Philadelphia").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("NewYork").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("Montreal").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("Boston").innerHTML = worldClock(-5, "NAmerica")
document.getElementById("BuenosAires").innerHTML = worldClock(-3, "BuenosAires")
document.getElementById("SaoPaulo").innerHTML = worldClock(-3, "SAmerica")
document.getElementById("RioDeJaneiro").innerHTML = worldClock(-3, "SAmerica")
document.getElementById("Lisbon").innerHTML = worldClock(0, "Europe")
document.getElementById("Dublin").innerHTML = worldClock(0, "Europe")
document.getElementById("London").innerHTML = worldClock(0, "Europe")
document.getElementById("Madrid").innerHTML = worldClock(1, "Europe")
document.getElementById("Barcelona").innerHTML = worldClock(1, "Europe")
document.getElementById("Paris").innerHTML = worldClock(1, "Europe")
document.getElementById("Brussels").innerHTML = worldClock(1, "Europe")
document.getElementById("Amsterdam").innerHTML = worldClock(1, "Europe")
document.getElementById("Frankfurt").innerHTML = worldClock(1, "Europe")
document.getElementById("Rome").innerHTML = worldClock(1, "Europe")
document.getElementById("Berlin").innerHTML = worldClock(1, "Europe")
document.getElementById("Prague").innerHTML = worldClock(1, "Europe")
document.getElementById("Vienna").innerHTML = worldClock(1, "Europe")
document.getElementById("Stockholm").innerHTML = worldClock(1, "Europe")
document.getElementById("Athens").innerHTML = worldClock(2, "Europe")
document.getElementById("Helsinki").innerHTML = worldClock(2, "Europe")
document.getElementById("Minsk").innerHTML = worldClock(2, "Europe")
document.getElementById("Istanbul").innerHTML = worldClock(2, "Europe")
document.getElementById("Cairo").innerHTML = worldClock(2, "Cairo")
document.getElementById("Jerusalem").innerHTML = worldClock(2, "Israel")
document.getElementById("Beirut").innerHTML = worldClock(2, "Beirut")
document.getElementById("Moscow").innerHTML = worldClock(3, "Europe")
document.getElementById("Baghdad").innerHTML = worldClock(3, "Baghdad")
document.getElementById("Dubai").innerHTML = worldClock(4, "Dubai")
document.getElementById("Bangkok").innerHTML = worldClock(7, "Bangkok")
document.getElementById("Jakarta").innerHTML = worldClock(7, "Jakarta")
document.getElementById("HongKong").innerHTML = worldClock(8, "HongKong")
document.getElementById("Beijing").innerHTML = worldClock(8, "Beijing")
document.getElementById("Shanghai").innerHTML = worldClock(8, "Shanghai")
document.getElementById("Seoul").innerHTML = worldClock(9, "Seoul")
document.getElementById("Tokyo").innerHTML = worldClock(9, "Tokyo")
document.getElementById("Melbourne").innerHTML = worldClock(10, "Australia")
document.getElementById("Sydney").innerHTML = worldClock(10, "Australia")
document.getElementById("Brisbane").innerHTML = worldClock(10, "Brisbane")
document.getElementById("Vladivostok").innerHTML = worldClock(10, "Europe")
document.getElementById("Kamchatka").innerHTML = worldClock(12, "Europe")

setTimeout("worldClockZone()", 1000)
}
window.onload=worldClockZone;

//-->
</script>
<style type="text/css">
.hrow {
font-weight:bold;
color: #F99D1C;
}
.hrow td{
padding-top: 20px;
width: 140px;
}
</style>
<table cellspacing="5px">
  <tr class="hrow">
    <td>GMT</td>
    <td>Vancouver</td>
    <td>San Francisco</td>
    <td>Seattle</td>
  </tr>
  <tr>
    <td><span id="GMT"></span></td>
    <td><span id="Vancouver"></span></td>
    <td><span id="SanFrancisco"></span></td>
    <td><span id="Seattle"></span></td>
  </tr>
  <tr class="hrow">
    <td>Los Angeles</td>
    <td>Denver</td>
    <td>Mexico City</td>
    <td>Houston</td>
  </tr>
  <tr>
    <td><span id="LosAngeles"></span></td>
    <td><span id="Denver"></span></td>
    <td><span id="MexicoCity"></span></td>
    <td><span id="Houston"></span></td>
  </tr>
  <tr class="hrow">
    <td>Minneapolis</td>
    <td>New Orleans</td>
    <td>Chicago</td>
    <td>Montgomery</td>
  </tr>
  <tr>
    <td><span id="Minneapolis"></span></td>
    <td><span id="NewOrleans"></span></td>
    <td><span id="Chicago"></span></td>
    <td><span id="Montgomery"></span></td>
  </tr>
  <tr class="hrow">
    <td>Indianapolis</td>
    <td>Atlanta</td>
    <td>Detroit</td>
    <td>Miami</td>
  </tr>
  <tr>
    <td><span id="Indianapolis"></span></td>
    <td><span id="Atlanta"></span></td>
    <td><span id="Detroit"></span></td>
    <td><span id="Miami"></span></td>
  </tr>
  <tr class="hrow">
    <td>Washington DC</td>
    <td>Philadelphia</td>
    <td>New York</td>
    <td>Montreal</td>
  </tr>
  <tr>
    <td><span id="WashingtonDC"></span></td>
    <td><span id="Philadelphia"></span></td>
    <td><span id="NewYork"></span></td>
    <td><span id="Montreal"></span></td>
  </tr>
  <tr class="hrow">
    <td>Boston</td>
    <td>Buenos Aires</td>
    <td>Sao Paulo</td>
    <td>Rio De Janeiro</td>
  </tr>
  <tr>
    <td><span id="Boston"></span></td>
    <td><span id="BuenosAires"></span></td>
    <td><span id="SaoPaulo"></span></td>
    <td><span id="RioDeJaneiro"></span></td>
  </tr>
  <tr class="hrow">
    <td>Lisbon</td>
    <td>Dublin</td>
    <td>London</td>
    <td>Madrid</td>
  </tr>
  <tr>
    <td><span id="Lisbon"></span></td>
    <td><span id="Dublin"></span></td>
    <td><span id="London"></span></td>
    <td><span id="Madrid"></span></td>
  </tr>
  <tr class="hrow">
    <td>Barcelona</td>
    <td>Paris</td>
    <td>Brussels</td>
    <td>Amsterdam</td>
  </tr>
  <tr>
    <td><span id="Barcelona"></span></td>
    <td><span id="Paris"></span></td>
    <td><span id="Brussels"></span></td>
    <td><span id="Amsterdam"></span></td>
  </tr>
  <tr class="hrow">
    <td>Frankfurt</td>
    <td>Rome</td>
    <td>Berlin</td>
    <td>Prague</td>
  </tr>
  <tr>
    <td><span id="Frankfurt"></span></td>
    <td><span id="Rome"></span></td>
    <td><span id="Berlin"></span></td>
    <td><span id="Prague"></span></td>
  </tr>
  <tr class="hrow">
    <td>Vienna</td>
    <td>Stockholm</td>
    <td>Athens</td>
    <td>Helsinki</td>
  </tr>
  <tr>
    <td><span id="Vienna"></span></td>
    <td><span id="Stockholm"></span></td>
    <td><span id="Athens"></span></td>
    <td><span id="Helsinki"></span></td>
  </tr>
  <tr class="hrow">
    <td>Minsk</td>
    <td>Istanbul</td>
    <td>Cairo</td>
    <td>Jerusalem</td>
  </tr>
  <tr>
    <td><span id="Minsk"></span></td>
    <td><span id="Istanbul"></span></td>
    <td><span id="Cairo"></span></td>
    <td><span id="Jerusalem"></span></td>
  </tr>
  <tr class="hrow">
    <td>Beirut</td>
    <td>Moscow</td>
    <td>Baghdad</td>
    <td>Dubai</td>
  </tr>
  <tr>
    <td><span id="Beirut"></span></td>
    <td><span id="Moscow"></span></td>
    <td><span id="Baghdad"></span></td>
    <td><span id="Dubai"></span></td>
  </tr>
  <tr class="hrow">
    <td>Bangkok</td>
    <td>Jakarta</td>
    <td>Hong Kong</td>
    <td>Beijing</td>
  </tr>
  <tr>
    <td><span id="Bangkok"></span></td>
    <td><span id="Jakarta"></span></td>
    <td><span id="HongKong"></span></td>
    <td><span id="Beijing"></span></td>
  </tr>
  <tr class="hrow">
    <td>Shanghai</td>
    <td>Seoul</td>
    <td>Tokyo</td>
    <td>Melbourne</td>
  </tr>
  <tr>
    <td><span id="Shanghai"></span></td>
    <td><span id="Seoul"></span></td>
    <td><span id="Tokyo"></span></td>
    <td><span id="Melbourne"></span></td>
  </tr>
  <tr class="hrow">
    <td>Sydney</td>
    <td>Brisbane</td>
    <td>Vladivostok</td>
    <td>Kamchatka</td>
  </tr>
  <tr>
    <td><span id="Sydney"></span></td>
    <td><span id="Brisbane"></span></td>
    <td><span id="Vladivostok"></span></td>
    <td><span id="Kamchatka"></span></td>
  </tr>
</table>
<p>Code Source: https://proglogic.com/code/javascript/time/worldclock.php</p>

Shamelessly stolen from here: https://proglogic.com/code/javascript/time/worldclock.php

Thwack - Symbolize TM, R, and C