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

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?