Disappointed ... reporting etc.

[Jond]

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

[mnantel_FTNT]

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!

[scerazy]

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

[mnantel_FTNT]

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

[mnantel_FTNT]

And here is one more, because this is frequently asked. Websites - Detailed Browsing Log https://www.dropbox.com/s/lm2ijlzmgwtrzst/Websites%20-%20Detailed%20Browsing%20Log.dat This is a full detailed log, ordered by timestamp, of every site a user visits.

[michellem812]

Thank you! This is great! This is good (websites- Detailed Browsing Log) but is there a way to add the full url to this report? It' s coming from the traffic filter, but if I can see the url then it helps to see if the person actually typed in a url or just clicked.

[mnantel_FTNT]

Hey Michelle, Did you try FAZ 5.0.7? Please be sure to backup your logs and your custom reports however before your upgrade, just in case. There may be a forced DB rebuild once you upgrade. FAZ 5.0.7 has a really exciting new feature called FortiView, which is a nice interactive drill-down which complements past tools nicely. There are many more improvements coming to FortiView in upcoming releases that I cant disclose here. Your request is easy enough to fulfill, but can you look at the existing native 5.0.7 reports? A lot of reports were added (most of them came from me). You might just find what you are looking for in those. If you dont, by all means reach out to me again and I will be happy to get something done for you.

[scerazy]

@mnantel Thank you very much, extremely appreciated! But would it be possible to have also timecode fopr the Websites - Top 500 visited by Users (Hits) Also is there any more stored (so it could be presented) beyond just domain So it is not just 123rf.com but http://www.123rf.com/photos-images/56/1/weddings_and_matrimony.html Seb

[mnantel_FTNT]

scerazy, If I add timestamp to the Top 500 report, it will no longer be an aggregated report and instead will be a full list of individual connections. That report currently aggregates all entries that have similar user,source,website,category. Did you look at the report I just added titled Detailed Browsing Log? It might be what you are looking for?

[scerazy]

Top 500 Websites visited by Users (Session) No matching log data for this report Report Filters Filter name User include (joe doe) What is going on? How can I see what is happening NOW (currently live) with internet web filtering Seb

[mnantel_FTNT]

scerazy, The report uses the traffic log. Can you ensure that the traffic log is displaying hostnames (add the " hostname" column). Another test is to ensure that the report works without any filters. Reports are not ran on live data. If you want live data, this is what you will see in the realtime log viewer. Go to the log view, right top corner menu has a realtime option. Put a filter for hostname=*, and this should display live web filtering data.