Adventures in PIVOTing - how to do a poor man's pivot in SWQL

Overview of PIVOT

For anyone who has worked with the SolarWinds Query Language (SWQL) for any amount of time knows that it mimics traditional SQL in many ways.  But there are some limitations that aren't put on traditional SQL.  Specifically, I'm looking at a few functions that don't exist in SWQL (at least of the time of this writing).  To say that SWQL and SQL are related it true; but they are second or third cousins.

One such omission is the ability to pivot returned data.  If you are unfamiliar with the term, it's explained very well on Microsoft's site, but I've always thought of it like the Paste as Transpose that's in Excel.

Basically, it takes data that's like this:

 and converts it to this:

Is this a gross over simplication? You better believe it, but it's how my brain interprets this data.

Poor Man's SWQL PIVOT

Another limitation on SWQL is that it doesn't support subqueries.  But there are ways to link tables together because SWQL does support JOINs.

I've written about why I dislike using JOINs in the past because SWQL has native functionality that renders most of them unecessary.  If it's at all possible, you should leverage Navigation Properties.  They work better than JOINS for 90% of your queries.

To better illustrate how this works in practicality, we need two tables that are related by some type of common element.  Since this procedure works for (at least) SAM Script components that return multiple statistics and Universal Device Pollers, I'm going to propose two different entities (tables) for this discussion.

The Example Entities

Orion.EntityTable

SELECT ElementID
     , Caption
     , IPAddress
FROM Orion.EntityTable

Orion.EntityMultiLookupTable

SELECT ElementID
     , ElementName
     , ElementValue
FROM Orion.EntityMultiLookupTable

Our Desired Result Set

Step-by-Step

1. Build the initial query getting what you need from the first entity
    

   -- Step 1: Select what elements you can traditionally
   SELECT [ET].Caption
        , [ET].IPAddress
   FROM Orion.EntityTable AS [ET]

2. Use a LEFT JOIN to connect that entity to the multiple element entity and add an additional filter for the element you want to retrieve
    

   -- Step 2: Adding the LEFT JOIN
   SELECT [ET].Caption
        , [ET].IPAddress
   FROM [Orion.EntityTable] AS [ET]
   LEFT JOIN [Orion.EntityMultiLookupTable] AS [EMLT]
     ON [ET].ElementID = [EMLT].ElementID
     AND [EMLT].ElementName = 'AccountStat'

3. And add [EMLT].ElementValue AS [AccountStat] to your list of properties to return
    

   -- Step 3: Add your newly connected element
   SELECT [ET].Caption
        , [ET].IPAddress
        , [EMLT].ElementValue AS [AccountStat] <-- new
   FROM [Orion.EntityTable] AS [ET]
   LEFT JOIN [Orion.EntityMultiLookupTable] AS [EMLT]
     ON [ET].ElementID = [EMLT].ElementID
    AND [EMLT].ElementName = 'AccountStat'

4. Since we want multiple elements from that target table, we just repeat with new aliases and element filters
    

   -- Step 4: Bring in additional information via joins and properties
   SELECT [ET].Caption
        , [ET].IPAddress
        , [EMLT].ElementValue AS [AccountStat]
        , [EMLT2].ElementValue AS [NumDrives]
        , [EMLT3].ElementValue AS [HasDvdRom]
   FROM [Orion.EntityTable] AS [ET]
   LEFT JOIN [Orion.EntityMultiLookupTable] AS [EMLT]
     ON [ET].ElementID = [EMLT].ElementID
    AND [EMLT].ElementName = 'AccountStat'
   LEFT JOIN [Orion.EntityMultiLookupTable] AS [EMLT2]
     ON [ET].ElementID = [EMLT2].ElementID
    AND [EMLT2].ElementName = 'NumDrives'
   LEFT JOIN [Orion.EntityMultiLookupTable] AS [EMLT3]
     ON [ET].ElementID = [EMLT3].ElementID
    AND [EMLT3].ElementName = 'HasDvdRom'

5. Keep repeating step 4 until you have all the information in your result set you need.
6. Use better names for the joined entity aliases - they can get numerous and hard to remember what's what
   

   -- Step 6: Think on it and use better names for your joined entities aliases
   SELECT [ET].Caption
        , [ET].IPAddress
        , [EMLT_AccountStat].ElementValue AS [AccountStat]
        , [EMLT_NumDrives].ElementValue AS [NumDrives]
        , [EMLT_HasDvdRom].ElementValue AS [HasDvdRom]
   FROM [Orion.EntityTable] AS [ET]
   LEFT JOIN [Orion.EntityMultiLookupTable] AS [EMLT_AccountStat]
     ON [ET].ElementID = [EMLT_AccountStat].ElementID
    AND [EMLT_AccountStat].ElementName = 'AccountStat'
   LEFT JOIN [Orion.EntityMultiLookupTable] AS [EMLT_NumDrives]
     ON [ET].ElementID = [EMLT_NumDrives].ElementID
    AND [EMLT_NumDrives].ElementName = 'NumDrives'
   LEFT JOIN [Orion.EntityMultiLookupTable] AS [EMLT_HasDvdRom]
     ON [ET].ElementID = [EMLT_HasDvdRom].ElementID
    AND [EMLT_HasDvdRom].ElementName = 'HasDvdRom'

Summary

In summation, the lack of the PIVOT function in the SolarWinds Query Language can be an annoyance then you're trying to represent data in a way that's appealing for you and for the consumers of your Observability solutions.  However, it's rarely needed because SWQL has Navigation Properties to help you do most of the heavy lifting.

Tl;DR: I didn't want to have to keep looking for my notes on how to do this, so I decided to publish this discussion and add it to my THWACK bookmarks for easy reference.

Related Content

• SWQL Pivot Example | THWACK Content Exchange - Custom Queries - The Orion Platform
• More efficient Account Status Application Monitor - Orion SDK - The Orion Platform