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

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.

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.