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.
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
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
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.
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.
-- Starter Query SELECT [CPU].Node.Caption , [CPU].AvgLoad AS [CPU] , [CPU].AvgPercentMemoryUsed , [CPU].ObservationTimestamp FROM Orion.CPULoad AS [CPU]
-- 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() )
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.-- 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() )
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 patriot, I believe KMSigma.SWI 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
Hi patriot, I believe KMSigma.SWI 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
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.