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.