Skip to main content
Terrence_M
Terrence_M
New Member
August 7, 2015
Question

Need help getting source country for attack report...

  • August 7, 2015
  • 3 replies
  • 12070 views

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

    3 replies

    AtiT
    AtiT
    New Member
    August 10, 2015

    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
    Terrence_MAuthor
    New Member
    August 10, 2015

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

    Terrence_M
    Terrence_MAuthor
    New Member
    August 14, 2015

    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
    djcarrington
    New Member
    May 9, 2016

    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
    Terrence_MAuthor
    New Member
    May 24, 2016

    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
    djcarrington
    New Member
    June 22, 2016

    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;

    Terms & ConditionsAccessibility statement