FAZ - Geoloc report

[Jirka1]

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

[Anonymous]

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]

Hello @Anonymous ,

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

Thank you.

Jirka

[Debbie_FTNT · Accepted solution]

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

[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) :)