topic Re: Previous month search based on now in Splunk Search

Previous month search based on now

antonio147 — Fri, 13 Nov 2020 17:20:01 GMT

Hi everyone,
I'm new to Splunk and trying to create a simple report, but I'm already having trouble.
I would like to do a search on a DATA_ACA field that contains dates in this format: 2020-11-13 15:10:23.
The search must return all those events that have the previous month in the DATA_ACA field, therefore all the events that have: 2020-10- *

I tried with
index=........
| eval month_aca = strptime (relative_time (now (), "- 1mon @ d") "% m)
| eval year_aca = strptime (relative_time (now (), "- 1mon @ d") "% Y)
| eval data_aca = year_aca. "-". month_aca. "- *"
| search DATA_ACA = data_aca
.....

....
| table DATA_ACA, month_aca, year_aca, data_aca

but nothing returns me no event.
You can help me?

Do you have any suggestions ?
Tks
Bye
Antonio

Re: Previous month search based on now

richgalloway — Fri, 13 Nov 2020 20:05:45 GMT

Try converting DATA_ACA into epoch form and then comparing it to the first and last days of the month.

index=foo | eval aca_epoch = strptime(DATA_ACA, "%Y-%m-%d") | where (aca_epoch >= relative_time(now(), "-1mon@mon") AND aca_epoch < relative_time(now(), "@mon")) | table DATA_ACA

If you want to keep the current query, be aware that the search command treats the RHS as a string. Use the where command to compare a field to another field.

index=........ | eval month_aca = strptime (relative_time (now (), "- 1mon @ d") "% m) | eval year_aca = strptime (relative_time (now (), "- 1mon @ d") "% Y) | eval data_aca = year_aca. "-". month_aca. "- *" | where DATA_ACA = 'data_aca' ..... .... | table DATA_ACA, month_aca, year_aca, data_aca

Re: Previous month search based on now

antonio147 — Fri, 13 Nov 2020 20:35:28 GMT

Hi richgalloway,
Thanks for the reply.
A doubt, if I use your suggestion converting to epoch, if now is for example November 12th, where would it take from October 12th to November 11th correct?
I need to extract all the October events from 1st to 31st, without any November day.
So for each month, on any day I launch the query, I always have to take only all the days of the previous month.

In any case, thanks, tomorrow I will try your suggestions and let you know the outcome.
Bye
Antonio

Re: Previous month search based on now

richgalloway — Fri, 13 Nov 2020 21:34:00 GMT

The "@mon" argument means "the beginning of the current month", which is 1st Nov in our context. So, when the SPL says "aca_epoch < relative_time(now(), "@mon")" it's accepting dates up to 31 Oct 2020 23:59:59. That's what's desired, right?

Re: Previous month search based on now

antonio147 — Sat, 14 Nov 2020 15:04:40 GMT

Hi richgalloway,
your suggestion was very valuable,
with the where and with
| eval aca_epoch = strptime (DATA_ACA, "% Y-% m-% d")
| where (aca_epoch> = relative_time (now (), "-1mon @ mon")
AND aca_epoch <relative_time (now (), "@mon"))
it worked the first time.
Also thanks for your explanations, I did not know that @mon took the first day of the month, I thought it was only referring to the number of days / months back or forward.
I understood that the search refers to strings while the where to the content of a field.
I have a question to ask: what is the difference between where and search if both are used as a comparison?
soo for the above or is there more?

and if instead of now (), I wanted to use a date of my choice, how should I put it?
Just replace now () with "2020-09-27" for example?
Thanks again for the tip
a nice we
Antonio

Re: Previous month search based on now

antonio147 — Sat, 14 Nov 2020 14:52:33 GMT

yes and just that
Thank you so much