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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

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

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...