nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

How to format the "ToLocal" SWQL command

patriot

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.

Find more posts tagged with

Accepted answers

All comments

wluther

@patriot 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

patriot

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

KMSigma.SWI

GETDATE() does local server time.

ToLocal() is more for when you want to present it in a browser context: modern dashboard or the like and want to convert it to the local browser time.

patriot

Interesting given that the report you see above is currently generating UTC timestamps. Any idea why?

KMSigma.SWI

My understanding (and I'm not one of the architects), is that the ToLocal() will only work when put in a "webby" thing (Modern Dashboard, Report, Custom Query Widget) but in SWQL Studio, it doesn't do any conversions.

I'm only about 70% sure here on the nuances of this function.

patriot

Gotcha. So, any thoughts as to why the query above results in UTC timestamps?

shuth

Some data in the database is stored in UTC time. Some data in the database is stored in the local database time. I can't recall offhand which is which but I recommend confirming via the raw data in the database.

E.g. testing an event at 2:23pm for this reply, the EventTime is stored in UTC but I can use TOLOCAL(EventTime) to convert it to my local time. GetDate and GetUTCDate used to verify.

patriot

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.

sum_giais

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

ToLocal(StartTime.EventTime) AS DownTime

patriot

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?

KMSigma.SWI

-- 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

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.

patriot

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

patriot

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())))

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