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

[Smithester]

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

[tanr · Accepted solution]

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]

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]

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