Splunk Search

Determining dates older than 90 days

ajdyer2000
Path Finder

Hi.

I have a Field called "Hire Date"
The format for this appears as "4/10/2018 12:00:00 AM"

Basically all the dates appear as 12:00 AM so that the time doesn't matter.

Question is how could you only have dates older than 90 days show up in the query?

Thanks so much
Alan D

0 Karma

somesoni2
Revered Legend

General idea is to convert the field values (may be temporarily) to epoch format using strptime function and then compare it with current time -90 days. Splunk has function relative_time to do the later part. Try something like this

your search
| where strptime('Hir Date',"%m/%d/%Y %H:%M:%S %p")<relative_time(now(),"-90d@d")

ajdyer2000
Path Finder

That didn't seem to work. I've took out the time and now I just have the dates. I need to see only CreateDate that are over 90 days.

CreateDate
1/11/2018
8/17/2017
5/25/2018
5/10/2018
5/17/2018
5/30/2018
12/15/2010
4/23/2018

0 Karma

somesoni2
Revered Legend

Try like this (runanywhere sample search, everything before where is just to generate sample data, replace everything before where with your search

| gentimes start=-1 | eval CreateDate="1/11/2018 8/17/2017 5/25/2018 5/10/2018 5/17/2018 5/30/2018 12/15/2010 4/23/2018" | table CreateDate | makemv CreateDate | mvexpand CreateDate 
| where strptime('CreateDate',"%m/%d/%Y")<relative_time(now(),"-90d@d")
0 Karma

burakcinar
Path Finder

hello ajdyer2000,

could you try this one ?

index=xxx
| eval testDate=strptime("Hire Date","%d-%m-%Y %H:%M:%S")
| where testDate < relative_time(now(),"-30d@d")

0 Karma

ajdyer2000
Path Finder

That didn't seem to work. I've took out the time and now I just have the dates. I need to see only CreateDate that are over 90 days.

CreateDate
1/11/2018
8/17/2017
5/25/2018
5/10/2018
5/17/2018
5/30/2018
12/15/2010
4/23/2018

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...