Extract data for last 3 months

[aartivig289]

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.

[lguinn2]

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