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

FAZ - Geoloc report

Hello,

I would like to process a report of IPS attacks sorted by geolocation (country) for the customer. I'm not familiar with sql - can I request a sample dataset or chart?

FAZ 7.2

Thank you. Jirka
1 Solution
Debbie_FTNT

Hey Jirka,

 

I haven't really had opportunities to test it, but something like this probably:

 

select srccountry, attack, severity, ref, sum(totalnum) as totalnum from ###(select srccountry, attack, severity, ref, count(*) as totalnum from $log where $filter and nullifna(attack) is not null group by srccountry, attack, severity, ref order by totalnum desc)### t where severity='critical' group by srccountry, attack, severity, ref order by totalnum desc

 

 Original dataset is 'App-Risk-Top-Critical-Threat-Vectors'; I added 'srccountry'.

-> you would  have to clone the dataset I mentioned above, or create a new one and paste the text in

-> you would need a new chart and map the columns accordingly (the original chart for the original dataset is "Top Critical Severity IPS Events")

-> there are other datasets/charts for other severities, which you can modify in the same vein (add 'srccountry' in the same places)

If there are other IPS charts that give you a better overview, you can do something very much the same:

- clone the dataset

- add 'srccountry' in each 'select <> from' section
- add 'srccountry' to each 'group by <>' section

- clone the chart

- add a new column to map 'srccountry' to it

-> please note that charts of type drill-down are somewhat rigid and difficult to modify, I would NOT recommend trying to clone and change one of those if you're just starting out with customizing charts and datasets

+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hello @Jirka1 ,

 

Thank you for using the Community Forum.

I will seek to get you an answer or help. We will reply to this thread with an update as soon as possible.

 

Regards,

Jirka1
Contributor III

Hello @Anonymous ,

 

did you take the time to look at it? Time is running out for me :)

Thank you.

Jirka

Debbie_FTNT

Hey Jirka,

 

I haven't really had opportunities to test it, but something like this probably:

 

select srccountry, attack, severity, ref, sum(totalnum) as totalnum from ###(select srccountry, attack, severity, ref, count(*) as totalnum from $log where $filter and nullifna(attack) is not null group by srccountry, attack, severity, ref order by totalnum desc)### t where severity='critical' group by srccountry, attack, severity, ref order by totalnum desc

 

 Original dataset is 'App-Risk-Top-Critical-Threat-Vectors'; I added 'srccountry'.

-> you would  have to clone the dataset I mentioned above, or create a new one and paste the text in

-> you would need a new chart and map the columns accordingly (the original chart for the original dataset is "Top Critical Severity IPS Events")

-> there are other datasets/charts for other severities, which you can modify in the same vein (add 'srccountry' in the same places)

If there are other IPS charts that give you a better overview, you can do something very much the same:

- clone the dataset

- add 'srccountry' in each 'select <> from' section
- add 'srccountry' to each 'group by <>' section

- clone the chart

- add a new column to map 'srccountry' to it

-> please note that charts of type drill-down are somewhat rigid and difficult to modify, I would NOT recommend trying to clone and change one of those if you're just starting out with customizing charts and datasets

+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++
Jirka1

Thank you Debbie,

 

it works like a charm!

 

Jirka

Debbie_FTNT

Hey Jirka,

happy to help :).

If you want to make similar modifications in the future:
- you can look at FortiGate logs to get an idea of what fields are available to filter/sort by

- add the field in question to both the 'select' section and 'group by' section

 

There are some decent YouTube series on SQL if you want to dive into it a bit and try your hand at modifying some datasets (which is a lot easier than creating from scratch) :)

+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++
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