- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
FAZ - Geoloc report
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
Solved! Go to Solution.
- Labels:
-
FortiAnalyzer
-
FortiGate
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created on 05-26-2022 11:46 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @Anonymous ,
did you take the time to look at it? Time is running out for me :)
Thank you.
Jirka
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Debbie,
it works like a charm!
Jirka
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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) :)