Splunk Search

Extract data for last 3 months

aartivig289
Engager

Hi All,

I am searching from a csv lookup.
The CSV contains fields -->
1. Reporting Month & Year -->17-Jan, 17-Feb, so on...
2. Account name --> these accounts have had transactions in the months reported in the CSV

My requirement is to extract the accounts, that have been active in the last 3 months on the basis of Reporting month & year.

Tags (2)
0 Karma

lguinn2
Legend

Assuming that the field for the reporting month and year is named "datetime", try this

|inputlookup your.csv
| eval current_year = strftime(now(),"%Y")
| eval epoch_time = strptime(datetime . "-" .  current_year,"%d-%m-%Y")
| eval epoch_time = if(epoch_time<=now(),epoch_time,relative_time(epoch_time,"-1year")
| where epoch_time > relative_time(now(),"-3mon@mon")

This was a little tricky because of the lack of a year in the timestamp. The second line of the search picks up the current year (when the search is running) - right now that would be 2017. The third line appends this to the datetime from the csv.
But what if the datetime from the csv is 29-Dec? Then the resulting epoch_time would be December 29, 2017 - clearly wrong!
So the 4th line tests: if the epoch_time is AFTER now, then subtract a year from it.
After all of that, filter the events, keeping only those with a datetime in the last 3 months.

If needed, you could summarize at the end by adding something like

| stats count by Account
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...