Splunk Search

How to subtract one date from another?

eranday
New Member

I hold a list of two dates that I want to subtract one from another
these dates are an outcome from:
|convert timeformat="%Y-%m-%d" ctime(_time) AS date1

source:
| date1 | date2 |
|2017-12-08 | 2017-12-05 |
|2017-12-04 | 2017-12-05 |
|2017-12-05 | 2017-12-05 |

Wanted outcom:
| date 1 | date 2 | diff |
|2017-12-08 | 2017-12-05 | 3 |
|2017-12-04 | 2017-12-05 | 1 |
|2017-12-05 | 2017-12-05 | 0 |

Tags (2)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi @eranday,

Can you please try this search?

YOUR_SEARCH
| eval dates=mvrange(strptime(date2,"%Y-%m-%d"),strptime(date1,"%Y-%m-%d"),86400)
| eval diff=mvcount(dates)

My Sample Search:

| makeresults 
| eval date1="2017-12-08" , date2="2017-12-05" 
| eval dates=mvrange(strptime(date2,"%Y-%m-%d"),strptime(date1,"%Y-%m-%d"),86400)
| eval diff=mvcount(dates)

Thanks

View solution in original post

sbbadri
Motivator

@eranday

Please try below,

| makeresults | eval d1="2017-12-08" | eval d2="2017-12-05" | eval da1=strptime(d1,"%Y-%m-%d") | eval da2=strptime(d2,"%Y-%m-%d") | eval diff=da1-da2 | eval diff=strftime(diff,"%d")
0 Karma

@eranday, please try the following runanywhere search,

| makeresults
| eval date2=relative_time(_time,"-3d")
| eval date1=strftime(_time,"%Y%m%d")
| eval date2=strftime(date2,"%Y%m%d")
| eval diff=date1-date2

You can replace first two pipes with your query to get date1 and date2 as epoch time. I have used relative_time function to get dummy data which is 3 days prior i.e. -3d.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi @eranday,

Can you please try this search?

YOUR_SEARCH
| eval dates=mvrange(strptime(date2,"%Y-%m-%d"),strptime(date1,"%Y-%m-%d"),86400)
| eval diff=mvcount(dates)

My Sample Search:

| makeresults 
| eval date1="2017-12-08" , date2="2017-12-05" 
| eval dates=mvrange(strptime(date2,"%Y-%m-%d"),strptime(date1,"%Y-%m-%d"),86400)
| eval diff=mvcount(dates)

Thanks

eranday
New Member

not working

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

search updated pls try it.

0 Karma

eranday
New Member

Champ!! it works
Thanks

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Great.. !!

Can you please accept the answer to close this question?

Thanks
Happy Splunking

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...