topic How to compare dates for expiration purposes? in Splunk Search

How to compare dates for expiration purposes?

albinortiz — Tue, 29 Sep 2020 18:07:44 GMT

Greetings,
I am trying to create a panel that helps me track expired trainings. What I am trying to do is to take the Class_Date and add 1year to it then compare that to todays date. So, IF Class_Date + 1 year > todays date, "Expired", "Valid".

This is what I have so far:

| inputlookup Classes.csv
| eval timenow = strftime(now(), "%Y-%m-%d")
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000)
| eval ConvertedTodaysDate = strptime(timenow, "%Y-%m-%d")
| eval Status = IF((ConvertedTodays_Date < ConvertedClassDate), "Expired", "Valid")
| table Username, Class_Date, Status

All help is greatly appreciated!

Re: How to compare dates for expiration purposes?

493669 — Thu, 15 Feb 2018 16:36:15 GMT

now() is already in epoch so no need to convert...simply you can compare like

| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")

so you can try this:

| inputlookup Classes.csv
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000)
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
| table Username, Class_Date, Status

Here Class_Date is expected in Year-month-day format like 2018-02-15

Re: How to compare dates for expiration purposes?

albinortiz — Fri, 16 Feb 2018 06:09:36 GMT

Hello,

I am almost there. I can see the valid and expired ones. However, this is happening:

username1 2018-1-1 Valid
username3 2018-12-12 Valid
username4 2017-1-2 Expired
username5 2017-1-3 Expired

The first entry should be expired since today's date is 2018-02-15. Why is the code making the comparison using the year?

Re: How to compare dates for expiration purposes?

493669 — Fri, 16 Feb 2018 06:27:10 GMT

When I tried I get exact reverse output ...check what sign you are using > or < in eval expression:
Try this run anywhere search:

|makeresults|eval Class_Date="2018-1-1"
|append[|makeresults|eval Class_Date="2018-12-12"]
|append[|makeresults|eval Class_Date="2017-1-2"]
|append[|makeresults|eval Class_Date="2017-1-3"]
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
 | eval ConvertedClassDate = ConvertedClassDate + 31536000 |eval current=now()| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")

Re: How to compare dates for expiration purposes?

albinortiz — Fri, 16 Feb 2018 21:31:53 GMT

The issue I see is that 2018-12-12 is not expired and it is marking it as expired. If I change the comparison character, it will say 2018-01-01 is valid when in reality it is not. I feel like it is making the comparison using the year and not the whole date.

Username ClassDate Status

username1 2018-1-1 Valid
username2 2018-1-1 Valid

These two lines should be Expired.

I tried yours and it also does the same thing.

Re: How to compare dates for expiration purposes?

493669 — Sat, 17 Feb 2018 00:51:19 GMT

If I tried for date 2018-1-1 it is coming as expired only as expected ...Not sure why are receiving it as valid...could you please provide your query and also try below query :

|makeresults|eval Class_Date="2018-1-1"
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
  | eval ConvertedClassDate = ConvertedClassDate + 31536000 |eval current=now()| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")

Re: How to compare dates for expiration purposes?

albinortiz — Tue, 29 Sep 2020 18:04:51 GMT

Here's my code:
| inputlookup Training.csv
| eval ConvertedClassDate = strptime(ClassDate, "%Y-%m-%d")
| eval ConvertedClassDate = (ConvertedClassDate + 31536000)
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
| table Username, ClassDate, Status

I changed the date your code so you would see what is going on:

Re: How to compare dates for expiration purposes?

micahkemp — Sat, 17 Feb 2018 01:01:14 GMT

You may consider using relative_time instead of adding a year's worth of seconds:

 | eval ConvertedClassDate = relative_time(ConvertedClassDate, "+1y")

Re: How to compare dates for expiration purposes?

micahkemp — Sat, 17 Feb 2018 01:06:12 GMT

I'm not sure where the above effort stood, but this seems to work, and is based from the same makeresults set:

| makeresults 
| eval Class_Date="2018-1-1" 
| append [| makeresults | eval Class_Date="2018-12-12"] 
| append [| makeresults | eval Class_Date="2017-1-2"] 
| append [| makeresults | eval Class_Date="2017-1-3"] 
| eval class_plus_one_year = relative_time(strptime(Class_Date, "%Y-%m-%d"), "+1y")
| eval Status = if(now() > class_plus_one_year, "Expired", "Valid")

Re: How to compare dates for expiration purposes?

493669 — Sat, 17 Feb 2018 01:19:12 GMT

here logic is that :
if (Classdate + 1year ) is less than today's date then it is expired else Valid...
if it is correct logic then
for classdate=2018-12-12 +1year=2019-12-12 is not less than todays date so it will be Valid....right?

Re: How to compare dates for expiration purposes?

493669 — Sat, 17 Feb 2018 01:23:31 GMT

try if(now() > class_plus_one_year, "Expired", "Valid")

Re: How to compare dates for expiration purposes?

albinortiz — Sat, 17 Feb 2018 01:28:22 GMT

Wow. I guess I need to take a break. You have been right the whole time. I will drink a few beers on your name today.

Re: How to compare dates for expiration purposes?

albinortiz — Sat, 17 Feb 2018 01:29:23 GMT

That might be a good option too!

EDIT:

Changed it to that and it also works.

Thanks!

Re: How to compare dates for expiration purposes?

493669 — Sat, 17 Feb 2018 01:31:42 GMT

🙂 ha ha ..