Splunk Search

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

atammana_splunk
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

davelugo
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

somesoni2
SplunkTrust
SplunkTrust

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"

somesoni2
SplunkTrust
SplunkTrust

Are you always going to run this query for YTD?

0 Karma

pradeepkumarg
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

atammana_splunk
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

pradeepkumarg
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...