5 Replies Latest reply on Dec 29, 2015 2:37 AM by Steven Klassen

    PIVOT function with SWQL?

    mwb

      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

        • Re: PIVOT function with SWQL?
          tdanner

          There's no way to do PIVOT in SWQL.

            • Re: PIVOT function with SWQL?
              mwb

              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

                • Re: PIVOT function with SWQL?
                  humejo

                  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

                    • Re: PIVOT function with SWQL?
                      mwb

                      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

                        • Re: PIVOT function with SWQL?
                          Steven Klassen

                          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