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

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

    atruepath@gmail.com

      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:

      CASE

          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

       

      ,CASE 

          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' 

          END 

        END AS CHANGED

       

      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
          xtraspecialj

          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:

           

          SQL:

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

           

          SWQL:

          DAYDIFF(table.Timestamp, getdate())