I'm getting a little further with this, however, it only returns the source country and loses the rest the of the information, can anyone advise me what I'm doing wrong?
Log Type: Attack
create temporary table t1 as select srcip, attack, count(*) as attack_count, action from $log where $filter and attack is not null and action is not null group by action, attack, srcip; create temporary table t2 as select distinct srcip, srccountry from $log-traffic where $filter and srcip is not null and srccountry is not null; select t2.srccountry from t2 inner join t1 on t2.srcip=t1.srcip group by srccountry, action, attack order by srccountry, action, attack_count desc
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
Hi,
Try the dataset:
DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; create temporary table t1 as select srcip, attack, count(*) as attack_count, action from $log where $filter and attack is not null and action is not null group by action, attack, srcip; create temporary table t2 as select distinct srcip, srccountry from $log-traffic where $filter and srcip is not null and srccountry is not null; select t2.srccountry AS source_contry, t1.attack_count AS at_count from t2 inner join t1 on t2.srcip=t1.srcip group by source_contry, t1.action, attack, at_count order by at_count desc
AtiT
Thanks AtiT, I'll try it out and let you know.
Ati -
Thanks for the help. In order to get what I was shooting for I had to edit you a little bit, below is the final dataset for others to use. This works on the 5.2.2 Analyzer
DROP TABLE IF EXISTS t1;DROP TABLE IF EXISTS t2;create temporary table t1 as select srcip, attack, count(*) as attack_count, action from $log where $filter and attack is not null and action is not null group by action, attack, srcip; create temporary table t2 as select distinct srcip, srccountry from $log-traffic where $filter and srcip is not null and srccountry is not null; select t2.srccountry AS source_contry, t1.attack_count AS at_count, t1.attack, t1.action from t2 inner join t1 on t2.srcip=t1.srcip group by source_contry, t1.action, t1.attack, at_count order by at_count desc
Hi,
I need to create a report similar to what you have described.
I would like to modify it to show the number of block traffic connection attempts coming in from the internet (instead of number of attacks) and still group it by source country being blocked.
Could you please give me some assistance on how to modify the dataset to accomplish this?
thanks
djcarrington
Hey there you could try something close to this - (I'm not near my analyzers at the moment)
select srcip as source, srccountry as source_country, srcintf, count(*) as count, action from $log where $filter and srcip is not null and srccountry is not null group by source, source_country, action, order by count desc
with the above you will see the source, source country, source interface action and count for each action. you would just need to filter the source interface and action to your desired out comes. IE: Srcintf is 'wan1' and action is 'blocked'.
fyi,
Working with a coworker, we came up with the dataset (listed below).
It also excludes the Reserved source country entries.
We then referenced the Dataset from a chart table and also a pie chart for a report, that shows the number of denied log entries by source country.
thanks for the help
Dave
------------
Log type: traffic
Query: DROP TABLE IF EXISTS T1; create temporary table T1 as select srccountry, count(*) as denied_traffic_count from $log-traffic where $filter and action = 'deny' and action is not null and srccountry NOT IN('Reserved') group by srccountry; select srccountry, denied_traffic_count from T1;
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
User | Count |
---|---|
1702 | |
1092 | |
752 | |
446 | |
228 |
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2024 Fortinet, Inc. All Rights Reserved.