topic Re: How to convert the time and calculate the difference in a query? in Getting Data In

How to convert the time and calculate the difference in a query?

pavanae — Tue, 12 Jul 2022 17:57:56 GMT

Hello Splunkers,

I have a query as follows

My query blah blah blah |stats latest(description) as description latest(result) as result latest(object) as object by host source _time

which gives the result as follows

pavanae_1-1657648352473.png

As highlighted with yellow color on the above results there are two different time values one under _time and the other under description.

Now I want to filter the results for the hosts that has more than 24 hours in the difference between _time and the time in the description. Something like below

difference time = (_time - time_in_the_description) > 24 hours

Re: How to convert the time and calculate the difference in a query?

ITWhisperer — Tue, 12 Jul 2022 18:30:47 GMT

It is a little difficult to see what your data looks like from the picture, but assuming I have worked out the pattern correctly, try something like this

| rex field=description "Last event received from \S+ : (?<description_time>\d+\-\d+\-\d+\s\d+:\d+)" | where _time-strptime(description_time,"%Y-%d-%m %H:%M") > 60*60*24

Re: How to convert the time and calculate the difference in a query?

pavanae — Tue, 12 Jul 2022 18:51:47 GMT

Thanks for the response. the regex provided didn't worked. Let me provide the full syntax of the Description below

Last event received from host_1 (ABCD-1234): 2022-12-06 23:59. logtype=ABC

Re: How to convert the time and calculate the difference in a query?

ITWhisperer — Tue, 12 Jul 2022 18:54:21 GMT

| rex field=description "Last event received from [^:]+: (?<description_time>\d+\-\d+\-\d+\s\d+:\d+)" | where _time-strptime(description_time,"%Y-%d-%m %H:%M") > 60*60*24