topic Re: After grouping by Date how can I SUM against a column? in Splunk Search

After grouping by Date how can I SUM against a column?

voninski — Tue, 29 Sep 2020 09:54:40 GMT

I am running the following query

index=security sourcetype=WeatherUnderground | eval Date=strftime(_time,"%m/%d/%y") |stats latest("current_observation.precip_today_in") as DailyRainfall by Date | table Date, DailyRainfall

And it produces data as it is supposed to, showing me the daily totals of rainfall. I want to ultimately create a table that is the SUM of the daily rainfall for each day in the month and then display it as a MonthYear , MonthlyTotal.

I have tried using the bin command to group by month after the stats and I have also tried to extract the month after I run the above query with a pipe to eval MonthYear=strftime(_time,"%B %Y") but the data doesn't show up. How can I turn this data into a table of monthYear , monthlyTotals? Attached is the current query and results. Thank you.

Re: After grouping by Date how can I SUM against a column?

acharlieh — Fri, 10 Jun 2016 23:15:50 GMT

After running stats, all fields other than your group by field or your calculations are gone. So you cannot eval any other values out of them at that point, and that's why running eval with _time as an input was failing.

Might I suggest other commands? such as timechart where you would keep _time as an epoch value making rolling up later easier... Assuming you only want monthly totals:

index=security sourcetype=WeatherUnderground | timechart span=1d latest("current_observation.precip_today_in") as DailyRainfall | timechart span=1mon sum(DailyRainfall) as MonthlyTotal

your fields after this would be _time (epoch time locked to the month) and MonthlyTotal ... and you can eval and format to your hearts content after this point

Alternatively, if you wanted both Daily Rainfall, and Monthly totals you could use appendpipe to get both sets of data in one search:

index=security sourcetype=WeatherUnderground | timechart span=1d latest("current_observation.precip_today_in") as DailyRainfall | appendpipe [timechart span=1mon sum(DailyRainfall) as MonthlyTotal]

you may wind up doing some sorting on _time afterwards ... but if you were guaranteeing that your timescale was within the month, then you may actually only want to use addtotals or addcoltotals. But that's likely not the format you're looking for.

Re: After grouping by Date how can I SUM against a column?

voninski — Fri, 10 Jun 2016 23:31:27 GMT

Thank you very much. I didn't realize my other fields were lost after stats. Timechart works great . And I have been able to combine it with my other data (which comes from temp sensors around the house and build the following chart now)

Monthly Statistics
Month Max Temp Min Temp Monthly Range Monthly Rainfall
June 2016 90.5 52.5 38.0 0.09
May 2016 83.6 25.7 57.9

April 2016 79.1 27.7 51.4