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

niketn
Legend

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...