Splunk Search

Age calculation based on Date

mbasharat
Builder

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!!!!

Tags (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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")
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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")
---
If this reply helps you, Karma would be appreciated.
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 ...