This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Difference Between SWQL ADDDATE and SQL DATEADD Results

The following SQL query returns 230:

Select COUNT(*) AS 'NodeCount'
FROM [SolarWindsOrion].[dbo].[NodesStatistics]
WHERE [LastSync] < DATEADD(MINUTE, -1, GETDATE())

The following SWQL query against the same database within seconds of the first returns 42 which is incorrect:

SELECT COUNT(*) AS NodeCount FROM Orion.Nodes WHERE LastSync < ADDDATE('MINUTE', -1, GETDATE())

The following SQL query, again within seconds on the same database, returns 42:

Select COUNT(*) AS 'NodeCount'
FROM [SolarWindsOrion].[dbo].[NodesStatistics]
WHERE [LastSync] < DATEADD(DAY, -1, GETDATE())

While the following SWQL query, again within seconds on the same database, also returns 42:

SELECT COUNT(*) AS NodeCount FROM Orion.Nodes WHERE LastSync < ADDDATE('HOUR', -1, GETDATE())

A physical count of the Nodes with Last Sync older than 1 hour is 42 so both of the last two queries are correct.  Why is the second query not working correctly?

Mike

  • Mike Lomax​ I'm not sure why, but the "LastSync" values, returned via SWQL query, from the Orion.Nodes table, are NOT converted to local time. Meanwhile, the SQL query, returning results from the dbo.NodesStatistics table, show the "LastSync" times already converted to local time. If you are going to use these tables, you would probably need to convert the results from one of the tables, to match the other table.

    Also, you may get a better response if you opened this post up to the community, instead of just tdanner​ and myself. While Mr. Danner surely knows what he is talking about, there are definitely folks out there more qualified than me, although, I will always answer what I can... or at least what I think I can.

    I hope this helps, or at least leads you to a solution to you problem.

    Thank you,

    -Will

  • Thanks Will...  I have now opened to the NPM Community and do see your point about the differing timestamps.  Thanks for pointing that out.

  • Mike Lomax

    It seems to work once you change the "GETDATE" to "GETUTCDATE", as far as I can tell.

    SELECT COUNT(*) AS NodeCount FROM Orion.Nodes WHERE LastSync < ADDDATE('MINUTE', -1, GETUTCDATE())

    When I use this SWQL query, it matches the results returned for the SQL query.

    Please let us know when you figure it out.

    Thank you,

    -Will

  • Here is where it gets even stranger... 

    Running the query in SWQL Studio minute, hour, day, month or year all return 42.

    However when I run these same queries through SWIS from within a PowerShell script and still using GETDATE(), all return the correct data except minute.  The Minute option always stays at 42 while running the SQL query, it is ever changing and in the hundreds.

    Changing to GETUTCDATE(), which you are correct I needed to do anyway, did fix the minute option as well, both from the PS script and from SWQL Studio.

    Thanks for the help Will.

    Mike

  • For better or worse (worse), Orion has a mix of UTC and "local" timestamps in the database depending on which table/column you are working with. SWIS smooths over this difference by dealing with clients in UTC as much as possible and converting to local time (as determined by SQL Server) when tables/columns in local time are involved. The "LastSync" column for nodes is in local time in the database.

    If you just do "SELECT LastSync FROM Orion.Nodes" in SWQL, you will get back UTC timestamps, following the principle that SWIS wants to present an all-UTC interface.

  • This is awesome - the differences in SWQL vs. SQL burn too much of my time