Disappointed ... reporting etc.

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

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.

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.

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!

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

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.

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

I can certainly help with that! Let me clarify that referral link tracking is not 100% accurate and requires reporting on extended web filter logs, rather than the unified traffic log. Now, about your requirements. scerazy: top 50 users in the last 24 hours is one, first chart. do you want the top 50 users in terms of bandwidth consumed, session count, or perhaps block rate? or all of those as 3 separate subsequent charts? Then you want blocked and allowed. We have 2 engines that probably concern you here: app control and web filter. What would you like those charts to look like? I can build anything really, but I am trying to find out what piece of data you would like. For instance: Chart 1: Allowed Websites by Bandwidth Chart 2: Allowed Websites by Hits Chart 3: Blocked Websites by Hits Then we repeat the same story for applications. When you filter this report by user, it will show that user' s top resource consumption. Without a user selected, we will show global data. I want to make sure we differentiate this from an investigative report which would include timestamps. When we do include timestamps, we will get one entry for each hit which results in very long datasets. I can definitely include this in the report, but it will have limited value when you do not filter the report (that is, if we are not hit with a bug). I' ll work on something for you, but if you have any precisions to offer by all means please do!

Web filter is the only bit I am interested in ANY accessed websites (so bandwith is not of interest) I actually need username showing & what they actually accessed That is such a simple requirement. My previous solution could produce in few seconds processed log from last day showing user ---> site accessed --> time accessed --> data downloaded and I could select top x user & also top y sites So I could next day produce (school so it can be helpful) report for yesterday - top 20 students yesterday accessed these top 50 sites and I could easily select a single name (jdoe) and produce report: - jdoe --> yesterday --> ALL sites accessed (of course blocked would show 0kb) Clean and easy Thanks Seb

scerazy, I am not debating the simplicity of the requirement - I' m trying to get this right for you. Here' s a number of reports I have created that pertain to web filtering. Can you try them and use them as a starting point? They are 5.2 beta reports but I suspect they will work on 5.0.6. Websites - Top 500 visited by Users (Hits): https://www.dropbox.com/s/ns878e5iwy438c5/Websites%20-%20Top%20500%20visited%20by%20Users%20%28Hits%29.dat Websites - Top 500 visited by Users (Bandwidth) https://www.dropbox.com/s/uketybfb1bw991h/Websites%20-%20Top%20500%20visited%20by%20Users%20%28Bandwidth%29.dat Websites - Top 500 Sessions by Bandwidth https://www.dropbox.com/s/a2tzudy5xejunex/Websites%20-%20Top%20500%20Sessions%20by%20Bandwidth.dat Websites - Top 20 Category and Websites (Hits) https://www.dropbox.com/s/rmjav5injixxfsv/Websites%20-%20Top%2020%20Category%20and%20Websites%20%28Hits%29.dat Websites - Top 20 Category and Websites (Bandwidth) https://www.dropbox.com/s/vgq43ccu86wif55/Websites%20-%20Top%2020%20Category%20and%20Websites%20%28Bandwidth%29.dat Websites - Hourly Website Hits https://www.dropbox.com/s/4iro2kciwsdy5lw/Websites%20-%20Hourly%20Website%20Hits-2.dat Please let me know if any of them are useful for you! Mat