SQL query that works in SQL Management Studio but not in Report builder

I have this query that was built in Management Studio but does not work when I am using it to create a report.

WITH AUOptions AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'AUOptions'),

       ScheduledInstallTime AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'ScheduledInstallTime'),

       ScheduledInstallDay AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'ScheduledInstallDay'),

       TargetGroup AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'TargetGroup'),

       WUServer AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'WUServer'),

       KBCheck AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'KB Check'),

       PendingPatches AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'Pending Patches'),

       PendingReboot AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'Pending Reboot'),

       RebootRequired AS (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'RebootRequired')

SELECT DISTINCT APM_AARD.[NodeName]

         ,AUOptions.[ComponentMessage] AS [AUOptions]

         ,ScheduledInstallTime.[ComponentMessage] AS [ScheduledInstallTime]

         ,ScheduledInstallDay.[ComponentMessage] AS [ScheduledInstallDay]

         ,TargetGroup.[ComponentMessage] AS [TargetGroup]

         ,WUServer.[ComponentMessage] AS [WUServer]

         ,KBCheck.[ComponentMessage] AS [KB Check]

         ,PendingPatches.[ComponentMessage] AS [Pending Patches]

         ,PendingReboot.[ComponentMessage] AS [Pending Reboot]

         ,RebootRequired.[ComponentMessage] AS [RebootRequired]

FROM [dbo].[APM_AlertsAndReportsData] APM_AARD

LEFT JOIN AUOptions ON APM_AARD.[NodeID] = AUOptions.[NodeID]

LEFT JOIN ScheduledInstallTime ON APM_AARD.[NodeID] = ScheduledInstallTime.[NodeID]

LEFT JOIN ScheduledInstallDay ON APM_AARD.[NodeID] = ScheduledInstallDay.[NodeID]

LEFT JOIN TargetGroup ON APM_AARD.[NodeID] = TargetGroup.[NodeID]

LEFT JOIN WUServer ON APM_AARD.[NodeID] = WUServer.[NodeID]

LEFT JOIN KBCheck ON APM_AARD.[NodeID] = KBCheck.[NodeID]

LEFT JOIN PendingPatches ON APM_AARD.[NodeID] = PendingPatches.[NodeID]

LEFT JOIN PendingReboot ON APM_AARD.[NodeID] = PendingReboot.[NodeID]

LEFT JOIN RebootRequired ON APM_AARD.[NodeID] = RebootRequired.[NodeID]

ORDER BY APM_AARD.[NodeName]

  • To the best of my knowledge the SQL-specific reporting engine in Orion doesn't support the "WITH" clause.  This would have to be rewritten.

    What do you get if you go to http://<YourOrionServer>/Orion/admin/swis.aspx and paste in the below?

    SELECT [CCS].Component.Application.Node.Caption
         , [CCS].Component.UserNotes
         , [CCS].Component.ComponentAlert.ComponentMessage
    FROM Orion.APM.CurrentComponentStatus AS [CCS]
    WHERE [CCS].Component.UserNotes IN ( 'AUOptions', 'ScheduledInstallTime', 'ScheduledInstallDay', 'TargetGroup', 'WUServer', 'KB Check', 'Pending Patches', 'Pending Reboot', 'RebootRequired' )
    ORDER BY [CCS].Component.Application.Node.Caption
    

  • Yes the one you put in works.  But I need the values to be columns.  Our DBAs rewrote the quere and this one works!  :-)

    SELECT DISTINCT APM_AARD.[NodeName]

             ,CASE WHEN AUOptions.[ComponentMessage] LIKE 'Connecting%' THEN 'N/A'

                         ELSE AUOptions.[ComponentMessage]

              END AS [AUOptions]

             ,CASE WHEN ScheduledInstallTime.[ComponentMessage] LIKE 'Connecting%' THEN 'N/A'

                         ELSE ScheduledInstallTime.[ComponentMessage]

              END AS [ScheduledInstallTime]

             ,CASE WHEN ScheduledInstallDay.[ComponentMessage] LIKE 'Connecting%' THEN 'N/A'

                         ELSE ScheduledInstallDay.[ComponentMessage]

              END AS [ScheduledInstallDay]

             ,CASE WHEN TargetGroup.[ComponentMessage] LIKE 'Connecting%' THEN 'N/A'

                         ELSE TargetGroup.[ComponentMessage]

              END AS [TargetGroup]

             ,CASE WHEN WUServer.[ComponentMessage] LIKE 'Connecting%' THEN 'N/A'

                         ELSE WUServer.[ComponentMessage]

              END AS [WUServer]

             ,KBCheck.[ComponentMessage] AS [KB Check]

             ,PendingPatches.[ComponentMessage] AS [Pending Patches]

             ,PendingReboot.[ComponentMessage] AS [Pending Reboot]

             ,RebootRequired.[ComponentMessage] AS [RebootRequired]

    FROM [dbo].[APM_AlertsAndReportsData] APM_AARD

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'AUOptions') AUOptions ON APM_AARD.[NodeID] = AUOptions.[NodeID]

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'ScheduledInstallTime') ScheduledInstallTime ON APM_AARD.[NodeID] = ScheduledInstallTime.[NodeID]

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'ScheduledInstallDay') ScheduledInstallDay ON APM_AARD.[NodeID] = ScheduledInstallDay.[NodeID]

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'TargetGroup') TargetGroup ON APM_AARD.[NodeID] = TargetGroup.[NodeID]

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'WUServer') WUServer ON APM_AARD.[NodeID] = WUServer.[NodeID]

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'KB Check') KBCheck ON APM_AARD.[NodeID] = KBCheck.[NodeID]

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'Pending Patches') PendingPatches ON APM_AARD.[NodeID] = PendingPatches.[NodeID]

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'Pending Reboot') PendingReboot ON APM_AARD.[NodeID] = PendingReboot.[NodeID]

    LEFT JOIN (SELECT [NodeID],[ComponentMessage] FROM [dbo].[APM_AlertsAndReportsData] WHERE [UserNotes] = 'RebootRequired') RebootRequired ON APM_AARD.[NodeID] = RebootRequired.[NodeID]

    ORDER BY APM_AARD.[NodeName]

    But now I am creating a modern dashboard and it does not take SQL so I have to figure out how to rewrite the whole thing in SWQL.

    Because the table names are so different I am having a lot of issues.  Why does this table APM_AlertsAndReportsData in SQL contain all the info I need but in SWQL I have to JOIN several tables?  I am looking at the one you sent and see if I can build the dashboard I need.

    Thanks so much ya'll!!!

  • I just wanted to make sure that what I was coming up with was correct in SWQL.  You can still do the various JOINS to pivot the table to layout you want.  Since I don't have this template configured on my system, I'd have to take a bunch of guesses, but at least you have the data there. Let me noodle with it a bit and see if I can get a working SWQL version for you.

  • Like I said before, I don't have your particular template in my environment, so I can't validate the results.  However, this is my best guess at what you need:

    SELECT DISTINCT [CCS].Component.Application.Node.Caption
         , CASE 
              WHEN [AUOptions].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [AUOptions].[ComponentMessage]
              END AS [AUOptions]
         , CASE 
              WHEN [ScheduledInstallTime].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [ScheduledInstallTime].[ComponentMessage]
              END AS [ScheduledInstallTime]
         , CASE 
              WHEN [ScheduledInstallDay].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [ScheduledInstallDay].[ComponentMessage]
              END AS [ScheduledInstallDay]
         , CASE 
              WHEN [TargetGroup].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [TargetGroup].[ComponentMessage]
              END AS [TargetGroup]
         , CASE 
              WHEN [WUServer].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [WUServer].[ComponentMessage]
              END AS [WUServer]
         , [KB Check].[ComponentMessage] AS [KB Check]
         , [Pending Patches].[ComponentMessage] AS [Pending Patches]
         , [Pending Reboot].[ComponentMessage] AS [Pending Reboot]
         , [RebootRequired].[ComponentMessage] AS [RebootRequired]
    FROM Orion.APM.CurrentComponentStatus AS [CCS]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'AUOptions'
         ) AS [AUOptions]
         ON [CCS].Component.Application.Node.NodeID = [AUOptions].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'ScheduledInstallTime'
         ) AS [ScheduledInstallTime]
         ON [CCS].Component.Application.Node.NodeID = [ScheduledInstallTime].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'ScheduledInstallDay'
         ) AS [ScheduledInstallDay]
         ON [CCS].Component.Application.Node.NodeID = [ScheduledInstallDay].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'TargetGroup'
         ) AS [TargetGroup]
         ON [CCS].Component.Application.Node.NodeID = [TargetGroup].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'WUServer'
         ) AS [WUServer]
         ON [CCS].Component.Application.Node.NodeID = [WUServer].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'KB Check'
         ) AS [KB Check]
         ON [CCS].Component.Application.Node.NodeID = [KB Check].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'Pending Patches'
         ) AS [Pending Patches]
         ON [CCS].Component.Application.Node.NodeID = [Pending Patches].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'Pending Reboot'
         ) AS [Pending Reboot]
         ON [CCS].Component.Application.Node.NodeID = [Pending Reboot].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM Orion.APM.CurrentComponentStatus AS [CCS]
         WHERE [CCS].Component.UserNotes = 'RebootRequired'
         ) AS [RebootRequired]
         ON [CCS].Component.Application.Node.NodeID = [RebootRequired].[NodeID]
    ORDER BY [CCS].Component.Application.Node.Caption
    

    You should be able to test it in SWQL Studio or in the above link on your Orion server to see if it returns data.

  • Sorry I actually had 2 different reports in there but when I cut it down to just one report it works great!!

    SELECT DISTINCT [CCS].Component.Application.Node.Caption
         , CASE
              WHEN [AUOptions].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [AUOptions].[ComponentMessage]
              END AS [AUOptions]
         , CASE
              WHEN [ScheduledInstallTime].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [ScheduledInstallTime].[ComponentMessage]
              END AS [ScheduledInstallTime]
         , CASE
              WHEN [ScheduledInstallDay].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [ScheduledInstallDay].[ComponentMessage]
              END AS [ScheduledInstallDay]
         , CASE
              WHEN [TargetGroup].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [TargetGroup].[ComponentMessage]
              END AS [TargetGroup]
         , CASE
              WHEN [WUServer].[ComponentMessage] LIKE 'Connecting%'
                   THEN 'N/A'
              ELSE [WUServer].[ComponentMessage]
              END AS [WUServer]
       
    FROM  Orion.APM.CurrentComponentStatus  AS [CCS]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM  Orion.APM.CurrentComponentStatus  AS [CCS]
         WHERE [CCS].Component.UserNotes  = 'AUOptions'
         ) AS [AUOptions]
         ON [CCS].Component.Application.Node.NodeID  = [AUOptions].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM  Orion.APM.CurrentComponentStatus  AS [CCS]
         WHERE [CCS].Component.UserNotes  = 'ScheduledInstallTime'
         ) AS [ScheduledInstallTime]
         ON [CCS].Component.Application.Node.NodeID  = [ScheduledInstallTime].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM  Orion.APM.CurrentComponentStatus  AS [CCS]
         WHERE [CCS].Component.UserNotes  = 'ScheduledInstallDay'
         ) AS [ScheduledInstallDay]
         ON [CCS].Component.Application.Node.NodeID  = [ScheduledInstallDay].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM  Orion.APM.CurrentComponentStatus  AS [CCS]
         WHERE [CCS].Component.UserNotes  = 'TargetGroup'
         ) AS [TargetGroup]
         ON [CCS].Component.Application.Node.NodeID  = [TargetGroup].[NodeID]
    LEFT JOIN (
         SELECT [CCS].Component.Application.Node.NodeID
              , [CCS].Component.ComponentAlert.ComponentMessage
         FROM  Orion.APM.CurrentComponentStatus  AS [CCS]
         WHERE [CCS].Component.UserNotes  = 'WUServer'
         ) AS [WUServer]
         ON [CCS].Component.Application.Node.NodeID  = [WUServer].[NodeID]
     
    ORDER BY [CCS].Component.Application.Node.Caption

    WOW!! Thank you so much!!  I will work on the 2nd report next.  :-)  If is just so confusing with SQL and SWQL.

  • Generally speaking SWQL is easier to interpret because you don't (normally) need to worry about building your own JOIN clauses.  That being said, if you can get the data, get the data.