topic Re: Difference between latest _time and a datetime field in Dashboards & Visualizations

Difference between latest _time and a datetime field

schou87 — Thu, 03 Jun 2021 08:35:36 GMT

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

Re: Difference between latest _time and a datetime field

ITWhisperer — Thu, 03 Jun 2021 08:49:07 GMT

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)

Re: Difference between latest _time and a datetime field

schou87 — Thu, 03 Jun 2021 08:53:41 GMT

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

Re: Difference between latest _time and a datetime field

ITWhisperer — Thu, 03 Jun 2021 08:55:39 GMT

The stats command is removing all the fields apart from last_occurence

Re: Difference between latest _time and a datetime field

ITWhisperer — Thu, 03 Jun 2021 09:00:10 GMT

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.

Re: Difference between latest _time and a datetime field

schou87 — Thu, 03 Jun 2021 09:06:33 GMT

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"

Re: Difference between latest _time and a datetime field

ITWhisperer — Thu, 03 Jun 2021 09:17:47 GMT

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

Re: Difference between latest _time and a datetime field

schou87 — Thu, 03 Jun 2021 09:46:17 GMT

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.

Re: Difference between latest _time and a datetime field

ITWhisperer — Thu, 03 Jun 2021 09:49:17 GMT

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?

Re: Difference between latest _time and a datetime field

schou87 — Thu, 03 Jun 2021 10:43:56 GMT

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

Re: Difference between latest _time and a datetime field

ITWhisperer — Thu, 03 Jun 2021 11:07:25 GMT

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

Re: Difference between latest _time and a datetime field

schou87 — Thu, 03 Jun 2021 11:21:47 GMT

Thanks it worked.