Splunk Search

How to only search data for the last day of any month within a chosen time range?

Splunk Employee
Splunk Employee

Hey all,

I have a bunch of billing data that is cumulative (month to date). I'm trying to gather total costs per month, but obviously cannot use my full data set, as it would be re-summing the billing data from the previous 30 days of the month (ie. I only want to use the billing data associated with the last day of any given month). Is there a way to pull data associated with the last day of any month within a chosen time range (ie. year to date) without going month by month? I'd like to avoid using case logic, such as

eval last_day=case(month=01, 31, month=02, 28, month=03, 31)...

Thanks for any input.

0 Karma

New Member

Use dedup early-ish in the pipeline. For instance, to get the event from the last date of each month: dedup date_month

0 Karma

Revered Legend

You can use this subsearch to get the data from last day of last closed month for any time range (works based on latest time selected)

your base search [| gentimes start=-1 | addinfo | eval earliest=relative_time(info_max_time,"@mon-1d")  | table earliest | eval latest=relative_time(earliest,"+1d") | table earliest latest | format] | rest of the search

e.g. if you select YTD today (July 27), it will select all data ingested on "06/30/2015".
if you select timerange as 01/01/2015 to 01/06/28, it will select all data ingested on "05/31/2015"

Revered Legend

Are you always going to run this query for YTD?

0 Karma

Influencer

Do you have the month field already extracted? In that case doing dedup on the month should retain only the last available event for each month.

..| dedup month
0 Karma

Splunk Employee
Splunk Employee

I'm extracting the month from a date field, and using the deduplication. I understand how this should work in theory:

*code* |  eval  month=strftime(strptime(InvoiceDate,"%Y/%m/%d"),"%m")| dedup month| stats sum(TotalCost) by Customer

but this yields no results with only one event matched. If I remove the dedup, it yields the results as expected. I believe it is matching the latest event on that last invoice billing day, rather than the entire bill. In other words, it is only matching one customer - the last customer that was pulled in during the last data pull. If this seems like a logical conclusion, is there any workaround for this that allows all customers and all events on the latest invoice to be pulled in?

0 Karma

Influencer

You will have to add another field to dedup to retrieve the unqiue combination of the results.. May be | dedup month Customer

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!