topic Difference between 2 dates based on another field in years and days in Splunk Search

Difference between 2 dates based on another field in years and days

khojas02 — Fri, 13 Mar 2020 18:35:43 GMT

I have a sample data as below

Assigned Analyst Assigned Date
John 2018-03-09 00:00:00.0
2018-03-23 00:00:00.0
2018-03-30 00:00:00.0
2018-04-16 00:00:00.0
2018-04-24 00:00:00.0
2018-04-26 00:00:00.0
2018-05-03 00:00:00.0

Joe 2017-03-22 00:00:00.0
2017-03-23 00:00:00.0
2017-05-01 00:00:00.0
2017-05-02 00:00:00.0
2017-05-18 00:00:00.0
2017-05-23 00:00:00.0

Now, I would like to find the time span for each Analyst based on the earliest and latest values of Assigned Date in Years and Days.
Assigned Date is simply the date on which the ticket was assigned. Ticket Number is the unique identifier which I didn't add in the sample data.
Thanks in advance

Re: Difference between 2 dates based on another field in years and days

to4kawa — Fri, 13 Mar 2020 19:56:14 GMT

| makeresults 
| eval _raw="Assigned Analyst,Assigned Date
John,2018-03-09 00:00:00.0
,2018-03-23 00:00:00.0
,2018-03-30 00:00:00.0
,2018-04-16 00:00:00.0
,2018-04-24 00:00:00.0
,2018-04-26 00:00:00.0
,2018-05-03 00:00:00.0

Joe,2017-03-22 00:00:00.0
,2017-03-23 00:00:00.0
,2017-05-01 00:00:00.0
,2017-05-02 00:00:00.0
,2017-05-18 00:00:00.0
,2017-05-23 00:00:00.0"
|multikv forceheader=1
| table A*
| rename COMMENT as "this sample, from here, the logic"
| eval assigned_time=strptime(Assigned_Date,"%F %T.%1Q")
| eval assigned_year=strftime(assigned_time,"%Y")
| filldown Assigned_Analyst
| stats min(assigned_time) as FirstAssigned max(assigned_time) as LastAssigned range(assigned_time) as span by Assigned_Analyst assigned_year
| eval FirstAssigned=strftime(FirstAssigned,"%F %T"), LastAssigned=strftime(LastAssigned,"%F %T")
| eval span=tostring(span,"duration")
| rex field=span mode=sed "s/(\d+)?\+?(\d\d):(\d\d):\d\d\.\d+/\1d \2h \3m/g"

Re: Difference between 2 dates based on another field in years and days

woodcock — Fri, 13 Mar 2020 21:12:08 GMT

Like this:

| makeresults 
| eval _raw="Assigned Analyst,Assigned Date
 John,2018-03-09 00:00:00.0 2018-03-23 00:00:00.0 2018-03-30 00:00:00.0 2018-04-16 00:00:00.0 2018-04-24 00:00:00.0 2018-04-26 00:00:00.0 2018-05-03 00:00:00.0
 Joe,2017-03-22 00:00:00.0 2017-03-23 00:00:00.0 2017-05-01 00:00:00.0 2017-05-02 00:00:00.0 2017-05-18 00:00:00.0 2017-05-23 00:00:00.0" 
| multikv forceheader=1 
| table A*

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex field=Assigned_Date mode=sed "s/(-\d+)\s+/\1T/g"
| makemv Assigned_Date
| eval Assigned_Date = strptime(Assigned_Date, "%Y-%m-%dT%H:%M:%S.%1n")
| stats range(Assigned_Date) AS duration BY Assigned_Analyst
| fieldformat duration = tostring(duration, "duration")

Re: Difference between 2 dates based on another field in years and days

khojas02 — Fri, 13 Mar 2020 23:52:15 GMT

This worked for me, thanks!!

How can we show the the days in years?

Re: Difference between 2 dates based on another field in years and days

woodcock — Sat, 14 Mar 2020 00:12:35 GMT

See my answer. It does this and is more efficient, too.

Re: Difference between 2 dates based on another field in years and days

to4kawa — Sat, 14 Mar 2020 02:17:12 GMT

assigned_year displays already.

the days in years? what's this?

....
|rex field=span "(?<days>\d+)"
| eventstats sum(days) by Assigned_Analyst

this?

Re: Difference between 2 dates based on another field in years and days

khojas02 — Mon, 16 Mar 2020 18:30:00 GMT

Hi,

For some reason, this solution is not producing any output for me.

I just added required index and source on top of it.

Re: Difference between 2 dates based on another field in years and days

khojas02 — Wed, 30 Sep 2020 04:34:44 GMT

Your search has created the output shown below. Now, if I want to add the column that shows the span in number of years.

AssignedAnalyst FirstAssigned LastAssigned span
B_Davis 2018-03-09 00:00:00 2020-02-28 00:00:00 721d 00h 00m
C_Ramos 2017-03-22 00:00:00 2019-06-24 00:00:00 824d 00h 00m
L_Allen 2018-09-19 00:00:00 2019-01-14 00:00:00 17d 01h 00m

Is is possible to do that? Thanks!!

Re: Difference between 2 dates based on another field in years and days

to4kawa — Tue, 17 Mar 2020 09:41:39 GMT

 ....
| rex field=span "(?<days>\d+)"
| eval years=floor(days/365)."years ".(days % 365)."days"

If you want only years, amend this.