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

[MrIncredible]

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

[scelikok · Accepted solution]

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

[MrIncredible]

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?

[yuanliu]

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]

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