How do I use date_mday to create a table with multiple fields?

[jnames10]

Hi Splunkers, newish user here...
I'm looking at firewall logs, I want to create a table with number of blocked IP for each day, a cumulative daily average, number of daily block events, average events.

I thought this (which works with a single pair of stats/streamstats lines) would work:

index=phase1 action=block tag=site1

| stats dc(src) as dailyip by date_mday
| streamstats avg(dailyip) as ip_average

| stats count as dailyevt by date_mday
| streamstats avg(dailyevt) as evt_average

| table date_mday, ip_average, dailyip, dailyevt, evt_average

and create a table like this....

date_mday   ip_average  dailyip dailyevt    evt_average
4           3082            3082
5           3439            3260.5
6           3578            3366.33
7           4210            3577.25
8           2545            3370.8

But it doesnt work.... It looks like I cannot use date_mday across 2 strings like that?

Can someone give me some hints (or a solution!!)?

Many thanks.

[richgalloway · Accepted solution]

It's not date_mday that's throwing you off, it's stats. The second stats command is counting the number of results returned by the first stats command. On top of that, there's no time information returned by stats so there's nothing to use to calculate date_mday.

The good news is you can combine the stats commands. Try this:

index=phase1 action=block tag=site1
| stats dc(src) as dailyip count as dailyevt by date_mday
| streamstats avg(dailyip) as ip_average avg(dailyevt) as evt_average
| table date_mday, ip_average, dailyip, dailyevt, evt_average

[jnames10]

Brilliant thank you - clearly a newbie question! Didn't think to combine them. Thanks again.