Help build report - new to SQL logging

[RH2]

So we are upgrading all of our fortigates to 100D and our fortianalyzer is new too. We want to move to version 5 and we have been using indexer reports. The new OS only uses SQL reporting but the analyzer only has one sample report template. We need to be able to run a report on a srcip to capture all of their webtraffic for the past 30-60 days. With an indexer report we just created a filter to match the users ip address and the traffic was not " referral" to get it. How do I set up a filter for one specific ip? How do I create a report template to capture all of that ip addresses traffic? Thanks! Rick

[RH2]

OK, I wasn' t getting any results in the dataset until I specified a fortigate instead of " All Fortigates" Once I did that I was getting an error on the ' I copied and pasted your text above and the apostrophe was not the same. I replaced all of the " single quotes" with new " single quotes" or the apostrophe and now the test returns: ERROR: non-integer constant in GROUP BY LINE 6: GROUP BY hod, ' user' , ' srcip' , ' hostname' , ' url' ^

[AtiT]

Hi, do not use single quotes ' (ALT+39) but `(ALT+96) at filed names like: `field`=' value' The single quotes are used for the value like: `status`=' passthrough' field = value

[RH2]

here' s the latest: SELECT $HOUR_OF_DAY AS hod, ' user' , ' srcip' , ' hostname' , ' url' , COUNT(*) AS totalnum FROM $log WHERE $filter AND `type`=' webfilter' AND `status`=' passthrough' GROUP BY hod, `user`, `srcip`, `hostname`, `url` ORDER BY hod ASC, totalnum DESC No more error, but it still returns no data

[AtiT]

The same has to be done on the first line, there are also field names from SQL table: SELECT $HOUR_OF_DAY AS hod, `user`, `srcip`, `hostname`, `url`, COUNT(*) AS totalnum

[RH2]

Still " No data" SELECT $HOUR_OF_DAY AS hod, `user`, `srcip`, `hostname`, `url`, COUNT(*) AS totalnum FROM $log WHERE $filter AND `type`=' webfilter' AND `status`=' passthrough' GROUP BY hod, `user`, `srcip`, `hostname`, `url` ORDER BY hod ASC, totalnum DESC

[AtiT]

Hi, I don' t know where is the problem, it' s working for me tested on FortiAnalyzer VM64 v5.0.2 and FAZ100C 5.0.3. Do you have logs in the Webfilter log? Try the simple dataset: SELECT * from $log LIMIT 10

[RH2]

Yes, I have plenty of webfilter logs and I can run the standard web filter reports.

[ruanbatista]

Hi Guys, I' m starting the Fortianalyzer management and have a question about to SQL Log. How to know the Database fields, tables, etc. There are documentation about this?

[AtiT]

Hi, the way how I search for the fileds: Choos the log type like Webfilter etc. and then write the dataset: SELECT * FROM $log LIMIT 5 Then run the dataset. The firts row will be the filed names and the other the data.

[RH2]

There is also a command you can run on the fortigate that will display every log field. I don' t remember what it is though.