Splunk Search

Previous month search based on now

antonio147
Communicator

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

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

antonio147
Communicator

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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?

---
If this reply helps you, Karma would be appreciated.

antonio147
Communicator

yes and just that
Thank you so much

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.

antonio147
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...