Build custom SQL report and dataset

[RH2]

Ok everyone, I' ve searched and can' t find any useful examples or documenatation on how to build a dataset and report for all webtraffic for a specific user or ip address. This is for fortigate/fortianalyzer version 5. We have been using indexer based logs with v4 mr3 and we are preparing to replace all of our firewalls with new 100Ds and we want to roll out version 5 at the same time, but v5 only uses SQL not indexer logs. The problem is we occasionally need to run a " forensic" report on a specific ip address showing all non-referral web traffic and I cannot find any prebuilt dataset or chart that will provide the right information. There is plenty for the " top users" and " top web traffic" etc, but nothing that fits my needs and I am unsure how to build a dataset that is going to return what I need. Can anyone provide an example sql statement I can use to build the dataset I need? Thanks.

[billp]

RH, I just went through a similar process switching to SQL reports. I am not using FortiAnalyzer 5, though. The word on the street is that FAZ 5.0 is not ready for prime time yet. YMMV. My recommendation would be to take a look at the FAZ manual under SQL Log Databases that lists the available fields. Then take a look at some of the existing predefined SQL datasets. Copy and play around to see if you can get close to what you want. A dataset like the one below would probably give you results similar to what you are asking for using a logtype of Webfilter in the dataset:

select src, user, hostname, url, req_type, FROM_UNIXTIME(itime) as Logtime from $log where $filter and req_type=direct

I am using an external MySQL server. If you are using the internal Postresql server in the FAZ, then I believe you want to change the from_unixtime to to_timestamp. The " Test" button in the Dataset area is very useful for debugging SQL statements like this to get them right. Once you get the dataset down, the Charting part is relatively simple. You create a new chart with the Data Set and a Graph Type of Table. In the Data Bindings section, display data in Raw form. Click " Add Column" to add the additional columns to your report. Deselect the option in the first column that says Only Show First 3 Items. Then just add the Chart to a custom report via drag and drop. Hope that helps.

[Rick_H]

The word on the street is that FAZ 5.0 is not ready for prime time yet.

As someone who walked down that street I can confirm the word. We upgraded our FAZ to 5 in hopes of better reporting, but ran into bugs specifically related to UTM and the webfilter logs (it accepts them but won' t run reports against them). Other' s mileage may vary as you say, of course, but our FAZ100C was basically non-functional on 5.

[RH2]

Thanks, I' ll give it a try this week!

[RH2]

Yeah, we would stay with v4 but the boss wants the new 100D on version 5 so we have to update the manager and analyzer to 5 too. Unfortunately, I' m not getting this to work. and of course, there is no " test" button for the sql statement to even see if I' m creating it right. I' d be happy if I could get it on the fortigate instead of the analyzer, but the built in reports don' t have anyway of creating a new report, you can only modify the default one.