Summarize data, count for today, average for 7 days and variance

[kenbaugher]

We have a data in splunk that is basically   DATE/APPLNAME/COUNT, there are about 15 applications, and we would like to create a table that shows by application, the current days count, the 7 day average, and the variance of today, to the average.  I've tried a number of things with different searches like appendcols, but not getting the results.   I can produce the count or the average, but can't seem to put them together correctly. 

[ITWhisperer]

Try something along these lines (I have used _internal/splunkd but you can easily modify it to your requirements)

index=_internal sourcetype=splunkd earliest=@d-7d latest=now
| bin span=1d _time
| stats count by _time component
| eventstats sum(count) as total by component
| where _time = relative_time(now(), "@d")
| eval 7day_average=(total - count) / 7

[bowesmana]

Here's an example which sets up two weeks of simulated data and then does the calcs you want

| makeresults count=14
| streamstats c
| eval _time=now() - ((c - 1) * 86400)
| fields - c
| eval ApplName=split("ABCDEFGHIJKLMNO","")
| mvexpand ApplName
| eval ApplName="Application ".ApplName
| eval count=random() % 20
| table _time ApplName count
``` The above sets up 2 weeks of data for 15 applications ```

``` Now sort in ascending time and calculate the rolling 7 day average
    at the end (most recent) it will show the average over the previous 
    7 days - based on the PRIOR 7 day. (current=f) ```
| sort _time
| streamstats window=7 current=f global=f avg(count) as Avg by ApplName
``` Then this just calculates the variance ```
| eval Variance=count-Avg
``` And finally put in order and retain only today's numbers ```
| sort ApplName - _time
| where _time >= relative_time(now(), "@d")

This should give you some pointers, but if you want to share what you've tried so far, we can help you get there.

[bowesmana · Accepted solution]

Note that if you are just searching 8 days, then it's as easy and probably more efficient to use stats rather than streamstats.

Note these are simple examples that you can paste into the search window to run

| makeresults count=8
| streamstats c
| eval _time=now() - ((c - 1) * 86400)
| fields - c
| eval ApplName=split("ABCDEFGHIJKLMNO","")
| mvexpand ApplName
| eval ApplName="Application ".ApplName
| eval count=random() % 20
| table _time ApplName count
``` Above creates data ```

``` This is just a simple technique to anchor todays and exclude it from the average ```
| streamstats c by ApplName
| sort _time
``` Then this will take the average (assuming 8 days of data) ```
| stats latest(_time) as _time avg(eval(if(c=1, null(), count))) as Avg latest(count) as count by ApplName
| eval Variance=count-Avg
| sort ApplName - _time
| where _time >= relative_time(now(), "@d")

[kenbaugher]

After a bit of working through this and understanding it, this works perfectly, TY

The below is closer to the actual data I was using.

| makeresults format=csv data="bus_date, appl, tran_count
20250122,appl1,336474
20250122,appl2,93
20250122,appl3,6
20250122,appl4,10585
20250123,appl1,2061
20250123,appl2,1075
20250123,appl3,1
20250123,appl4,190
20250124,appl1,6
20250124,appl2,40635
20250124,appl3,786
20250124,appl4,12978
20250125,appl1,140
20250125,appl2,133
20250125,appl3,514
20250125,appl4,125449
20250126,appl1,98
20250126,appl2,5
20250126,appl3,5258
20250126,appl4,3424
20250127,appl1,596
20250127,appl2,1
20250127,appl3,265
20250127,appl4,3
20250128,appl1,38200
20250128,appl2,1320
20250128,appl3,11706
20250128,appl4,114"
| fields bus_date, appl, tran_count
| streamstats c by appl
| sort bus_date
| stats latest(bus_date) as bus_date avg(eval(if(c=1, null(), tran_count))) as Avg6day latest(tran_count) as todays_total by appl
| eval Variance=todays_total-Avg6day
| sort appl - bus_date
| table bus_date appl, todays_total, Avg6day, Variance