Dashboards & Visualizations

Difference between latest _time and a datetime field

schou87
Path Finder

Hi,

I require a field which gives me the difference between the latest event timestamp and a date time field. I am using the below query but I am getting "No results found". Please help!!

 

 

 

base_query | dedup RQ_ID |  stats latest(_time) AS latest_occurence | eval c_time=strptime(latest_occurence,"%m/%d/%y %H:%M:%S") | eval start_time=strptime(mvindex(START_TS,0),"%m/%d/%y %H:%M:%S") | eval diff=round ((c_time-setup_time)/3600,2) | table RQ_ID, diff

 

 

 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

OK so you don't need the stats or the parsing of _time, just use _time instead of c_time in your diff calculation

base_query 
| dedup RQ_ID 
| eval start_time=strptime(mvindex(START_TS,0),"%m/%d/%y %H:%M:%S") 
| eval diff=round ((_time-setup_time)/3600,2) 
| table RQ_ID, diff

View solution in original post

schou87
Path Finder

@ITWhisperer I am getting the same message "No results found".

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

last_occurence is an epoch time since it is the latest _time, therefore does not need to be parsed (strptime) into an epoch time. Try 

eval diff=round ((last_occurence-setup_time)/3600,2)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What is it you are trying to do? dedup with pick up the first event in the pipeline for each RQ_ID, which is probably the latest since they are usually presented in descending order by time but it depends on what your base query is doing. If you want to process START_TS in anyway after the stats command, it needs to be passed through the stats command, same goes for RQ_ID.

0 Karma

schou87
Path Finder

START_TS for each of RQ_ID is the start time for each of the request ids which will be different across different ids. I want to find the difference between each of the start time and latest timestamp when the latest logs is getting generated. So basically I want to know how long has the request ids been sitting on the system without getting processed basis different statuses that i have like pending, delivered, error.

So the output should be

RQ_ID            START_TS                  Diff AS "Time Spent"

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

So is there a first and last timestamp for each RQ_ID?

| stats earliest(_time) as start latest(_time) as end by RQ_ID
| eval diff=end-start
0 Karma

schou87
Path Finder

I just have a Splunk timestamp for event logs (which I am considering in latest occurence). But the start time field is in the index and not a Splunk timestamp.

The stats command is not working for my start time field.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Does the deduped event contain everything that you need, including START_TS, as well as being the latest (first found) event for the RQ_ID?

0 Karma

schou87
Path Finder

Yes it does. It is just giving the latest entry for a RQ_ID, rest everything is returned that I need.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

OK so you don't need the stats or the parsing of _time, just use _time instead of c_time in your diff calculation

base_query 
| dedup RQ_ID 
| eval start_time=strptime(mvindex(START_TS,0),"%m/%d/%y %H:%M:%S") 
| eval diff=round ((_time-setup_time)/3600,2) 
| table RQ_ID, diff

schou87
Path Finder

Thanks it worked. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The stats command is removing all the fields apart from last_occurence

0 Karma
Get Updates on the Splunk Community!

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 ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...