Description
Solution
This article explains and gives some SQL request examples that can be used in a report.
Solution
To create a manual SQL request that can be used in a report, a Dataset must be created:
* Go to an ADOM
Report > Advanced > Dataset
* Create a new Dataset
- Choose Device Type FortiGate
- Choose the Log Type: this is the table in which the search will be done. The table name will be stored in $log variable used below. For example, to search something specific related to traffic, 'Traffic' table must be selected.
- SQL query must be pasted in the text area and Test must be clicked.
Note that if the field to be retrieved from the selected table is unknown, the global SQL query "select * from $log" can be run.
This will show all the content of the $log table (for example $log=traffic) so that one can see the different column names and use these names to refine the SQL query
Example 1: SQL request example to check packet denied for a source or destination matching '172.30' for any specific reason:
select from_dtime(dtime) as tstamp, vd, src, dst, src_port, dst_port, src_int, dst_int, service, msg, count(*) as totalnum from $log where status='deny' AND (src LIKE '172.30%' OR dst LIKE '172.30%') group by tstamp, src, dst, src_port, dst_port, src_int, dst_int, vd, service, msg order by totalnum desc
Example 2: SQL request example to report the top 10 hosts that got most packet dropped with error message "no session matched" or "replay packet(allow_err), drop":
select from_dtime(dtime) as tstamp, vd, src, dst, src_port, dst_port, src_int, dst_int, service, msg, count(*) as totalnum from $log where (msg='no sessions matched' OR msg='replay packet(allow_err), drop') group by tstamp, src, dst, src_port, dst_port, src_int, dst_int, vd, service, msg order by totalnum desc limit 10
Example 3: SQL request to see the websites visited per user and per hour:
select coalesce(nullifna(`user`), `src`) as user_src, hostname, date_part('day',from_dtime(dtime)) as day, date_part('hour',from_dtime(dtime)) as hour from $log where $filter and status!='blocked' group by user_src, hostname, day, hour order by day desc, hour desc
These examples were run on a FortiAnalyzer 4.3, but they can easily be adapted to FortiAnalyzer 5.0. Some attributes have been renamed in FortiAnalyzer 5.0, so you should first run a "select * from $log" to check the column names.
* Go to an ADOM
Report > Advanced > Dataset
* Create a new Dataset
- Choose Device Type FortiGate
- Choose the Log Type: this is the table in which the search will be done. The table name will be stored in $log variable used below. For example, to search something specific related to traffic, 'Traffic' table must be selected.
- SQL query must be pasted in the text area and Test must be clicked.
Note that if the field to be retrieved from the selected table is unknown, the global SQL query "select * from $log" can be run.
This will show all the content of the $log table (for example $log=traffic) so that one can see the different column names and use these names to refine the SQL query
Example 1: SQL request example to check packet denied for a source or destination matching '172.30' for any specific reason:
select from_dtime(dtime) as tstamp, vd, src, dst, src_port, dst_port, src_int, dst_int, service, msg, count(*) as totalnum from $log where status='deny' AND (src LIKE '172.30%' OR dst LIKE '172.30%') group by tstamp, src, dst, src_port, dst_port, src_int, dst_int, vd, service, msg order by totalnum desc
Example 2: SQL request example to report the top 10 hosts that got most packet dropped with error message "no session matched" or "replay packet(allow_err), drop":
select from_dtime(dtime) as tstamp, vd, src, dst, src_port, dst_port, src_int, dst_int, service, msg, count(*) as totalnum from $log where (msg='no sessions matched' OR msg='replay packet(allow_err), drop') group by tstamp, src, dst, src_port, dst_port, src_int, dst_int, vd, service, msg order by totalnum desc limit 10
Example 3: SQL request to see the websites visited per user and per hour:
select coalesce(nullifna(`user`), `src`) as user_src, hostname, date_part('day',from_dtime(dtime)) as day, date_part('hour',from_dtime(dtime)) as hour from $log where $filter and status!='blocked' group by user_src, hostname, day, hour order by day desc, hour desc
These examples were run on a FortiAnalyzer 4.3, but they can easily be adapted to FortiAnalyzer 5.0. Some attributes have been renamed in FortiAnalyzer 5.0, so you should first run a "select * from $log" to check the column names.
