1 Reply Latest reply on Apr 13, 2016 7:09 AM by xtraspecialj

    CASE function supported in SWQL? - Calculating Time since an event


      Hello Fellow Thwackers,


      From prior posts, I have seen the CASE function being used in swql queries, although I haven't seen any documentation stating that it was supported.

      One example I can give is this beast right here: Alert Prioritising Dashboard (SWQL) for Problematic Nodes (Servers)


      Let's take a small portion from there for discussion:


          WHEN a.Status = 2 Then 1000  -- No Connection, Node is Down

          Else 0  -- Node is Up

      END AS wConn,  -- Set Node Status Score


      What I would like to do is use DAYDIFF / GetTime with calculations to output a column which can display Days/Min/Hours since an event.

      I haven't seen this done so far and I am unable to successfully do a calculation within a CASE/WHEN/ELSE/END.

      I was looking to replicate the same example below (which is in SQL) in SWQL. This is taken from Alex Soul's Know Your Routing Neighbours



          WHEN DAYDIFF(rn.LastChange, getdate()) > 3 

          THEN DAYDIFF(rn.LastChange, getUTCdate()) + ' days ago' 

          ELSE CASE 

            WHEN DAYDIFF(rn.LastChange, getdate() > 3 

            THEN HOURDIFF(rn.LastChange, getdate()) + ' hours ago' 

            ELSE MINUTEDIFF(rn.LastChange, getdate()) + ' min ago' 




      Thank you to the amazing people who created the original scripts I'm pulling from and any suggestions or alternate ways of calculating this would be greatly appreciated - J

        • Re: CASE function supported in SWQL? - Calculating Time since an event

          Here is some documentation showing CASE is supported in SWQL.  It's from the OrionSDK Wiki which I highly recommend you take a look at.  TDanner and crew have done a great job with it so far.  I feel like it was listed in the old SWQL Documentation too, but I can't find the documentation now. 


          As far as the snippet that you want to replicate from Alex, that looks like SWQL to me since it is using DAYDIFF/HOURDIFF/MINUTEDIFF Functions, which are SWQL functions, not SQL Functions.  If it were SQL he would have used DATEDIFF and then declared the datepart as the first argument in the parenthesis.  For example:



          DATEDIFF(DAY, table.Timestamp, getdate())



          DAYDIFF(table.Timestamp, getdate())