Geo IP Location SQL Table available in FortiAnalyzer's Datasets?

[CrisPete]

Hello,

I need to design a report on VPN Remote IP countries. There is no srccountry or similar field in the VPN event logs, so I wonder if the Geo IP Location info is available in SQL as a table that can be queried in a dataset.
Alternatively, I wonder if the Dataset design interface would allow me to create this table, operation that would require the interface to accept a quite large amount of text in the CREATE TABLE command. Also I wonder if in 7.4 ar more recent releases the possibility to create permanent tables (not temporary!) has been restored.

Thanks

[Stephen_G]

Hello CrisPete,

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.

Thanks,

[Stephen_G]

Hello,

We are still looking for someone to help you.

We will come back to you ASAP.

Thanks,

[Stephen_G]

Hi again CrisPete,

I found the following information. Please let me know if it helps:

To design a report on VPN remote IP countries using FortiAnalyzer, you can follow these steps:

Geo IP Location Information: In FortiAnalyzer, geo-location information such as 'srccountry' and 'dstcountry' is typically parsed into Fabric logs. If these fields are not directly available in the VPN event logs, you may need to ensure that the logs are being parsed correctly to include geo-location data.

Querying Geo IP Data: If the geo-location data is available in the logs, you can create a dataset using SQL queries to extract this information. Use the normalized SIEM fields like 'src_geo_country' and 'dst_geo_country' if they are available.

Creating a Custom Table: As of FortiAnalyzer 7.6.0, the interface allows for creating custom datasets and querying metadata tables. However, creating permanent tables directly through the dataset design interface is not typically supported. The interface is designed for querying existing data rather than creating new permanent tables.

Dataset Design Interface: You can use the dataset design interface to create complex queries and visualize data. However, the interface is not intended for executing large 'CREATE TABLE' commands or managing database schema changes.

Permanent Tables: The ability to create permanent tables directly through the FortiAnalyzer interface is not a standard feature. You may need to rely on existing data structures and use SQL queries to manipulate and report on the data.

[CrisPete]

Hello, thank you very much for your effort!
I know that starting 7.4.x the FortiGate adds the country information in the Event logs related to VPN. My problem is that the FG has 7.2.x, and I was hoping that the FAZ could have included into SQL the Geo IP Location as a table -- because the FAZ 7.2.10 resolves the country in FortiView (displaying the country and the city, clearly using the Geo IP Location info).