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

Help build report - new to SQL logging

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
45 REPLIES 45
SteveRoadWarrior
New Contributor III

I' ve been seeing complaints in this forum for a while about the Fortianalyzer 5 code. You will want to test thoroughly under version 5 before " going live" . The directions I' m going to provide are for SQL logging under 4.3 Under your Indexer reports schedule area there was a place to define " user" just before you ran the report for scheduling. You used to be able to choose when the report would run, what FG would be used for data, etc. You could enter an IP there. Or you could enter a username there, if you are running the FSSO agent on your AD server, which I strongly suggest. Under the SQL reports, a similar menu can be had by right clicking the report and choosing the option to edit the reports. Then there' s a Report Filters ->more filters area. Enter the IP in the username field. You can make one report with lots of extra info and run it on-demand or make up a report for one user and have it scheduled. To create a new report, right click on the left side and make a new report. Then edit the report and start adding charts by doubleclicking the bar, pie, or chart objects on the right side of the screen. A window will come up and you can select the category of information you want. Then select an individual chart you wish to add. You can multiselect, but it' s not terribly smart. Click ok when done. This will add the charts to the bottom of the report, be aware. The only way I' ve found to reorder them is to delete them and start over. If you' re having issues with this, you may want to try using sections to break up the report into zones which are easier to manage. Be sure to click save and then exit the edit mode by clicking " view mode" in the upper left corner of the edit window. If your data comes back with " No Data" there are a few possible causes. 1. your UTM logs may not display in Fortianalyzer 5 (known bug), 2. the SQL hdata cache needs to be bypassed (there' s kb articles on how to bypass it but it' s ugly) or 3. or the most obvious reason: the Fortigate isn' t logging that type of data and not sending it to the FA. If you found this post useful, please rate it accordingly. My score is recently low.
RH2
New Contributor II

Thanks Steve, I already know how to " Build" reports with what is already provided. The problem I have with the SQL reports is there is no pre-built chart or dataset that will return all of a users traffic over a time period. There are plenty of " Top" and " Top 10" reports and datasets available but nothing that provides a way to run a report on a specific user or ip address. I' m asking for help building the dataset so I can run it with a filter on a specific ip address or username. FYI, I created a test report and included every available chart and nothing is returned that addresses my needs. I also tried using the built in reports on the fortigate but it does not include any charts or datasets in 5.0 that fit either.
SteveRoadWarrior
New Contributor III

I understand your pain. I' ve given up and am going back to Rev 4 MR3 patch 0 or patch 1 with SQL turned off, I' m testing this still. Someone else whose opinion matters has determined that the " estimated browse time" chart is critical, and I understand their reasons.
RafalS
New Contributor

Rick: In my FAZ which is 400B running 5.0.1. both filters are available as customizable parameters to Report Schedules, respectively:
How do I set up a filter for one specific ip?
Edit Schedule -> Advanced Settings -> Enable Filters -> Source.
We need to be able to run a report on a srcip to capture all of their webtraffic for the past 30-60 days
Edit Schedule -> Time Period so theoretically, SQL queries seem evitable in this case. However, in my case, both filters are error-prone, namely: A. Report Schedules >> Edit Schedule >> Time period >> Other B. Report Schedules >> Edit Schedule >> Advanced Settings >> Filter >> Source >> range or prefix producing unexpected results, which is being discussed with TAC. Cheers! Rafal
FCNSP 4.x running FortiOS 5.0.4 on FG621B A-A HA
FCNSP 4.x running FortiOS 5.0.4 on FG621B A-A HA
Rafael_Rosseto
New Contributor

Hi Guys, I tried to apply source filter, and the report ignore the source IP. I tried to create a new sql query but I do not know postgress at all. So for example, how do I filter a source IP address in that query: select $DAY_OF_MONTH as dom, sum(sentbyte) as traffic_out, sum(rcvdbyte) as traffic_in from $log where $filter group by dom having sum(sentbyte+rcvdbyte)>0 order by dom Any help I appreciate. Cheers
RH2
New Contributor II

Thanks everyone, I updated to patch 5.0.2 but I' m still not getting what I need. Why does fortinet think we are only interested in the top 10 of anything? I just need to be able to provide our personnel dept with a report on a users web activity for a specific time period.
billp
Contributor

I tried to apply source filter, and the report ignore the source IP. I tried to create a new sql query but I do not know postgress at all. So for example, how do I filter a source IP address in that query: select $DAY_OF_MONTH as dom, sum(sentbyte) as traffic_out, sum(rcvdbyte) as traffic_in from $log where $filter group by dom having sum(sentbyte+rcvdbyte)>0 order by dom
Can you add the ' src' field to the select statement and change the order by clause? For example,
select src, $DAY_OF_MONTH as dom, sum(sentbyte) as traffic_out, sum(rcvdbyte) as traffic_in from $log where $filter group by dom having sum(sentbyte+rcvdbyte)>0 order by src
Once you get that working, you should be able to narrow it down to specific IP addresses as needed when you run the report. I' m not using FAZ 5.0.x, but I am guessing the basic SQL syntax should be the same as in 4.3.x.

Bill ========== Fortigate 600C 5.0.12, 111C 5.0.2 Logstash 1.4.1

Bill ========== Fortigate 600C 5.0.12, 111C 5.0.2 Logstash 1.4.1
billp
Contributor

Thanks everyone, I updated to patch 5.0.2 but I' m still not getting what I need. Why does fortinet think we are only interested in the top 10 of anything? I just need to be able to provide our personnel dept with a report on a users web activity for a specific time period.
+1 One of my biggest frustrations with the Fortinet line-up is the lack of useful reporting for my purposes. The SQL solution is nice in that it lets us roll our own reports now, but a basic report showing a user' s history, apps, etc. over a specific time period would be really useful. It would be great to hear from the FAZ product manager about where this product is going.

Bill ========== Fortigate 600C 5.0.12, 111C 5.0.2 Logstash 1.4.1

Bill ========== Fortigate 600C 5.0.12, 111C 5.0.2 Logstash 1.4.1
emorillo
New Contributor

How do you filter an IP Range? I' ve tried every way I can think of: 1.1.1.0 - 1.1.2.255 (1.1.1.0 - 1.1.2.255) 1.1.1.0-1.1.2.255 1.1.1.0/24 1.1.1.0 255.255.255.0 ???
Labels
Top Kudoed Authors