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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...