String to DateTime Conversion in SWQL - CustomPoller

anirban

Hello

We have a custom Poller which is polling the last failover of the device to HA. We can see the value with the following SWQL.

Now the format of CurrentValue is HH:MM:SS UTC Oct 7 2024. What we want to do is convert into DateTime format and validate if the timestamp is less than 1hr it means the device has recently failed over and needed to send an alert. This is a string format and needs to be converted. Any direction will be appreciated

Vinx

Hi, 

An example of how you can manipulate timestamp

SELECT getdate() as TimeStamp, max(DateTime) as LastResponseTime, R.Node.Caption, R.Node.Engine.ServerName as PollingEngine, MINUTEDIFF(max(DateTime),getdate()) as MinDiff
FROM Orion.ResponseTime R
GROUP BY R.Node.NodeID, R.Node.Caption, R.Node.Engine.ServerName

timestamp is the current time, lastresponse could be your "currentvalue".

And the last column is a calculation in minute between the lastresponse and the current time

You can just create a swql alert, and be triggered when mindiff > 60