Need help getting source country for attack report...

[Terrence_M]

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

[AtiT]

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

[Terrence_M]

Thanks AtiT, I'll try it out and let you know. 

[Terrence_M]

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

[djcarrington]

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

[Terrence_M]

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'.  

[djcarrington]

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;