This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

atruepath over 8 years ago

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

Top Replies

xtraspecialj over 8 years ago +1

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…

0 xtraspecialj over 8 years ago

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())
Cancel
Vote Up +1 Vote Down

Cancel