SWQL: Cannot resolve property (but it is defined actually)

omavel

Hey. Could you help me find the problem in my query?
I'm trying to gather information about unknown SAM applications, and my query is working as expected, but I want to display "Never up" when the downtime column is NULL.

SELECT
ACP.Application.Name AS ApplicationName,
ACP.Application.Node.Caption AS Node,
(ToString(DayDiff(0,GETUTCDATE()-CAS.LastTimeUp))+' Days '+ToString(Ceiling((HourDiff(0,GETUTCDATE()-CAS.LastTimeUp)/24.0-Floor(HourDiff(0,GETUTCDATE()-CAS.LastTimeUp)/24.0))*24))+' h '+ToString(Ceiling((MinuteDiff(0,GETUTCDATE()-CAS.LastTimeUp)/60.0-Floor(MinuteDiff(0,GETUTCDATE()-CAS.LastTimeUp)/60.0))*60))+' m ') AS downtime,'https://swm.veeam.local/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + ToString(ACP.Application.NodeID) AS [_LinkFor_Node],

'/Orion/images/StatusIcons/Small-' + ACP.Application.Node.StatusLED AS [_IconFor_Node],
'https://swm.veeam.local/Orion/NetPerfMon/NodeDetails.aspx?NetObject=AA:' + ToString(ACP.Application.ApplicationID) AS [_LinkFor_ApplicationName],
'/Orion/images/StatusIcons/Small-' + ACP.Application.StatusDescription + '.gif' AS [_IconFor_ApplicationName]

FROM Orion.APM.ApplicationCustomProperties ACP
INNER JOIN Orion.APM.CurrentApplicationStatus AS CAS ON ACP.applicationid = CAS.applicationid
WHERE ACP.Application.Status = 0 AND ACP.applicationid = CAS.applicationid
ORDER BY DOWNTIME ASC, ACP.Application.Node.Nodename

 

Here is an example of the current view:

Raw data:

What I want to see:

I tried to use a CASE statement (and also changed the ORDER BY clause) like this:

SELECT 
     ACP.Application.Name  AS  ApplicationName,
     ACP.Application.Node.Caption  AS  Node,
(ToString(DayDiff(0,GETUTCDATE()-CAS.LastTimeUp))+' Days '+ToString(Ceiling((HourDiff(0,GETUTCDATE()-CAS.LastTimeUp)/24.0-Floor(HourDiff(0,GETUTCDATE()-CAS.LastTimeUp)/24.0))*24))+' h '+ToString(Ceiling((MinuteDiff(0,GETUTCDATE()-CAS.LastTimeUp)/60.0-Floor(MinuteDiff(0,GETUTCDATE()-CAS.LastTimeUp)/60.0))*60))+' m ') AS  downtime,

    CASE WHEN  downtime  IS NULL THEN 'No data' ELSE  downtime  END AS  downtime_formatted,

    'https://XXX.local/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + ToString(ACP.Application.NodeID) AS [_LinkFor_Node],
    '/Orion/images/StatusIcons/Small-' +   ACP.Application.Node.StatusLED  AS [_IconFor_Node],
    'https://XXX.local/Orion/NetPerfMon/NodeDetails.aspx?NetObject=AA:' + ToString(ACP.Application.ApplicationID) AS [_LinkFor_ApplicationName],
    '/Orion/images/StatusIcons/Small-' +   ACP.Application.StatusDescription   + '.gif' AS [_IconFor_ApplicationName]
FROM  Orion.APM.ApplicationCustomProperties   ACP   
INNER JOIN  Orion.APM.CurrentApplicationStatus   CAS  ON  ACP.applicationid  =  CAS.applicationid 
WHERE  ACP.Application.Status  =  0  AND  ACP.applicationid  =  CAS.applicationid 
ORDER BY  downtime_formatted  ASC,  ACP.Application.Node.Nodename 

However, I'm getting this error:

Can you explain why this is happening and how I can fix it?SELECT
ACP.Application.Name AS ApplicationName,
ACP.Application.Node.Caption AS Node,
(ToString(DayDiff(0,GETUTCDATE()-CAS.LastTimeUp))+' Days '+ToString(Ceiling((HourDiff(0,GETUTCDATE()-CAS.LastTimeUp)/24.0-Floor(HourDiff(0,GETUTCDATE()-CAS.LastTimeUp)/24.0))*24))+' h '+ToString(Ceiling((MinuteDiff(0,GETUTCDATE()-CAS.LastTimeUp)/60.0-Floor(MinuteDiff(0,GETUTCDATE()-CAS.LastTimeUp)/60.0))*60))+' m ') AS downtime,'https://swm.veeam.local/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + ToString(ACP.Application.NodeID) AS [_LinkFor_Node],

'/Orion/images/StatusIcons/Small-' + ACP.Application.Node.StatusLED AS [_IconFor_Node],
'https://XXX.local/Orion/NetPerfMon/NodeDetails.aspx?NetObject=AA:' + ToString(ACP.Application.ApplicationID) AS [_LinkFor_ApplicationName],
'/Orion/images/StatusIcons/Small-' + ACP.Application.StatusDescription + '.gif' AS [_IconFor_ApplicationName]

FROM Orion.APM.ApplicationCustomProperties ACP
INNER JOIN Orion.APM.CurrentApplicationStatus AS CAS ON ACP.applicationid = CAS.applicationid
WHERE ACP.Application.Status = 0 AND ACP.applicationid = CAS.applicationid
ORDER BY DOWNTIME ASC, ACP.Application.Node.Nodename

KMSigma.SWI

I think this is one of those rare instances where you need to use a subquery.

Here's my best guess (because I can't mimic your situation in my lab):

SELECT [ApplicationName]
     , [Node]
     , CASE 
          WHEN downtime IS NULL
               THEN 'Never Up'
          ELSE downtime
          END AS [Downtime]
     , [_LinkFor_Node]
     , [_IconFor_Node]
     , [_LinkFor_ApplicationName]
     , [_IconFor_ApplicationName]
FROM (
     SELECT ACP.Application.Name AS ApplicationName
          , ACP.Application.Node.Caption AS Node
          , (ToString(DayDiff(0, GETUTCDATE() - CAS.LastTimeUp)) + ' Days ' + ToString(Ceiling((HourDiff(0, GETUTCDATE() - CAS.LastTimeUp) / 24.0 - Floor(HourDiff(0, GETUTCDATE() - CAS.LastTimeUp) / 24.0)) * 24)) + ' h ' + ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - CAS.LastTimeUp) / 60.0 - Floor(MinuteDiff(0, GETUTCDATE() - CAS.LastTimeUp) / 60.0)) * 60)) + ' m ') AS downtime
          , ACP.Application.Node.DetailsUrl AS [_LinkFor_Node]
          , CONCAT (
               '/Orion/images/StatusIcons/Small-'
               , ACP.Application.Node.StatusLED
               ) AS [_IconFor_Node]
          , ACP.Application.DetailsUrl AS [_LinkFor_ApplicationName]
          , CONCAT (
               '/Orion/images/StatusIcons/Small-'
               , ACP.Application.StatusDescription
               , '.gif'
               ) AS [_IconFor_ApplicationName]
     FROM Orion.APM.ApplicationCustomProperties ACP
     INNER JOIN Orion.APM.CurrentApplicationStatus AS CAS
          ON ACP.applicationid = CAS.applicationid
     WHERE ACP.Application.STATUS = 0
          AND ACP.applicationid = CAS.applicationid
     ORDER BY DOWNTIME ASC
          , ACP.Application.Node.Nodename
     )

P.S. - You can do away with a bunch of those "ToString()" functions if you swap the logic over to a CONCAT() function, because that automatically converts all parts to strings.  I put in a few examples, but I wasn't tackling your "downtime" math.  I'm not that energetic.