How to find a difference between two times which are off by two different formats and set an alert if the difference is more than 10mins?

[sangs8788]

Hi,

I have a query which returns two columns Time1 which is _time and one more column Time 2 which is user calculated time available in the event as below,

Query used

index=data |eval GetdateTime = date + " " + gettime  | timechart span=5m last(GetdateTime) as Time2 by server

Query returns the last date logged in event by server. I have to identify difference between those two time fields which is _time & Time2. How do i find the difference since the format of the fields are different for hours section. _time uses : as separator where in the field available in the column is using . as separator. How do i replace it and then convert it to epochtime in order to find the difference ?

I need to define an alert in real time to check if there is a difference in field is more than 10 mins
Please let me know.

[mstjohn_splunk]

Hi @sangs8788. Did the answer below solve your question? If yes, please click “Accept” directly below the answer to resolve the post. If not, please comment with more information if you are still having issues. Thanks!

[richgalloway · Accepted solution]

There is no need to convert _time as it is already in epoch form (it's automatically converted to text when displayed). Use the strptime function to convert time2 to an epoch then you can subtract the two times to find their difference.

index=data |eval GetdateTime = date + " " + gettime | eval Timediff=strptime(GetdateTime, "%Y-%m-%d %H.%M.%S") | where Timediff>600 | ...

[rsokolova]

Please explain how can you strip the time out of 2 epoch times together. Also how can you compare an epoch time result against a regular number ?