Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Is it possible to join multiple rows of the same table into one row using SWQL?

I have monitoring data for RDS instances that is being written into the Orion.APM.MultipleStatisticsData table. In my current dashboard I have each of the metrics split up based on the name. Eg CPU, Read Throughput, write throughput, etc. what I want to do is display all of the data for each RDS instance in one row.

I have this:

and i want to display it

Node, CPU utilization, FreeStorageSpace, FreeableMemory, Read throughtput, WriteThroughput, etc. on one row for each of my RDS nodes.

Can anyone help with this? I don't believe SWQL can do pivots and modern dashboards can't do SQL.

Find more posts tagged with

SWQL

Accepted answers

shuth

I've done something similar for a client but I'm not sure of the efficiency/optimisation of this type of query, especially with a large data set and if you want to add all 15 pieces of data. You will need unique labels and you could probably narrow the sub-queries further by using the application name as well as the component name.

SELECT
n.Caption AS [Node Name]
, rds_readthroughput.StatData AS [Read Throughput]
, rds_writethroughput.StatData AS [Write Throughput]
, rds_freespace.StatData AS [Free Space]
FROM Orion.Nodes n

JOIN (
SELECT DISTINCT MultiStatData.Component.Application.NodeID
, MultiStatData.Name
, MultiStatData.NumericData AS [StatData]
FROM Orion.APM.MultipleStatisticData MultiStatData
WHERE MultiStatData.Label = 'ReadThroughput'
) rds_readthroughput ON rds_readthroughput.NodeID = n.NodeID

JOIN (
SELECT DISTINCT MultiStatData.Component.Application.NodeID
, MultiStatData.Name
, MultiStatData.NumericData AS [StatData]
FROM Orion.APM.MultipleStatisticData MultiStatData
WHERE MultiStatData.Label = 'WriteThroughput'
) rds_writethroughput ON rds_writethroughput.NodeID = n.NodeID

JOIN (
SELECT DISTINCT MultiStatData.Component.Application.NodeID
, MultiStatData.Name
, MultiStatData.NumericData AS [StatData]
FROM Orion.APM.MultipleStatisticData MultiStatData
WHERE MultiStatData.Label = 'FreeStorageSpace'
) rds_freespace ON rds_freespace.NodeID = n.NodeID

I don't have those components in my lab so the query returns the following empty table. Keep expanding the query for more pieces of data - but again, I'm not sure about the performance impact of this type of query.

All comments

KMSigma.SWI

Correct. SWQL cannot do PIVOTs, but other posts (here's one), explain how to do a "long form" pivot.

shuth

hallstev5

Thank you. I used the post KMSigma linked to come up with a very similar solution.

KMSigma.SWI

@hallstev5 - can you share back the actual SWQL you used so others can get the help? (Please use the "Insert/Code" and select "SQL" as the language for pretty formatting)

hallstev5

Sure thing. The code used is almost exactly what was in the linked post. Just updated it to use the MultipleStatisticsData table.

select c.NODE
    ,'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(c.NodeID) AS [_LinkFor_NODE]
    , c.NumericData as CPUUtilization
    , d.NumericData as FreeStorageSpace
    , e.NumericData as FreeableMemory
    , f.NumericData as DatabaseConnections
    , g.NumericData as DiskQueueDepth
    , h.NumericData as ReadIOPs
    , i.NumericData as ReadLatency
    , j.NumericData as ReadThroughput
    , k.NumericData as WriteIOPs
    , l.NumericData as WriteLatency
    , m.NumericData as WriteThroughput
    , n.NumericData as NetRvcThroughput
    , o.NumericData as NetTransThroughput

FROM
(
    SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'CPUUtilization'
) c
FULL JOIN
(
       SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'FreeStorageSpace'
) d ON c.NODE=d.NODE
FULL JOIN
(
      SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'FreeableMemory'
) e ON c.NODE=e.NODE
FULL JOIN
(
      SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.NumericData,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'DatabaseConnections'
) f ON c.NODE=f.NODE
FULL JOIN
(
       SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'DiskQueueDepth'
) g ON c.NODE=g.NODE
FULL JOIN
(
       SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'ReadIOPS'
) h ON c.NODE=h.NODE
FULL JOIN
(
      SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'ReadLatency'
) i ON c.NODE=i.NODE
FULL JOIN
(
      SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.NumericData,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'ReadThroughput'
) j ON c.NODE=j.NODE
FULL JOIN
(
       SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'WriteIOPS'
) k ON c.NODE=k.NODE
FULL JOIN
(
       SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'WriteLatency'
) l ON c.NODE=l.NODE
FULL JOIN
(
       SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'WriteThroughput'
) m ON c.NODE=m.NODE
FULL JOIN
(
       SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'NetworkReceiveThroughput'
) n ON c.NODE=n.NODE
FULL JOIN
(
       SELECT
    n.nodeid,
    n.Caption AS NODE,
    cs.numericdata,
    c.componentID
    FROM Orion.APM.Component c
    JOIN ORION.APM.MultipleStatisticData cs ON c.ComponentID = cs.ComponentID
    JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
    JOIN Orion.Nodes n ON a.NodeID = n.NodeID
    WHERE
    cs.name = 'NetworkTransmitThroughput'
) o ON c.NODE=o.NODE