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.
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.
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.
No sure where the two "5." are coming from in code markup... delete when using.
Any luck with approach above?
Thank you very much, the second approach works with what I want to do.
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?
Not sure I am following. Can you post where you are putting the where in the query?
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?
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?
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
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
2.3.4.5 1213 14565 H.323 50
BGP 123
AOL 1213