Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
Jond
New Contributor III

Disappointed ... reporting etc.

Hi all, I' m finding my new shiny Fortianalyser rather impenetrable. The default reports are worse than useless and I find myself really rather disappointed compared to a standard old syslog server! I wonder whether some of the knowledgeable people here could answer a few questions? - is it possible to just run SQL queries directly and receive the output ? (or does it have to be integrated into a chart/report etc.) - is there a guide to using SQL on the Fortianalyzer somewhere? - is there a schema somewhere to know what columns I might even use? Sort of questions I want to answer are... - which user accessed a specific/host/ip address and when - what traffic is being exchanged between specific ip addresses etc. etc. I' m sure there will be more :-) Thanks, Jon
24 REPLIES 24
morsnoctus
New Contributor

Yes you can query the SQL Database from the command line. The distructions for those steps are in the administration guide. If you pull up the GUI and look in the logs you will see the table columns listed out for the various items
Will code for cookies
Will code for cookies
AtiT
Valued Contributor

Hi Jond, 1) yes you can run the dataset directly. Go to Reports -> Device or ADOM -> Advaced -> Dataset then double click on the dataset and click on the Test button. 2) a good starting point how to write datasets (the basics) is: http://docs.fortinet.com/uploaded/files/1177/fortianalyzer-fortigate-sql-technote-40-mr2.pdf See the Appendix D: Querying FortiAnalyzer SQL log databases - this is an old version (4.2) but quering the database is the same. 3) Read the document on http://docs.fortinet.com/d/log-message-reference There are the tables and columns you can use and you can compare the diferences between the version 4.3 and 5. In some earlier post I wrote how I check the available colums. Let the analyer to show everything like: SELECT * FROM $log LIMIT 10 The first row (header) is the field names you can use.

AtiT

AtiT
Frosty
Contributor

Hi Jon, I have to agree with you re: the " out of the box" experience with my 100C unit. Default reports are indeed close to useless, other than as examples for building your own. But I found the SQL stuff also very difficult to get to grips with. The syntax requirements were not obvious, at least, not to me. Back-ticks, quotes, double-quotes, etc. Very picky. What would be WONDERFUL, if any Fortinet staff are reading this, would be to implement a community REPORTS LIBRARY. That way people would write reports, then could publish them to a Fortinet library for others to benefit. Then provide an option in the GUI to browse reports and download them to use. As things currently stand, I would not recommend a Fortianalyzer to anyone. Regards, Steve.
RH2
New Contributor II

ORIGINAL: Stephen Frost Hi Jon, I have to agree with you re: the " out of the box" experience with my 100C unit. Default reports are indeed close to useless, other than as examples for building your own. But I found the SQL stuff also very difficult to get to grips with. The syntax requirements were not obvious, at least, not to me. Back-ticks, quotes, double-quotes, etc. Very picky. What would be WONDERFUL, if any Fortinet staff are reading this, would be to implement a community REPORTS LIBRARY. That way people would write reports, then could publish them to a Fortinet library for others to benefit. Then provide an option in the GUI to browse reports and download them to use. As things currently stand, I would not recommend a Fortianalyzer to anyone. Regards, Steve.
My thoughts exactly! The logging is more responsive in 5.0.6 but the reporting is still useless.
trubble
New Contributor

RE: Disappointed ... reporting etc. (in reply to Jond)  Hi Jon, I have to agree with you re: the " out of the box" experience with my 100C unit. Default reports are indeed close to useless, other than as examples for building your own. But I found the SQL stuff also very difficult to get to grips with. The syntax requirements were not obvious, at least, not to me. Back-ticks, quotes, double-quotes, etc. Very picky. What would be WONDERFUL, if any Fortinet staff are reading this, would be to implement a community REPORTS LIBRARY. That way people would write reports, then could publish them to a Fortinet library for others to benefit. Then provide an option in the GUI to browse reports and download them to use. As things currently stand, I would not recommend a Fortianalyzer to anyone. Regards, Steve.
Seconded - the entire post!
mnantel_FTNT
Staff
Staff

Greetings, I would encourage that you give FAZ 5.0.6 a try. We' ve improved many of the base reports quite a bit, in addition to really improving the log view and the event management. Mat

-- Mathieu Nantel Systems Engineer / Conseiller Technique - Fortinet Montreal, QC

RH2
New Contributor II

MNANTEL, How about creating a basic forensic report by ip or username? Our Legal/HR department wants to know what user A was doing on the internet for the last 60 days. That' s it! where they went, by date, without the referral links added. I have one someone else created on this board and it works ok, except for the current report bug in 5.0.6 that limits the report to 1500 lines!!!!!
seadave
Contributor III

I have a 100C and I agree the response time and getting good data out of it can be a pain. First off use Firefox, it runs super smooth compared to the other browsers. Make sure you are on 4 MR3 P8 if your Fortigates are on 4.0 MR3. Fortigates should be on 4.0 MR3 P15 unless you are already on 5.0.X. I' m beta testing 5.2 now. If you are a command line wizard, the FAZ GUI will drive you nuts, but it does work. You just have to get used to making a selection and waiting 10 or so seconds. I find that under Log Access, UTM Log/Traffic Log are the most useful. You can click on the filter icons for the various fields to search for criteria that you define. If you have FSSO enabled on your DCs you can search by username, etc. Half the time I do this and then export to Excel for analysis. One annoying thing about that is the Excel output is essential raw. Every data value is <field id>= value. It would be so nice if the CSV output was with filed ids as headers followed by values only. I always have to run a find/replace for *= and then =* to clean up the data for review. Under Archive Access, IPS Packet gives you good info related to IPS attacks that have been blocked. You need to enable packet logging in your FG for this to work. Also Web from the Archive Access gives you more detail regarding specific pages that people are visiting. Another piece of advice is to create rules for traffic that you don' t want to log such as DNS and set it not to log. That will help filter out the chatter in your logs. Of course at times you do want to see this so you can enable logging when you want that. It isn' t the best device but it is better than nothing. Ideally I' d like to get a Splunk box. But that also has a fairly steep learning curve. Allowing people to put SSDs in FAZ100Cs would make a world of a difference, but I do understand why that isn' t done.
scerazy
New Contributor III

Same here, all I really need is a simple report where I can get all ie 50 top users in last 24 hours & what they tried to access (either allowed or banned) And if I need a single user I just add LDAP filter to it. Is that too much to ask? Seb
Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors