New dataset examples
Top 100 applications by bandwidth:
1. Go to Report > Advanced > Dataset.
2. Select Create New to create a new dataset and enter a name for the dataset.
3. Select FortiGate from the dev-type drop-down list
4. Select Traffic Log from the log-type drop-down list.
5. In the SQL Query field, enter the following:
SELECT (
TIMESTAMP - TIMESTAMP %3600
) AS hourstamp, app, service, SUM( sent + rcvd ) AS volume
FROM $log
GROUP BY app
ORDER BY volume DESC
LIMIT 100
6. Select OK to create the dataset.
Notes:
• SUM(sent + rcvd) AS volume - this calculates the total sent and received bytes.
• ORDER BY volume DESC - this orders the results by descending volume (largest volume first)
• LIMIT 100 - this lists only the top 100 applications.
Top 10 attacks:
1. Go to Report > Advanced > Dataset.
2. Select Create New to create a new dataset and enter a name for the dataset.
3. Select FortiGate from the dev-type drop-down list
4. Select Attack from the log-type drop-down list.
5. In the SQL Query field, enter the following:
SELECT attack_id, COUNT( * ) AS totalnum
FROM $log
and attack_id IS NOT NULL
GROUP BY attack_id
ORDER BY totalnum DESC
LIMIT 10
6. Select OK to create the dataset.
Notes:
• The result is ordered by the total attack number of the same attack_id. The most frequent attack_id will appear first.
Top WAN optimization applications
1. Go to Report > Advanced > Dataset.
2. Select Create New to create a new dataset and enter a name for the dataset.
3. Select FortiGate from the dev-type drop-down list
4. Select Traffic Log from the log-type drop-down list.
5. In the SQL Query field, enter the following:
SELECT wanopt_app_type, SUM( wan_in + wan_out ) AS bandwidth
FROM $log
AND subtype = 'wanopt-traffic'
GROUP BY wanopt_app_type
ORDER BY SUM( wan_in + wan_out ) DESC
LIMIT 5
6. Select OK to create the dataset.
Notes:
• The WAN optimizer module will log each application bandwidth. All bandwidth data is logged in traffic logs and wan opt data will have the subtype ‘wanopt-traffic’
• SUM(wan_in + wan_out) AS bandwidth - this calculates the total in and out traffic.