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]

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

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

Children