5 Replies Latest reply on Oct 20, 2015 5:24 AM by tom.rybka

    Datetime precision is lost in SWQL Studio query

    alexndr

      HI!

       

      I have tried to build a simple query to fetch all EventTime from Event - table using the SWQL Studio (SDK 1.5).

       

      The query is as simple as this:

      SELECT EventTime FROM Orion.Events;

       

      The result is returned as yyyy-MM-DD HH:mm:ss.

       

      I have also tried to query the database directly using:

      SELECT EventTime FROM dbo.Events

       

      which gives me: yyyy-MM-DD HH:mm:ss.SSS

       

      As shown above the milliseconds are not returned using the SWQL Studio. Also, for some reason, it seems that 2 hours are added to the time in SWQL Studio which would indicate that it uses some kind of timeezone formatting which is not visbile in the returned result

       

      Ideas?

       

      Thanks

       

      /Alex

        • Re: Datetime precision is lost in SWQL Studio query
          tom.rybka

          Hi Alex,

           

          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.

            • Re: Datetime precision is lost in SWQL Studio query
              alexndr

              Hi!

               

              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)

              where nodeup.EventType=5

              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

                 WHERE nodedown.EventType=1

                 AND nodeup.NetworkNode= nodedown.NetworkNode

                 AND nodeup.EventTime>nodedown.EventTime

                 ORDER BY nodedown.EventTime DESC)

                AS stoppedEvent

                FROM Orion.Events nodeup

                JOIN Orion.Nodes ON (nodeup.NetworkNode = Nodes.NodeID)

                WHERE nodeup.EventType=5

                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?

               

              /Alex

                • Re: Datetime precision is lost in SWQL Studio query
                  tom.rybka

                  Alex,

                   

                  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

                  FROM (

                  SELECT Nodes.dns AS dns, nodeup.EventTime AS _startedEvent, MAX(nodedown.EventTime) AS _stoppedEvent

                  FROM Orion.Events nodeup

                  LEFT JOIN Orion.Events nodedown

                     ON nodeup.NetworkNode=nodedown.NetworkNode

                    AND nodeup.EventTime>nodedown.EventTime

                  JOIN Orion.Nodes ON nodeup.NetworkNode = Nodes.NodeID

                  WHERE nodeup.EventType=5 AND nodedown.EventType=1

                  GROUP BY nodeup.EventID, Nodes.dns, nodeup.EventTime

                  ) x

                  ORDER BY x._startedEvent ASC

                  1 of 1 people found this helpful
              • Re: Datetime precision is lost in SWQL Studio query
                eanderson

                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.