Splunk Search

How to compare dates for expiration purposes?

albinortiz
Engager

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!

Tags (2)
0 Karma
1 Solution

493669
Super Champion

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

View solution in original post

0 Karma

micahkemp
Champion

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")
0 Karma

493669
Super Champion

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

0 Karma

micahkemp
Champion

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

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

albinortiz
Engager

That might be a good option too!

EDIT:

Changed it to that and it also works.

Thanks!

0 Karma

albinortiz
Engager

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?

0 Karma

493669
Super Champion

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")
0 Karma

albinortiz
Engager

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.

0 Karma

493669
Super Champion

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")
0 Karma

albinortiz
Engager

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:

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

0 Karma

493669
Super Champion

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?

0 Karma

albinortiz
Engager

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.

0 Karma

493669
Super Champion

🙂 ha ha ..

0 Karma

493669
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...