How can I separate the event by condition?

[kcchu01]

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 src_ip 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 src_ip, show the service summary of allowed, corresponding number of each service and corresponding dst_ip.

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 service_allow and service_block based on the "action", I tried to find many information but not help so much.

Please give me a hand for help. Thanks.

[snoobzilla · Accepted solution]

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.

[snoobzilla]

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

[snoobzilla]

Any luck with approach above?

[kcchu01]

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

[kcchu01]

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?

[snoobzilla]

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

[kcchu01]

sourcetype=my_traffic 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
src_ip Blocked Service Block_Count 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 :
src_ip Blocked Service Block_Count Service Allowed Allow Service Count Destination IP
1.2.3.4 SNMP 30
SNMP 28

Are there any wrong?

[snoobzilla]

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?

[kcchu01]

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
src_ip max_Service_Block_Count total_block Blocked Service Block_Count
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

src_ip max_Service_Block_Count total_block Blocked Service Block_Count
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