This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

PIVOT function with SWQL?

mwb over 8 years ago

Can anyone comment on PIVOT functionality with SWQL? Perhaps an example of where it's been used previously?

I have a web resource for the dashboard view using SWQL perfectly, but I would love to see the row data flipped around into column for easier consumption.

I did exactly what I wanted to do on a web report with a custom SQL query --- and of course I can do a custom SQL query report through report writer and display the report on the view. Works great for me as the admin, but there are known issues with custom SQL reporting and account limitations (it gets broken).

The answer seems simple, to write the view Custom Query resource in SWQL with a pivot table, but I can't for the life of me seem to get it functional.

Anyone run into the same issues previously?

- Matt

Top Replies

Parents

0 tdanner over 8 years ago

There's no way to do PIVOT in SWQL.
Cancel
Vote Up 0 Vote Down

Cancel
0 mwb over 8 years ago in reply to tdanner

Although, I will add for anyone reading this and wanting to accomplish the same, I was able to take my SQL query, pull it apart and rewrite it without PIVOT for SWQL by doing two JOINs for the ComponentStatisticData and selecting each as a different value. Ends up displaying on the dashboard view exactly the same as the grid PIVOT was giving me... which is all I wanted in the first place
Cancel
Vote Up 0 Vote Down

Cancel
0 humejo over 8 years ago in reply to mwb

I'm trying to do the exact same thing right here. I'm SQL/SWQL competent, but I can't picture how to do this in my head right now. Can you post an example of how you accomplished this?
Thanks,
Jordan
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 humejo over 8 years ago in reply to mwb

I'm trying to do the exact same thing right here. I'm SQL/SWQL competent, but I can't picture how to do this in my head right now. Can you post an example of how you accomplished this?
Thanks,
Jordan
Cancel
Vote Up 0 Vote Down

Cancel

Children

0 mwb over 8 years ago in reply to humejo

This is a sample of what I've done in the past - joining multiple queries and then wrapping the whole thing in another select query.
select c.NODE
,'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(c.NodeID) AS [_LinkFor_NODE]
, c.ComponentStatisticData as Total
,   '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_Total]
, d.ComponentStatisticData as Success
,   '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(d.ComponentID) AS [_LinkFor_Success]
, e.ComponentStatisticData as Warning
,   '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(d.ComponentID) AS [_LinkFor_Warning]
, f.ComponentStatisticData as Failed
,   '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(d.ComponentID) AS [_LinkFor_Failed]
, g.ComponentStatisticData as Running
,   '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(d.ComponentID) AS [_LinkFor_Running]
FROM

(
SELECT
n.nodeid,
n.Caption AS NODE,
cs.ComponentStatisticData,
c.componentID

    FROM Orion.APM.Component c
    JOIN ORION.APM.CurrentStatistics 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
c.name = 'Total Job Count (daily)'
) c

FULL JOIN
(
SELECT

n.Caption AS NODE,
cs.ComponentStatisticData,
c.componentID

    FROM Orion.APM.Component c
    JOIN ORION.APM.CurrentStatistics 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
c.name = 'NetVault Jobs Successful (daily)'
) d
ON c.NODE=d.NODE
FULL JOIN
(
SELECT

n.Caption AS NODE,
cs.ComponentStatisticData,
c.componentID

    FROM Orion.APM.Component c
    JOIN ORION.APM.CurrentStatistics 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
c.name = 'NetVault Jobs Warning (daily)'
) e
ON c.NODE=e.NODE
FULL JOIN
(
SELECT

n.Caption AS NODE,
cs.ComponentStatisticData,
c.componentID

    FROM Orion.APM.Component c
    JOIN ORION.APM.CurrentStatistics 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
c.name = 'NetVault Jobs Failed (daily)'
) f
ON c.NODE=f.NODE

FULL JOIN
(
SELECT

n.Caption AS NODE,
cs.ComponentStatisticData,
c.componentID

    FROM Orion.APM.Component c
    JOIN ORION.APM.CurrentStatistics 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
c.name = 'Running Jobs (daily)'
) g
ON c.NODE=g.NODE
Cancel
Vote Up 0 Vote Down

Cancel

0 mrxinu over 8 years ago in reply to mwb

With syntax highlighting:

select c.NODE

,'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(c.NodeID) AS [_LinkFor_NODE]

, c.ComponentStatisticData as Total

, '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_Total]

, d.ComponentStatisticData as Success

, '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(d.ComponentID) AS [_LinkFor_Success]

, e.ComponentStatisticData as Warning

, '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(d.ComponentID) AS [_LinkFor_Warning]

, f.ComponentStatisticData as Failed

, '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(d.ComponentID) AS [_LinkFor_Failed]

, g.ComponentStatisticData as Running

, '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(d.ComponentID) AS [_LinkFor_Running]

FROM

(

SELECT

n.nodeid,

n.Caption AS NODE,

cs.ComponentStatisticData,

c.componentID

FROM Orion.APM.Component c

JOIN ORION.APM.CurrentStatistics 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

c.name = 'Total Job Count (daily)'

) c

FULL JOIN

(

SELECT

n.Caption AS NODE,

cs.ComponentStatisticData,

c.componentID

FROM Orion.APM.Component c

JOIN ORION.APM.CurrentStatistics 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

c.name = 'NetVault Jobs Successful (daily)'

) d ON c.NODE=d.NODE

FULL JOIN

(

SELECT

n.Caption AS NODE,

cs.ComponentStatisticData,

c.componentID

FROM Orion.APM.Component c

JOIN ORION.APM.CurrentStatistics 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

c.name = 'NetVault Jobs Warning (daily)'

) e ON c.NODE=e.NODE

FULL JOIN

(

SELECT

n.Caption AS NODE,

cs.ComponentStatisticData,

c.componentID

FROM Orion.APM.Component c

JOIN ORION.APM.CurrentStatistics 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

c.name = 'NetVault Jobs Failed (daily)'

) f ON c.NODE=f.NODE

FULL JOIN

(

SELECT

n.Caption AS NODE,

cs.ComponentStatisticData,

c.componentID

FROM Orion.APM.Component c

JOIN ORION.APM.CurrentStatistics 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

c.name = 'Running Jobs (daily)'

) g ON c.NODE=g.NODE

0 jfoudy over 3 years ago in reply to mrxinu

Using the Examples above I was able to create a "PIVOT" query for Disk Volumes per Node. You'll need to add addition columns as needed. You can also add/remove the left joins as needed for the drives you want to view.

This ends up looking like this:

Config of Drive Column:

SELECT a.Caption, a.NodeStatus, a.DetailsURL, c.DeviceID as C_DeviceID, C.VolumePercentUsed as C_VolumePercentUsed, d.DeviceID as D_DeviceID, d.VolumePercentUsed as D_VolumePercentUsed, e.DeviceID as E_DeviceID, e.VolumePercentUsed as E_VolumePercentUsed, f.DeviceID as F_DeviceID, f.VolumePercentUsed as F_VolumePercentUsed, g.DeviceID as G_DeviceID, g.VolumePercentUsed as G_VolumePercentUsed, h.DeviceID as H_DeviceID, h.VolumePercentUsed as H_VolumePercentUsed, k.DeviceID as K_DeviceID, k.VolumePercentUsed as K_VolumePercentUsed, w.DeviceID as W_DeviceID, w.VolumePercentUsed as W_VolumePercentUsed
FROM
(SELECT TOP 200 NodeID, InstanceSiteId, n.Caption,n.CPULoad,n.PercentMemoryUsed,n.Status as NodeStatus, n.DetailsUrl
FROM Orion.Nodes n
WHERE (n.CustomProperties.AssetTag= 'SQL' and n.CustomProperties.Environment= 'Production')) a

LEFT JOIN

(SELECT
NodeID, InstanceSiteId, DeviceID, VolumeType, VolumeTypeIcon, Round(VolumeSize/1024/1024/1024,2) as VolumeSizeGB, Round(VolumeSpaceAvailable/1024/1024/1024,2) as VolumeSpaceAvailable, VolumePercentAvailable, Status, StatusDescription, StatusIcon,Round(VolumePercentUsed, 0) as VolumePercentUsed
FROM Orion.Volumes
WHERE VolumeType = 'Fixed Disk' and DeviceID = 'C:') c
ON c.NodeID=a.NodeID AND c.InstanceSiteId=a.InstanceSiteId

LEFT JOIN

(SELECT
NodeID, InstanceSiteId, DeviceID, VolumeType, VolumeTypeIcon, Round(VolumeSize/1024/1024/1024,2) as VolumeSizeGB, Round(VolumeSpaceAvailable/1024/1024/1024,2) as VolumeSpaceAvailable, VolumePercentAvailable, Status, StatusDescription, StatusIcon,Round(VolumePercentUsed, 0) as VolumePercentUsed
FROM Orion.Volumes
WHERE VolumeType = 'Fixed Disk' and DeviceID = 'D:') D
ON d.NodeID=a.NodeID AND d.InstanceSiteId=a.InstanceSiteId

LEFT JOIN

(SELECT
NodeID, InstanceSiteId, DeviceID, VolumeType, VolumeTypeIcon, Round(VolumeSize/1024/1024/1024,2) as VolumeSizeGB, Round(VolumeSpaceAvailable/1024/1024/1024,2) as VolumeSpaceAvailable, VolumePercentAvailable, Status, StatusDescription, StatusIcon,Round(VolumePercentUsed, 0) as VolumePercentUsed
FROM Orion.Volumes
WHERE VolumeType = 'Fixed Disk' and DeviceID = 'E:') e
ON e.NodeID=a.NodeID AND e.InstanceSiteId=a.InstanceSiteId

LEFT JOIN

(SELECT
NodeID, InstanceSiteId, DeviceID, DetailsURL, VolumeType, VolumeTypeIcon, Round(VolumeSize/1024/1024/1024,2) as VolumeSizeGB, Round(VolumeSpaceAvailable/1024/1024/1024,2) as VolumeSpaceAvailable, VolumePercentAvailable, Status, StatusDescription, StatusIcon,Round(VolumePercentUsed, 0) as VolumePercentUsed
FROM Orion.Volumes
WHERE VolumeType = 'Fixed Disk' and DeviceID = 'F:') F
ON f.NodeID=a.NodeID AND f.InstanceSiteId=a.InstanceSiteId

LEFT JOIN

(SELECT
NodeID, InstanceSiteId, DeviceID, VolumeType, VolumeTypeIcon, Round(VolumeSize/1024/1024/1024,2) as VolumeSizeGB, Round(VolumeSpaceAvailable/1024/1024/1024,2) as VolumeSpaceAvailable, VolumePercentAvailable, Status, StatusDescription, StatusIcon,Round(VolumePercentUsed, 0) as VolumePercentUsed
FROM Orion.Volumes
WHERE VolumeType = 'Fixed Disk' and DeviceID = 'G:') G
ON g.NodeID=a.NodeID AND g.InstanceSiteId=a.InstanceSiteId

LEFT JOIN

(SELECT
NodeID, InstanceSiteId, DeviceID, VolumeType, VolumeTypeIcon, Round(VolumeSize/1024/1024/1024,2) as VolumeSizeGB, Round(VolumeSpaceAvailable/1024/1024/1024,2) as VolumeSpaceAvailable, VolumePercentAvailable, Status, StatusDescription, StatusIcon,Round(VolumePercentUsed, 0) as VolumePercentUsed
FROM Orion.Volumes
WHERE VolumeType = 'Fixed Disk' and DeviceID = 'H:') h
ON h.NodeID=a.NodeID AND h.InstanceSiteId=a.InstanceSiteId

LEFT JOIN

(SELECT
NodeID, InstanceSiteId, DeviceID, VolumeType, VolumeTypeIcon, Round(VolumeSize/1024/1024/1024,2) as VolumeSizeGB, Round(VolumeSpaceAvailable/1024/1024/1024,2) as VolumeSpaceAvailable, VolumePercentAvailable, Status, StatusDescription, StatusIcon,Round(VolumePercentUsed, 0) as VolumePercentUsed
FROM Orion.Volumes
WHERE VolumeType = 'Fixed Disk' and DeviceID = 'K:') k
ON k.NodeID=a.NodeID AND k.InstanceSiteId=a.InstanceSiteId

LEFT JOIN

(SELECT
NodeID, InstanceSiteId, DeviceID, VolumeType, VolumeTypeIcon, Round(VolumeSize/1024/1024/1024,2) as VolumeSizeGB, Round(VolumeSpaceAvailable/1024/1024/1024,2) as VolumeSpaceAvailable, VolumePercentAvailable, Status, StatusDescription, StatusIcon,Round(VolumePercentUsed, 0) as VolumePercentUsed
FROM Orion.Volumes
WHERE VolumeType = 'Fixed Disk' and DeviceID = 'W:') w
ON w.NodeID=a.NodeID AND w.InstanceSiteId=a.InstanceSiteId

0 omavel over 2 years ago in reply to jfoudy

After these manipulation my table looks like this:

It different than yours. Some additional configuration needed? Yep, got it

And do you know how to ignore those nodes which have no volumes?
Cancel
Vote Up 0 Vote Down

Cancel

0 jfoudy over 2 years ago in reply to omavel

So in the first part of the query I'm doing this:

WHERE (n.CustomProperties.AssetTag= 'SQL' and n.CustomProperties.Environment= 'Production')) a

It is basically filtering out only nodes that have those custom properties and I know that they're windows boxes with SQL Server on them and they'd all have the drives I listed. Most of them don't have a w:\ for example so they'd just be blank. Since all of these joins below the first part of the query are Left Joins, they're including all nodes and keeping nodes with no volumes.

You could either add custom properties to your nodes to filters the nodes down, or have some other property to query for only nodes with a c:\.

So adding say a join to the volumes to check if it has a c: at the top might also give you the results you need. This query is untested, but it a good start:

Select a.Caption, a.NodeStatus, a.DetailsURL, c.DeviceID as C_DeviceID, C.VolumePercentUsed as C_VolumePercentUsed, d.DeviceID as D_DeviceID, d.VolumePercentUsed as D_VolumePercentUsed, e.DeviceID as E_DeviceID, e.VolumePercentUsed as E_VolumePercentUsed, f.DeviceID as F_DeviceID, f.VolumePercentUsed as F_VolumePercentUsed, g.DeviceID as G_DeviceID, g.VolumePercentUsed as G_VolumePercentUsed, h.DeviceID as H_DeviceID, h.VolumePercentUsed as H_VolumePercentUsed, k.DeviceID as K_DeviceID, k.VolumePercentUsed as K_VolumePercentUsed, w.DeviceID as W_DeviceID, w.VolumePercentUsed as W_VolumePercentUsed
FROM
(SELECT TOP 200 NodeID, InstanceSiteId, n.Caption,n.CPULoad,n.PercentMemoryUsed,n.Status as NodeStatus, n.DetailsUrl
FROM Orion.Nodes n
JOIN Orion.Volumes v
WHERE v.VolumeType = 'Fixed Disk' and v.DeviceID = 'C:') c
ON n.NodeID=v.NodeID AND n.InstanceSiteId=v.InstanceSiteId

Let me know if you're still having issues.