Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
Smithester
New Contributor

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

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

1 Solution
tanr
Valued Contributor II

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

 

View solution in original post

3 REPLIES 3
tanr
Valued Contributor II

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
Valued Contributor II

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

Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors