Getting Data In

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

pavanae
Builder

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 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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

View solution in original post

0 Karma

pavanae
Builder

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
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...