Skip to main content
Smithester
Smithester
New Member
September 25, 2018
Solved

Report to show number of policy violations per source country for a given period

  • September 25, 2018
  • 1 reply
  • 4450 views

Hi Guys

 

First post here so please go easy on me! ;)

 

I'd like to be able to create a report that contains a chart the number of denied connection attemps that are made, per country, from the internet.  

 

From Log View I'm able to see this information by putting in either of the following filters:

smart_action="Policy violation" -srccountry=Reserved 

or

smart_action="Policy violation" -srcip=172.16.0.0/12,10.0.0.0/8,192.168.0.0/16

 

When I click on 'chart builder' I see the following queries:

 

select `srccountry`, `dstip`, `service` from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and ( ( ( (`action`='deny') )) AND (lower(`srccountry`) != lower('Reserved')))

 

select `srccountry`, `dstip`, `service` from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and ( ( ( (`action`='deny') )) AND (not (`srcip` <<= inet('172.16.0.0/12'))) AND (not (`srcip` <<= inet('10.0.0.0/8'))) AND (not (`srcip` <<= inet('192.168.0.0/16'))))

 

I've no idea how to convert that into a chart that contains the sum total of attempts per country.

 

Ideally, I'm looking for something that outputs either as a graph or a pie chart the following (example) information:

 

USA: 1034

Ukraine:789

Germany: 543

Egypt:331

(you get the gist!)

 

Does anybody have any idea how to achieve this?

 

 

Many thanks

    Best answer by tanr

    Would be useful.  Maybe using one or both of the datasets:

     

    utm-Top-Attack-Source

    select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as totalnum from $log where $filter group by user_src order by totalnum desc

     

    CTAP-Top-Source-Countires

    select srccountry, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(srccountry) is not null and srccountry <> 'Reserved' group by srccountry having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc, srccountry

     

    1 reply

    tanr
    tanrAnswer
    New Member
    September 25, 2018

    Would be useful.  Maybe using one or both of the datasets:

     

    utm-Top-Attack-Source

    select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as totalnum from $log where $filter group by user_src order by totalnum desc

     

    CTAP-Top-Source-Countires

    select srccountry, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(srccountry) is not null and srccountry <> 'Reserved' group by srccountry having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc, srccountry

     

    Smithester
    SmithesterAuthor
    New Member
    September 26, 2018

    Hey tanr

     

    That was a good shout. Both of those had aspects of what I needed. Luckily I've got a colleague who's pretty good with SQL and after looking at both of those he was able to get exactly what I was looking for.

     

    If it helps anybody else this the Dataset we created:

     

    select `srccountry` as SourceCountry, count(*) as totalnum from $log where $filter and logid_to_int(logid) not in (4, 7) and ( ( ( (`action`='deny') )) AND (lower(`srccountry`) != lower('Reserved')))  group by SourceCountry order by totalnum desc

    tanr
    tanr
    New Member
    September 27, 2018

    Thanks for that dataset Smithester!  I already plugged it into a custom report to scare people with.

    Terms & ConditionsAccessibility statement