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

Author
Smithester
New Member
  • Total Posts : 2
  • Scores: 2
  • Reward points: 0
  • Joined: 2018/09/25 03:16:07
  • Status: offline
2018/09/25 07:48:42 (permalink)
0

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
tanr
Platinum Member
  • Total Posts : 613
  • Scores: 20
  • Reward points: 0
  • Joined: 2016/05/09 17:09:43
  • Status: offline
Re: Report to show number of policy violations per source country for a given period 2018/09/25 08:50:56 (permalink) ☄ Helpfulby Smithester 2018/09/26 01:32:07
0
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
 
#2
Smithester
New Member
  • Total Posts : 2
  • Scores: 2
  • Reward points: 0
  • Joined: 2018/09/25 03:16:07
  • Status: offline
Re: Report to show number of policy violations per source country for a given period 2018/09/26 01:34:47 (permalink)
4 (2)
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
#3
tanr
Platinum Member
  • Total Posts : 613
  • Scores: 20
  • Reward points: 0
  • Joined: 2016/05/09 17:09:43
  • Status: offline
Re: Report to show number of policy violations per source country for a given period 2018/09/27 12:05:47 (permalink)
0
Thanks for that dataset Smithester!  I already plugged it into a custom report to scare people with.
#4
Jump to:
© 2018 APG vNext Commercial Version 5.5