Technical Tip: Customizing reports generated by FortiGate

[tsimeonov_FTNT]

Description

This article provides basic tips about creating custom reports on a FortiGate.

Most of the steps are available only in the CLI. For more complicated custom report scenarios, Fortinet recommends the use of FortiAnalyzer.

FortiGate reports are based on data stored in the Database tables.

 

1. Table names and structure.

The next commands show ca complete list of these tables and their content:

diagnose report sql "select name from sqlite_master where type='table' order by name as below"

grp_attack
grp_cloud_app
grp_dlp
grp_email
grp_email_recipient
grp_email_sender
grp_sandbox
grp_security_sum
grp_threat
grp_traffic_all
grp_traffic_all_dst
grp_traffic_all_src
grp_traffic_app
grp_traffic_country
grp_traffic_interface
grp_traffic_summary
grp_traffic_usersrcip
grp_virus
grp_vpn
grp_web
grp_web_cat
grp_web_cat_site
grp_web_dst
grp_web_src
grp_web_stream_site

diagnose report sql "select * from <table name> limit 1" ---> Show column names and one line of data.

diagnose report sql "select * from grp_web_cat limit 1"
oid|timestamp|hostname|srcmac|srcname|devtype|unauthuser|srcintf|dstintf|appid|user|srcip|policyid|status|catdesc|crlevel|crscore|sentbyte|rcvdbyte|sessioncount|crcount
1|1426618800|sqm.telemetry.microsoft.com|00:15:5d:01:0c:52||||port2|port1||guest|192.168.1.112|5|passthrough|Information Technology||0|3153|4090|1|01 row(s) returned in 0.00 seconds.

1 row(s) returned in 0.00 seconds.

2. Dataset.

A dataset is needed in order to tell the reporting engine what information is of interest. Dataset configuration is similar to a sql query.
In the following example, the dataset collection information about the number of requests to specific websites by individual users.

config report dataset
    edit "web.allowed-user-request.websites"
        set query "select coalesce(user, unauthuser, srcip) user_src, ft_domainName(hostname) host, sum(sessioncount) requests from grp_web where  status <> \'blocked\' and hostname<>\'unknown\' group by user_src order by requests desc limit 50"
            config field
                edit 1
                    set name "user_src"
                    set displayname "user/IP"
                next
                edit 2
                    set name "host"
                    set displayname "WebSite"
                next
                edit 3
                    set type integer
                    set name "requests"
                    set displayname "Requests"
                next
            end
    next
end

The following commands provide a list and syntax of predefined datasets:

diagnose report dataset ?
...
vpn.bandwidth.users.static-tunnel
    vpn.duration.users
    web.allowed-request.sites
                                srcintf (text)
                                dstintf (text)
                                coalesce(user, unauthuser, srcip) (text)
...
diagnose report dataset <dataset name>
diagnose report dataset web.allowed-request.sites
SQL statement:
select ft_domainName(hostname) host, sum(sessioncount) requests from grp_web_cat_site where  timestamp BETWEEN 1433347200 and 1433433599  and status <> 'blocked' and hostname<>'unknown' and 1=1 group by host order by requests desc limit 10
Report start time: 1433347200, end time: 1433433599
host|requests

0 row(s) returned in 0.00 seconds.

3. Chart.

A chart is needed in order to tell the reporting engine how to present the data from the dataset.
In the following example, the chart specifies that a table be used for the dataset ('web.allowed-user-request.websites') created in the previous step.

config report chart
    edit "top.allowed-user-request.website"
        set type table
        set period last7d
        set dataset "web.allowed-user-request.websites"
        set category webfilter
    next
end

4. Report.

Currently, FortiOS supports only one report layout from the GUI. The report layout name should be 'default'. It is configurable and customizable from the GUI.

This step is easy to configure from the GUI: FortiGate_GUI\Log & Report\Report\Local\...

It could also be shown or configured from the CLI:

show report layout
config report layout
    edit "default"
        set title "FortiGate System Analysis Report"
        set style-theme "default-report"
        set options include-table-of-content view-chart-as-heading
        set format pdf
        set schedule-type demand
            config page
                set paper letter
                set page-break-before heading1
                    config header
                        config header-item
                            edit 1
                                set type image
                                set style "header-image"
                                set img-src "fortinet_logo_small.png"
                            next
                        end
                    end
                    config footer
                        config footer-item
                            edit 1
                                set style "footer-text"
                                set content "FortiGate ${schedule_type} Security Report - Host Name: ${hostname}"
                            next
                            edit 2
                                set style "footer-pageno"
                            next
                        end
                    end
            end
            config body-item
                edit 1
                    set type image
                    set style "report-cover1"
                    set img-src "fortigate_log.png"
                next
                edit 2
                    set style "report-cover2"
                    set content "FortiGate ${schedule_type} Security Report"
                next
                edit 3
                    set style "report-cover3"
                    set content "Report Date: ${started_time}"
                next
                edit 4
                    set style "report-cover3"
                    set content "Data Range: ${report_data_range}  (${hostname})"
                next
                edit 5
                    set type image
                    set style "report-cover4"
                    set img-src "fortinet_logo_small.png"
                next
                edit 6
                    set type misc
                    set misc-component page-break
                next
                edit 7
                    set type chart
                    set chart "top.allowed-user-request.website"
                next
            end
end

5. Diagnostics commands.

diagnose debug application sqldb -1
diagnose debug application reportd -1
diagnose debug enable

To run a report from the CLI:

execute report run default "2015-01-01 00" "2015-06-05 00" 

Generating report for layout: default...
Request to generate a report is successfully sent to reportd.

 # reportd_main.c[765] __handle_cron_message-Cron message. type=2, vd=root

report_engine.c[50] rptengine_create_report_dir- Report directory: /var/log/root/report/generated/On-Demand-default-2015-06-04-124537 is created successfully
report_engine.c[1188] rptengine_init_toc_list-
...
report_engine.c[1001] rptengine_handle_body-start body item
report_engine.c[907] _handle_image_item-Process image item: id=1,  chart=fortigate_log.png
report_engine.c[403] _handle_text_item-Process text item: id=2,  contents=FortiGate ${schedule_type} Security Report
report_engine.c[403] _handle_text_item-Process text item: id=3,  contents=Report Date: ${started_time}
report_engine.c[403] _handle_text_item-Process text item: id=4,  contents=Data Range: ${report_data_range}  (${hostname})
report_engine.c[907] _handle_image_item-Process image item: id=5,  chart=fortinet_logo_small.png
report_engine.c[920] _handle_misc_item-Process misc item: id=6
report_engine.c[855] _handle_chart_item-Process chart item: id=7,  chart=top.allowed-user-request.website
...
sqldb conn 11 request 3 len 234
sqldb db 70 make stmt 'select coalesce(user, unauthuser, srcip) user_src, ft_domainName(hostname) host, sum(sessioncount) requests from grp_web where  status <> 'blocked' and hostname<>'unknown' group by user_src order by requests desc limit 50'
sqldb db 70 stmt 30186 open
sqldb conn 11 write 16
sqldb conn 11 request end rc 0
....

Note:
FortiGate has limited reporting capabilities. Reporting is not a primary FortiGate function. Heavy reports or multiple reports schedules may require more device resources and may affect device performance.

FortiAnalyzer is a dedicated reporting and log analyzing product and is recommended for all but the simplest of reporting needs.