PIVOT function with SWQL?

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

Parents
  • 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 emoticons_wink.png

  • 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

  • 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

  • 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

  • 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

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

Children
No Data