Difference between latest _time and a datetime field

[schou87]

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

[schou87]

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

[ITWhisperer]

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)

[ITWhisperer]

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.

[schou87]

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"

[ITWhisperer]

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

[schou87]

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.

[ITWhisperer]

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?

[schou87]

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

[ITWhisperer · Accepted solution]

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]

Thanks it worked. 

[ITWhisperer]

The stats command is removing all the fields apart from last_occurence