Splunk Search

How to calculate number of days between two dates for a list of dates?

MrIncredible
Explorer

Hello Community,

I am trying to calculate number of days (difference) between today's date and a list of dates but getting desired result only for 1 record out of list of records (different dates). Can anyone help here please?Following query I am using:

index="cds_prod_app" sourcetype=httpevent source="lambda:dip-prod-certs-validity-Splunk"
| eval today=strftime(now(), "%d-%m-%Y")
| eval todaydate=strptime(today, "%d-%m-%Y")
| eval t1 = mvindex(expiry,0)
| eval expirydate=strptime(t1, "%d-%m-%Y")
| eval diff = round((expirydate - todaydate)/86400)
| table expiry, today, diff

***Attaching screenshot

 

~TIA

Labels (3)
0 Karma
1 Solution

scelikok
SplunkTrust
SplunkTrust

Hi @MrIncredible,

You should expand multi-value expiry field to process separately. Please try below simplified query.

index="cds_prod_app" sourcetype=httpevent source="lambda:dip-prod-certs-validity-Splunk"
| table expiry
| mvexpand expiry 
| eval expiry=strptime(expiry, "%d-%m-%Y")
| eval diff = round((expiry - now())/86400)
| eval todaydate=strftime(now(), "%d-%m-%Y")
| table expiry, todaydate, diff
If this reply helps you an upvote and "Accept as Solution" is appreciated.

View solution in original post

scelikok
SplunkTrust
SplunkTrust

Hi @MrIncredible,

You should expand multi-value expiry field to process separately. Please try below simplified query.

index="cds_prod_app" sourcetype=httpevent source="lambda:dip-prod-certs-validity-Splunk"
| table expiry
| mvexpand expiry 
| eval expiry=strptime(expiry, "%d-%m-%Y")
| eval diff = round((expiry - now())/86400)
| eval todaydate=strftime(now(), "%d-%m-%Y")
| table expiry, todaydate, diff
If this reply helps you an upvote and "Accept as Solution" is appreciated.

MrIncredible
Explorer

Thanks a lot @scelikok for letting me know about "mvexpand", its really helpful.
I am sorry earlier I forgot to mention one more field "certname" in this table. if I am adding this field now in this table, its showing 1 set of results for 1 date. Please help how to add certname field in this table?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

You need to explain how the field field certname is matched with field t1 in raw data.  I assume that they come in the exact same order.  You also want to explain how you want the result presented, preferably with illustration.  Assuming that the base date is always "today", and you want to present data for each certname.

Now, if you run Splunk 8 and above, you can use JSON functions in mvexpand.

index="cds_prod_app" sourcetype=httpevent source="lambda:dip-prod-certs-validity-Splunk"
| table expiry certname
| eval idx = mvrange(0, mvcount(expiry))
| eval certexpiry = mvmap(idx, json_object("certname", mvindex(certname, idx), "expiry", mvindex(expiry, idx)))
| mvexpand certexpiry 
| eval expiry=strptime(json_extract(certexpiry, "expiry") "%d-%m-%Y")
| eval certname = json_extract(certexpiry, "certname")
| eval diff = round((expiry - now())/86400)
| eval todaydate=strftime(now(), "%d-%m-%Y")
| table certname expiry, todaydate, diff

If you use 7 or earlier, the traditional zip and split still works.

index="cds_prod_app" sourcetype=httpevent source="lambda:dip-prod-certs-validity-Splunk"
| table expiry certname
| eval certexpiry = mvzip(certname, expiry, ":zip:")
| mvexpand certexpiry
| eval certexpiry = split(certexpiry, ":zip")
| eval expiry=strptime(mvindex(certexpiry, 0) "%d-%m-%Y")
| eval certname = mvindex(certexpiry, 1)
| eval diff = round((expiry - now())/86400)
| eval todaydate=strftime(now(), "%d-%m-%Y")
| table certname expiry, todaydate, diff

 

MrIncredible
Explorer

thank @yuanliu its working. got to know new "json" functions. thanks for that.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...