Ad precision: It depends if you talk to SWISv2 or SWISv3. SWISv2 really cuts milliseconds, however SWISv3 does not. Although SWQL Studio displays the time in seconds only, you can still get precise time if you use other clients, e.g. PowerShell SWIS snapin. Anyway, you need to query against SWISv3 to get the full precision.
Ad 2 hours shift: You are right. SWIS entities return all times in UTC (except for rare cases which might be found), so you must be GMT-2h. SWIS takes care of the conversion, clients are provided with already converted times. Since UTC is a default by convention, it is not indicated explicitly.
Changing to SWISv3 gave more precision indeed. Thanks for that.
Extending the query a little gave me some new headache though:
Following SQL query gives me dns name for a node, when the node went down and when the node came back up again (haven't verified it's 100% correct):
select dbo.Nodes.dns as dns, nodeup.EventTime as startedEvent,
(select top 1 nodedown.EventTime from "
SolarWindsOrion"."dbo"."Events" nodedown where nodedown.EventType=1
and nodeup.NetworkNode= nodedown.NetworkNode and nodeup.EventTime>nodedown.EventTime
order by nodedown.EventTime desc) as stoppedEvent
from "SolarWindsOrion"."dbo"."Events" nodeup
join dbo.Nodes on (nodeup.NetworkNode = dbo.Nodes.NodeID)
order by nodeup.EventTime asc;
This gives me:
node1.com 2012-10-03 23:11:20:513 2012-10-03 23:09:23:427
node2.com 2012-10-05 08:10:00:147 2012-10-05 07:49:56:533
node3.com 2012-10-05 08:11:04:063 2012-10-05 07:51:01:060
node4.com 2012-10-05 08:11:10:153 2012-10-05 07:51:08:170
The corresponding SWIS query:
SELECT Nodes.dns AS dns, nodeup.EventTime AS startedEvent,
(SELECT TOP 1 nodedown.EventTime
FROM Orion.Events nodedown
AND nodeup.NetworkNode= nodedown.NetworkNode
ORDER BY nodedown.EventTime DESC)
FROM Orion.Events nodeup
JOIN Orion.Nodes ON (nodeup.NetworkNode = Nodes.NodeID)
ORDER BY nodeup.EventTime ASC;
gives me ..
node1.com 2012-10-03T21:11:20.5130000Z 2012-10-03T23:09:23.4270000
node2.com 2012-10-05T06:10:00.1470000Z 2012-10-05T07:49:56.5330000
node3.com 2012-10-05T06:11:04.0630000Z 2012-10-05T07:51:01.0600000
node4.com 2012-10-05T06:11:10.1530000Z 2012-10-05T07:51:08.1700000
If you look at the result returned from SWIS:
The first column is UTC formatted indicated by the Z? The second is not? (Maybe this is one of the rare cases you were talking about). The second column of times matches the result of the SQL query (no timezone information eventhough the precision of the milliseconds seems strange?).
Maybe my query is pushing the limits for SWIS?
1 of 1 people found this helpful
your script is valid and since you're referring to the same property, it really should be both provided in UTC. This has been confirmed as a bug, tracked under internal bug ticket #186175.
I have also tried to rewrite your query in particular, so that it passes through and provides you what you need (I hope I didn't miss anything from your original query):
SELECT x.dns, ToUtc(x._startedEvent) as startedEvent, ToUtc(x._stoppedEvent) as stoppedEvent
SELECT Nodes.dns AS dns, nodeup.EventTime AS _startedEvent, MAX(nodedown.EventTime) AS _stoppedEvent
FROM Orion.Events nodeup
LEFT JOIN Orion.Events nodedown
JOIN Orion.Nodes ON nodeup.NetworkNode = Nodes.NodeID
WHERE nodeup.EventType=5 AND nodedown.EventType=1
GROUP BY nodeup.EventID, Nodes.dns, nodeup.EventTime
ORDER BY x._startedEvent ASC
It doesn't appear that this is fixed in the most recent SWQL Studio. The results from the studio are misleading using V3. This really ought to have a bug ticket.
We have updated SWQL Studio to improve the precision of the displayed datetime values. Now it uses a fixed format "yyyy-MM-dd HH:mm:ss[.FFFFFF]". Please try the most recent build available at link: Orion SDK release v2.0.41-beta.