Age calculation based on Date

[mbasharat]

Hi,

I have a field in my data that is called "date". This "date" is when a vulnerability was seen the first time. I need a calculation that allows me to do following:

<30,"Less than 30 Days"
30-60,"30-60 Days"
60-90,"60-90 Days"
90-180,"90-180 Days"
180-365,"30 Days to 1 Year"
>365,"Over 1 Year"
0==0,"No Age Data"

I used below. It works BUT it is not breaking down the way I need. Instead, below is generating <30 Days, No Age Data and Over 60 Days. So basically, everything that is over 60 which is the second statement in second EVAL below is being added in Over 60 field. I need them separate.

| eval age=ceiling((now()-strptime(date,"%Y-%m-%d"))/86400)
| eval Vulnerability_Age=case(
age<30,"Less than 30 Days",
age>=60,"Older than 60 Days",
age>=90,"Older than 90 Days",
age>=120,"Older than 120 Days",
age>=180,"Older than 180 Days",
age>=365,">Older than 1 Year",
0==0,"No Age Data")

Thanks in-advance!!!!

[richgalloway · Accepted solution]

Expressions in case statements are evaluated left-to-right and stop at the first match. That means a date older than 365 days will match the "Older than 60 Days" case because 365 > 60. To get the results you desire, change the order of expressions.

| eval age=ceiling((now()-strptime(date,"%Y-%m-%d"))/86400)
| eval Vulnerability_Age=case(
age<30,"Less than 30 Days",
age>=365,">Older than 1 Year",
age>=180,"Older than 180 Days",
age>=120,"Older than 120 Days",
age>=90,"Older than 90 Days",
age>=60,"Older than 60 Days",
0==0,"No Age Data")