Splunk Search

How to convert date string to date format in string and extract all the dates which are 60 and 90 days earlier than the current day?

vineeth_jain
Explorer

Hi All,

I am unable to convert date string to date format using below SPL query.

eval "-Last Logon Date" = strptime("Last Logon Date", "%m/%d/%Y") | sort "Last Logon Date" | table "User Name", "User Lock Status", "Last Logon Date"

Also while sorting it is not properly performing the operation. Please suggest how to extract date values from Last Logon Date column which are 60 or 90 days earlier.

Thanks,
Vineeth Jain

Tags (3)
0 Karma
1 Solution

woodcock
Esteemed Legend

There are many things wrong here. Field names with spaces are EVIL. Never use sort without a number after it (get in the habit of sort 0 for unlimited). Try this:

... | rename "* * * * *" AS *_*_*_*_*, "* * * *" AS *_*_*_*, "* * *" AS *_*_*, "* *" AS *_*
| eval Last_Logon_Date = strptime(Last_Logon_Date, "%m/%d/%Y") 
| where ((Last_Logon_Date >= relative_time(now(), "-90d")) AND (Last_Logon_Date <= relative_time(now(), "-60d")))
| sort 0 - Last_Logon_Date
| fieldformat Last_Logon_Date = strftime(Last_Logon_Date, "%m/%d/%Y")
| table User_Name User_Lock_Status Last_Logon_Date

View solution in original post

woodcock
Esteemed Legend

There are many things wrong here. Field names with spaces are EVIL. Never use sort without a number after it (get in the habit of sort 0 for unlimited). Try this:

... | rename "* * * * *" AS *_*_*_*_*, "* * * *" AS *_*_*_*, "* * *" AS *_*_*, "* *" AS *_*
| eval Last_Logon_Date = strptime(Last_Logon_Date, "%m/%d/%Y") 
| where ((Last_Logon_Date >= relative_time(now(), "-90d")) AND (Last_Logon_Date <= relative_time(now(), "-60d")))
| sort 0 - Last_Logon_Date
| fieldformat Last_Logon_Date = strftime(Last_Logon_Date, "%m/%d/%Y")
| table User_Name User_Lock_Status Last_Logon_Date

vineeth_jain
Explorer

Thanks for your reply and suggestion about field names. It has worked for me.

Another doubt, how can I export the results of this search using email notification. I have tried alert option but unable to send email notification.

woodcock
Esteemed Legend

Click Accept to close this question and ask another one; let's keep this orderly. The probably answer to your other question is that you did not configure the email settings (correctly) on the search head.

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

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

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...