91. Customizing the Orion Platform with the Orion API and SWQL

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

Additional Resources

Lab91Queries.pdf
  • 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.

  • PERFECT! I added "ROUND(SystemUpTime / 60 / 60 / 24, 0) AS [TotalDaysUp] and got exactly what I wanted!

    Thanks for helping a Network guy!

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

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

  • 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