FortiAnalyzer - Specific generated report for all policy changes

[Mike22]

Hi,

I want to generate a specific FortiAnalyzer report as a PDF file, which contains all policy changes of my in total 10 fortigates. Each fortigate has in total 5-10 policies. My ambition is to get an monthly automatic generated PDF report of all policy changes from all policies and fortgates I've done during this time.

The report should list the following issues: Policy ID, Device ID, Device Name, Source IP, Destination IP, Source Interface, Destination Interface, Action, Log ID & the Timestamp of the change.

So I've seen in the FortiAnalyzer, that there is no pre-generated report like that.
I know this report is possible with SQL code/ dataset only. So I've started a small and non finish SQL code because I'm a SQL beginner. This ist my SQL code currently:

My question is, can anybody complete my SQL code so that all issues are included and the validation without any warnings?
And can anybody explain me the nessecary steps and settings I've to do in FortiAnalyzer to get a correct automatic generated PDF report?

Thank you guys!
Mike

[AEK]

Hi Mike

I'm not expert in FortiAnalyzer's Datasets but for your first question I guess you need to add an "order by" command at the end, in order to sort the result.

Something like this:

order by datestamp desc

Then I guess you need to create a chart of type "table" and use your dataset in it.

Alternatively you may use the chart builder (easier) from Log View:

https://docs.fortinet.com/document/fortianalyzer/6.2.0/cookbook/989203/building-charts-with-chart-bu...

Hope it helps

[WaynReau]

Mike22:

Did you ever get this working to any useful state? I actually copied the SQL from your query JPG and it runs but shows no output. Seems like this would be a useful periodic check.

Thanks,

Wayne