Splunk Search
Highlighted

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

Engager

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

Tags (2)
0 Karma
Highlighted

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

Ultra Champion
| 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"

View solution in original post

0 Karma
Highlighted

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

Engager

This worked for me, thanks!!

How can we show the the days in years?

0 Karma
Highlighted

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

Esteemed Legend

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

0 Karma
Highlighted

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

Ultra Champion

assigned_year displays already.

the days in years? what's this?

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

this?

0 Karma
Highlighted

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

Engager

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

0 Karma
Highlighted

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

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

If you want only years, amend this.

0 Karma
Highlighted

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

Esteemed Legend

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")
0 Karma
Highlighted

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

Engager

Hi,

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

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

0 Karma