Creating new FortiAnalyzer Reports???

[Stevomillan]

Forgive me but I' m new to FortiAnalyzer reporting. My manager has asked me to create a weekly report with all of our VPN users activity. The report activity specifically is username, source IP address, connection start time, connection stop time, & total duration. None of the " template" reports will do this for me. The real problem here is creating the initial dataset. Since I' m new, I don' t know how the SQL tables work. I haven' t been able to get the help I need from Fortinet support. My first question would be how to output the list of SQL tables from the CLI. Second, how to put these datasets together. Third, are there any resources available for developing reports without attending formal training? We are currently running v5.0.2. Any help would be MOST appreciated.

[billp]

I' ll take a shot at this since no one else has responded yet. Caveat -- I have limited experience in this area. Just giving you my best guess. My recent discussions with Fortinet led me to believe that FortiAnalyzer is not ready with 5.0.2. There are still a lot of issues with it. My rep is not recommending deployment until 5.0.3 which is due out in May/June time frame. FortiCloud is the recommended interim solution. It can process 1GB of log data for free, so that might be enough for you to determine if it will do what you want. That said, I' ve played around with SQL tables in FortiAnalyzer 4.3 which is vaguely similar to what' s available in 5.0.2. VPN information is stored in the Event Log. It records a VPN login event and a separate logout event. It would take some SQL wizardry to correlate the two events and calculate the total session time. The docs for FortiAnalyzer 4.3 contain the table and field names. I am guessing that most of the fields are similar. If you take a look at that, you might be able to cobble together a SQL statement that would give you some of what you' re asking for Fortinet is aware that the Fortianalyzer is missing some basic reports. What you are asking for is not very unusual and the device should be able to provide that. It' s possible that something like that could be included in 5.0.3.

ORIGINAL: Stevomillan Forgive me but I' m new to FortiAnalyzer reporting. My manager has asked me to create a weekly report with all of our VPN users activity. The report activity specifically is username, source IP address, connection start time, connection stop time, & total duration. None of the " template" reports will do this for me. The real problem here is creating the initial dataset. Since I' m new, I don' t know how the SQL tables work. I haven' t been able to get the help I need from Fortinet support. My first question would be how to output the list of SQL tables from the CLI. Second, how to put these datasets together. Third, are there any resources available for developing reports without attending formal training? We are currently running v5.0.2. Any help would be MOST appreciated.

[Stevomillan]

Thanks for the reply. I' ve seen some of the documentation on v4.0. The data map is really what I' m looking for because as soon as I get that I can start building queries. Is there a command that I can run from the command line that will output the SQL tables?

[billp]

I don' t believe there' s a CLI command to dump the SQL structure. Appendix C of the FAZ v4.0 MR3 Administration guide has a decent dump and explanation of the fields. I am not sure why they left this out of the 5.0 admin guide. If you want to take it to the next step, you could install your own mySQL server and setup a remote database for log storage. That would give you a way to see the files directly. If you' re not familiar with mySQL setup, Lynda.com has a good tutorial on setting it up in a VM here: http://www.lynda.com/PHP-tutorials/Up-Running-Linux-PHP-Developers/101840-2.html P.S. Just found this tech doc with more info on fields available, albeit for 4.0 MR2. It' s at least a start. http://kb.fortinet.com/kb/microsites/search.do?cmd=displayKC&docType=kc&externalId=fortianalyzer-for...

[MRNES7]

hi i have a client that would like a fortinet report that includes everything. meaning VPN, 360, application usage, web usages, cyber threat assessment, data loss prevention, high bandwidth application usage.

is it possible to include this on one single report as to send the client multiple reports on all the above.

i will appreciate your feedback on this