Splunk Search

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

jnames10
Explorer

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.

1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

jnames10
Explorer

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

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