Splunk Search

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

khojas02
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
1 Solution

to4kawa
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

woodcock
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

khojas02
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

to4kawa
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"
0 Karma

khojas02
Engager

This worked for me, thanks!!

How can we show the the days in years?

0 Karma

to4kawa
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

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

0 Karma

to4kawa
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

woodcock
Esteemed Legend

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...