How to format the "ToLocal" SWQL command

I need to convert the timestamps in a SWQL report to show in local time. I know that the ToLocal command will do that, but I can't tell where the parentheses go. Can anyone provide a before and after example? Thanks.

Top Replies

Parents
  • Using the TOLOCAL, you just put that first, then put the date/time field in the parentheses directly afterwards. You'll probably need to assign an alias as well, but it can usually be the same name as the field, or completely different if you'd prefer.

    If you were going to put the "TriggeredDateTime" value from the AlertActive table, it should look something like the following.

    Before/without the function:

    ,AlertActive.TriggeredDateTime

    After/with the function

    ,ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime

    I hope that helps get you what you need.

    Thank you,

    -Will

  • Thanks for the reply wluther, but when I re-examined the following query I'm not sure now why I need the ToLocal after all. My understanding is that GETDATE should be returning the local time anyway (I verified that the Orion server is set to the local time zone).

    Anyway, in the following query I actually have two problems. 1. I need to get the local time as previously mentioned, and 2. I need the report to be able to get three time frames in separate reports - Yesterday, Last Week, and Last Calendar Month. In the query you see I can't seem to get anything other than the current day, week or month. Sorry to throw so much out there, but I am so close to getting this right. Just need to get over the hump.

    SELECT  Nodes.Caption, Nodes.IPAddress, StartTime.EventTime AS DownTime ,
    (
        SELECT TOP 1 EndTime.EventTime
        FROM Orion.Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime
        AND EndTime.EventType = 5
        AND EndTime.NetObjectType = 'N'
        AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime
    ) AS UpTime,
    Nodes.CustomProperties.Customer_Node,
    MINUTEDIFF(StartTime.EventTime,(
            SELECT TOP 1 EventTime FROM Orion.Events AS Endtime
            WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime
    )) AS MinutesDown
    FROM Orion.Events AS StartTime
    INNER JOIN Orion.Nodes ON StartTime.NetworkNode = Nodes.NodeID
    WHERE (StartTime.EventType = 1)
    AND (StartTime.EventTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(1, GETDATE())))
    AND Nodes.CustomProperties.Customer_Node = 'True'
    ORDER BY StartTime.EventTime desc

  • Thanks for the ideas folks, but I am still struggling to explain why  the timestamps are displaying in UTC time. I've proven that by manually taking a node down and back up. The events show up on UTC time rather than CST even though the GETDATE is used in the query. Need to fix this. Also, need this report to be YESTERDAY, not TODAY. Thanks.

  • To display it in local time you need to change the select for the date to:

    ToLocal(StartTime.EventTime) AS DownTime

  • Thank you. Is that the only place in my original query that needs the ToLocal command? Do I also need it in the  AND statement 4 lines from the bottom?

    Also, can you help me with the syntax for Yesterday, Last Week and Last Month?

  • -- Starter Query
    SELECT [CPU].Node.Caption
         , [CPU].AvgLoad AS [CPU]
         , [CPU].AvgPercentMemoryUsed
         , [CPU].ObservationTimestamp
    FROM Orion.CPULoad AS [CPU]

    • Yesterday:
      • -- includes midnight "today"
        WHERE <DateFieldInQuestion> BETWEEN DATETRUNC( 'day', AddDay( -1, GETDATE() ) ) AND DATETRUNC('day', GETDATE() )
      • -- excludes midnight "today"
        WHERE <DateFieldInQuestion> >= DATETRUNC( 'day', AddDay( -1, GETDATE() ) )
        AND <DateFieldInQuestion> < DATETRUNC( 'day', GETDATE() )
    • Last Week:
      • This is actually pretty tricky
      • You could use the WEEK() function to get the previous week's number, but that falls on it's face when the current week number is 1 (there is no '0' week) - and you'd to come up with a clever filter for the year as well.
      • I know it's on THWACK somewhere, but I've never found a version that I 100% love.
      • Last 7 days are easy - just change the "Yesterday" from -1 to -7.
    • Last Month:
      • -- includes midnight on day 1 of current month
        WHERE <DateFieldInQuestion> BETWEEN DATETRUNC( 'month', AddMonth( -1, GETDATE() ) ) AND DATETRUNC( 'month', GETDATE() )
      • --excludes midnight on day 1 of current month
        WHERE <DateFieldInQuestion> >= DATETRUNC( 'month', AddMonth( -1, GETDATE() ) )
        AND <DateFieldInQuestion> < DATETRUNC( 'month', GETDATE() )
    • Current record in my above query in the lab: 19,071
    • Yesterday (include): 1,584
    • Yesterday (exclude): 1,584
    • Last month (include): 469
    • Last month (exclude): 461

    Example of Previous Month

    FYI: Last Year and Last Quarter should work following the same general syntax.

    Additional Note: I'm using ObservationalTimestamp whenever possible as my "date" field because DateTime and Timestamp (which were common) are misleading - at least according to my brain.  I prefer ObservationalTimestamp because it "makes sense" to my head.

  • Well, I must be doing something very wrong, because:

    1. If I insert the "ToLocal" in the first line of the query, I get no errors, but the times in the report are still in UTC

    2. If I use "-1" in the WHERE statement I get no events in the report, which is incorrect because I manually created several down/up events yesterday. Now they seemingly never happened.

    What am I missing here?

    Here is the query as it now stands:

    SELECT  Nodes.Caption, Nodes.IPAddress, ToLocal(StartTime.EventTime) AS DownTime ,
    (
        SELECT TOP 1 EndTime.EventTime
        FROM Orion.Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime
        AND EndTime.EventType = 5
        AND EndTime.NetObjectType = 'N'
        AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime
    ) AS UpTime,
    Nodes.CustomProperties.CustomerName,
    MINUTEDIFF(StartTime.EventTime,(
            SELECT TOP 1 EventTime FROM Orion.Events AS Endtime
            WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime
    )) AS MinutesDown
    FROM Orion.Events AS StartTime
    INNER JOIN Orion.Nodes ON StartTime.NetworkNode = Nodes.NodeID
    WHERE (StartTime.EventType = 1)
    AND (StartTime.EventTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(-1, GETDATE())))
    ORDER BY StartTime.EventTime desc

  • Additional notes:

    1. Using ADDDAY set to -1 yields no events, but using ADDWEEK set to 1 (current week) shows events for the last 2 days. What gives?

    AND (StartTime.EventTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(-1, GETDATE())))

    vs

    AND (StartTime.EventTime BETWEEN DATETRUNC('week', GETDATE()) AND DATETRUNC('week', ADDWEEK(1, GETDATE())))

  • Your order is wrong.  These have to go the lower to the greater.

    AND (StartTime.EventTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(-1, GETDATE())))
  • Not following. I don;t have both of the above Where statements in the same query if that is what you think I mean. I'm saying that the "-1" does not return events from the previous day, week, month, etc. that definitely occurred. So, what am I doing wrong? Can you give an example of the exact syntax that would return "previous" data? Appreciate it.

  • Hi , I believe  is noting that the order of your BETWEEN statement is wrong.

    E.g. it should follow this format: AND <datetime-field> BETWEEN <olderdate> AND <newerdate>.
    Currently you have it like this: AND <datetime-field> BETWEEN <newerdate> AND <olderdate>

    Does that make sense and help clarify? So it should be adjusted to this:

    SELECT  
          Nodes.Caption
         ,Nodes.IPAddress
         ,ToLocal(StartTime.EventTime) AS DownTime
         ,( SELECT TOP 1 EndTime.EventTime
            FROM Orion.Events AS Endtime
            WHERE EndTime.EventTime > StartTime.EventTime
            AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
            ORDER BY EndTime.EventTime
          ) AS UpTime
          ,Nodes.CustomProperties.CustomerName
          ,MINUTEDIFF(StartTime.EventTime
          ,( SELECT TOP 1 EventTime FROM Orion.Events AS Endtime
             WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
             AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime
          )) AS MinutesDown
    FROM Orion.Events AS StartTime
    INNER JOIN Orion.Nodes ON StartTime.NetworkNode = Nodes.NodeID
    WHERE ( StartTime.EventType = 1 )
    AND ( StartTime.EventTime BETWEEN DATETRUNC('day', ADDDAY(-1, GETDATE())) AND DATETRUNC('day', GETDATE()) )
    ORDER BY StartTime.EventTime DESC
  • Appreciate the explanation sum_giais. How ever this query results in the Node Down and Node Up timestamps being exactly the same.

Reply Children
No Data