Splunk Search
Highlighted

How can I separate the event by condition?

Explorer

Hi,

I think it is quite complicated and try to explain clearly. I got the firewall log with the following fields

src_ip     action    service           dst_ip     
1.1.1.1    allowed   tcp_8080          1.1.1.2
1.1.1.1    blocked   tcp_8081          2.2.2.2

What I want to do is the following.
1. Show the top 10 srcip that with maximum number of blocks in 5 minutes
2. For each src
ip, show the service summary of attacks blocked and corresponding number of each service
3. For each srcip, show the service summary of allowed, corresponding number of each service and corresponding dstip.

Therefore the desired table is as below.

Src_ip         Blocked Service       Block Count       Allowed Service    Allow Count     Dst_ip for allowed service
1.1.1.1        tcp_8081              100               tcp_8080           20              2.2.2.2

I got stuck in how to separate the service into two based on the condition. In this case, I would like to separate the service into serviceallow and serviceblock based on the "action", I tried to find many information but not help so much.

Please give me a hand for help. Thanks.

0 Karma
Highlighted

Re: How can I separate the event by condition?

Builder

Try this...

| eval AlllowedService=if(action="allowed",service,null())
| eval DestIP=if(action="allowed",dest_ip,null())
| eval BlockedService=if(action="blocked",service,null())
| stats values(BlockedService) AS "Blocked Service" count(BlockedService) AS "Block Count" values(AllowedService) AS "Allowed Service" count(AllowedService) AS "Allowed Service" values(DestIP) AS DestIP by src_ip

If there are a lot of results by src_ip will need to do counts for allowed/blocked separately then something like below

| stats count AS Count by src_ip action service dst_ip
| eval AllowedService=if(action="allowed",service,null())
| eval AllowedServiceCount=if(action="allowed",Count,null())
| eval DestIP=if(action="allowed",dest_ip,null())
| eval BlockedService=if(action="blocked",service,null())
| eval BlockedServiceCount=if(action="blocked",Count,null())
| stats list(BlockedService) AS BlockedService list(BlockedServiceCount) AS BlockedCount list(AllowedService) AS AllowedService list(AllowedServiceCount) AS AllowedCount list(DestIP) AS DestIP by src_ip

Good luck. Hope this helps.

View solution in original post

Highlighted

Re: How can I separate the event by condition?

Builder

No sure where the two "5." are coming from in code markup... delete when using.

0 Karma
Highlighted

Re: How can I separate the event by condition?

Builder

Any luck with approach above?

0 Karma
Highlighted

Re: How can I separate the event by condition?

Explorer

Thank you very much, the second approach works with what I want to do.

0 Karma
Highlighted

Re: How can I separate the event by condition?

Explorer

Additional question: I would like to show top 10 IP addresses that showing the list of block with corresponding number > 50, when I tried to set the condition

" where BlockedCount>50 "

it only shows one of the block >50 for that IP address, I tried to sum up all the blocks in two hours and only limiting total number of blocks, it can show that IP containing multiple blocks > 50 but this also showing blocks < 50, what can I do in order to show the block list with blocks > 50 only for that particular IP address?

0 Karma
Highlighted

Re: How can I separate the event by condition?

Builder

Not sure I am following. Can you post where you are putting the where in the query?

0 Karma
Highlighted

Re: How can I separate the event by condition?

Explorer

sourcetype=mytraffic action=blocked OR action=allowed | bin _time span=5m
| stats count as Count list(dstip) as dstip by src
ip action service time
| eval.......<>
| stats list(BlockedService) AS BlockedService list(BlockedServiceCount) AS BlockedCount list(AllowedService) AS AllowedService list(AllowedServiceCount) AS AllowedCount list(DestIP) AS DestIP by src
ip

The output would be
srcip Blocked Service BlockCount Service Allowed Allow Service Count Destination IP
1.2.3.4 SNMP 30
SNMP 28
SNMP 3
SNMP 2
SNMP 2

After add the "| where Block_Count>5" at the end , it outputs nothing.

What I expected for :
srcip Blocked Service BlockCount Service Allowed Allow Service Count Destination IP
1.2.3.4 SNMP 30
SNMP 28

Are there any wrong?

0 Karma
Highlighted

Re: How can I separate the event by condition?

Builder

I think I am following. Add what you want to filter on as a sum or max in the second stats command then use that for your filter...

| stats max(BlockedServiceCount) AS maxBlockedServiceCount sum(BlockedServiceCount) AS sumBlockedServiceCount list(BlockedService) AS BlockedService list(BlockedServiceCount) AS BlockedCount list(AllowedService) AS AllowedService list(AllowedServiceCount) AS AllowedCount list(DestIP) AS DestIP by src_ip
| where ...
| fields - maxBlockedServiceCount sumBlockedServiceCount

Does that help?

0 Karma
Highlighted

Re: How can I separate the event by condition?

Explorer

Sorry may be I described badly in last reply, what I want to filter is the block of service that less than 50 in 5min window for each of the IP address

Therefore the output without filter is following
srcip maxServiceBlockCount totalblock Blocked Service BlockCount
1.2.3.4 1201 12890 SNMP 55
SNMP 28

HTTP 1201

2.3.4.5 1213 14565 H.323 50
BGP 123
AOL 1213

And expected result after filter is following

srcip maxServiceBlockCount totalblock Blocked Service BlockCount
1.2.3.4 1201 12890 SNMP 55

HTTP 1201 <<< SNMP 28 is filtered for this IP address

2.3.4.5 1213 14565 H.323 50
BGP 123
AOL 1213

0 Karma