Splunk Search

How to search average outbound connections per month, week, and day?

alaking
Explorer

I am trying to create a baseline for average outbound connections per day/week/month. I started with this as my search:

source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16) | stats count by src, dst, srcprt | stats avg(count) by 1d@d*

However, there doesn't seem to be any results. The fields are correct, and it shows a table listing with dst, src count when I remove the part of the search after the last pipe.

My goal is to return 3 fields: Average Connections per day, per week, and per month that I can print to a flat file.

Any help would be greatly appreciated.

0 Karma
1 Solution

woodcock
Esteemed Legend

Try this:

source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
| bucket _time span=1d
| stats count by _time
| stats avg(count) AS dailyAvg
| appendcol
    [search source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
    | bucket _time span=1w
    | stats count by _time
    | stats avg(count) AS weeklyAvg]
| appendcol    [search source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
    | bucket _time span=1mon
    | stats count by _time
    | stats avg(count) AS monthlyAvg]

Or if more breakout is desired, perhaps this:

source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
| bucket _time span=1d
| stats count by src, dst, srcprt _time
| stats avg(count) AS dailyAvg BY src, dst, srcprt
| appendcol
    [search source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
    | bucket _time span=1w 
    | stats count by src, dst, srcprt _time
    | stats avg(count) AS weeklyAvg BY src, dst, srcprt
    | appendcol
        [search source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
            | bucket _time span=1mon
            | stats count by src, dst, srcprt _time
            | stats avg(count) AS monthlyAvg BY src, dst, srcprt]

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this

source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
| bucket span=1d _time
| stats count by src, dst, srcprt _time
| appendpipe       
    | stats avg(count) as av
    | eval ReportType=__@STR@_
    | eval Include="DailyAvg"]
| appendpipe    
    | bucket span=1w _time
    | stats sum(count) as count by _time
    | stats avg(count) as av
    | eval ReportType="Y"
    | eval Include="WeeklyAvg" ] 
| appendpipe    
    | bucket span=1mon _time
    | stats sum(count) as count by _time
    | stats avg(count) as av
    | eval ReportType=__@STR@_
    | eval Include="Y" ]
| where Include="MonthlyAvg"
| xyseries Include ReportType avg
0 Karma

woodcock
Esteemed Legend

Try this:

source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
| bucket _time span=1d
| stats count by _time
| stats avg(count) AS dailyAvg
| appendcol
    [search source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
    | bucket _time span=1w
    | stats count by _time
    | stats avg(count) AS weeklyAvg]
| appendcol    [search source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
    | bucket _time span=1mon
    | stats count by _time
    | stats avg(count) AS monthlyAvg]

Or if more breakout is desired, perhaps this:

source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
| bucket _time span=1d
| stats count by src, dst, srcprt _time
| stats avg(count) AS dailyAvg BY src, dst, srcprt
| appendcol
    [search source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
    | bucket _time span=1w 
    | stats count by src, dst, srcprt _time
    | stats avg(count) AS weeklyAvg BY src, dst, srcprt
    | appendcol
        [search source=fwlogs earliest=-2mon@m latest=@m NOT (dstip=10.0.0.0/8 OR dstip=172.16.0.0/12 OR dstip=192.168.0.0/16)
            | bucket _time span=1mon
            | stats count by src, dst, srcprt _time
            | stats avg(count) AS monthlyAvg BY src, dst, srcprt]
0 Karma

alaking
Explorer

This works as intended thank you! However, I've noticed it is too slow for my purposes so I'm going to use data models 🙂

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...