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!

Exciting News: The AppDynamics Community Joins Splunk!

Hello Splunkers,   I’d like to introduce myself—I’m Ryan, the former AppDynamics Community Manager, and I’m ...

The All New Performance Insights for Splunk

Splunk gives you amazing tools to analyze system data and make business-critical decisions, react to issues, ...

Good Sourcetype Naming

When it comes to getting data in, one of the earliest decisions made is what to use as a sourcetype. Often, ...