Splunk Search

How to find days between today's date and field value date?

rkassabov
Path Finder

I am trying to subtract a field value date (Step Due Date) from today's date (nowstring) to determine if the number of days between them is less than half the SLA value.

This is the relevant part of my query but it doesn't seem to be working:

 | eval nowstring=strftime(now(), "%Y-%m-%d")
 | eval "Coming Due"=case(("Step Due Date"-nowstring) < (SLA / 2), 1, "Step Date Completed" != "", 0)

Here is an example value from "Step Due Date": 2018-06-18 12:11:07.890000

0 Karma
1 Solution

MuS
SplunkTrust
SplunkTrust

Hi rkassabov,

your dates are in various formats, and because of the " the case() statement uses literally "Step Due Date" as string.
Try this one instead, where Step Due Date becomes an epoch timestamp which then can be subtracted with now():

| makeresults 
| eval SLA="2", "Step Due Date"="2018-06-18 12:11:07.890000", step_date_epoch=strptime('Step Due Date', "%F %H:%M:%S.%6N"), nowstring=now() 
| eval "Coming Due"=case(('Step Due Date' - nowstring) < (SLA / 2), 1, "Step Date Completed" != "", 0)

Adapt the SPL to your needs.

Hope this helps ...

cheers, MuS

View solution in original post

MuS
SplunkTrust
SplunkTrust

Hi rkassabov,

your dates are in various formats, and because of the " the case() statement uses literally "Step Due Date" as string.
Try this one instead, where Step Due Date becomes an epoch timestamp which then can be subtracted with now():

| makeresults 
| eval SLA="2", "Step Due Date"="2018-06-18 12:11:07.890000", step_date_epoch=strptime('Step Due Date', "%F %H:%M:%S.%6N"), nowstring=now() 
| eval "Coming Due"=case(('Step Due Date' - nowstring) < (SLA / 2), 1, "Step Date Completed" != "", 0)

Adapt the SPL to your needs.

Hope this helps ...

cheers, MuS

rkassabov
Path Finder

Perfect, thank you!

0 Karma
Get Updates on the Splunk Community!

User Groups | Upcoming Events!

If by chance you weren't already aware, the Splunk Community is host to numerous User Groups, organized ...

Splunk Lantern | Spotlight on Security: Adoption Motions, War Stories, and More

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...