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.
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"
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?