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 (3)
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!

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...