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

Interface History Custom Report

As we all know, the status dashboard has an "Interface History" widget. I love this widget however it resets when the device is power cycled.

 

How would I go about creating a FortiAnalyzer report that would show the same data in the same manner (hour, day week) bandwidth in and out (MBps) See attached file for how it looks in the GUI. 

The FAZ is running 5.0.x code 

 

I know there is a dataset I need to make etc but I have no idea how to do that (databases and sql are not strong points of mine)interface_history.gif

Mike Pruett Fortinet GURU | Fortinet Training Videos
33 REPLIES 33
Osama

hzhao_FTNT wrote:

Unfortunately we don't have any log field to directly reflect real-time speed, and current FAZ do not allow user to create this kind of timeline chart. If you want to see max speed by time interval in table chart, you can try below dataset:

select $flex_timescale as hodex, max(coalesce(sentbyte, 0)/duration) as traffic_out_speed, max(coalesce(rcvdbyte, 0)/duration) as traffic_in_speed from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and duration!=0 group by hodex having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by hodex

 

Thanks Mr Hzhao

but here the graph represent the value every one hour, Kindly help me to be every one minute to be more closed to the real time as Fortiview in Fortigate.

 

 

hzhao_FTNT

Please change $flex_timescale to to_char(from_itime(itime), 'MM-DD HH:MI')

Osama

hzhao_FTNT wrote:

Please change $flex_timescale to to_char(from_itime(itime), 'MM-DD HH:MI')

Sorry hzhao,

 

select to_char(from_itime(itime), 'MM-DD HH:MI') as hodex, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in from ###(select $flex_timestamp as timestamp, sum(coalesce(sentbyte, 0)) as traffic_out, sum(coalesce(rcvdbyte, 0)) as traffic_in from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by timestamp having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by timestamp desc)### t group by hodex order by hodex

 

 

has an error 

ERROR: column "itime" does not exist LINE 1: select * from (select to_char(from_itime(itime), 'MM-DD HH:M...

                                                                                                                            ^

 

 

either when I tried the select * from $log there was a coloum named itime

 

thanks for your support, help me if there is another modification for above query.

abelio
Valued Contributor

Osama wrote:

hzhao_FTNT wrote:

Please change $flex_timescale to to_char(from_itime(itime), 'MM-DD HH:MI')

Sorry hzhao,

 

select to_char(from_itime(itime), 'MM-DD HH:MI') as hodex, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in from ###(select $flex_timestamp as timestamp, sum(coalesce(sentbyte, 0)) as traffic_out, sum(coalesce(rcvdbyte, 0)) as traffic_in from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by timestamp having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by timestamp desc)### t group by hodex order by hodex

 

 

has an error 

ERROR: column "itime" does not exist LINE 1: select * from (select to_char(from_itime(itime), 'MM-DD HH:M...                                                                                                                            ^  either when I tried the select * from $log there was a coloum named itime thanks for your support, help me if there is another modification for above query.

Osama

 

Try

 

select to_char(from_itime(itime), 'MM-DD HH:MI') as hodex, max(coalesce(sentbyte, 0)/duration) as traffic_out_speed, max(coalesce(rcvdbyte, 0)/duration) as traffic_in_speed from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and duration!=0 group by hodex having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by hodex

 

 

regards




/ Abel

regards / Abel
Osama

 

Please another question?

May I used the wrong report or I have a confusion

I have used the Bandwidth and Application report and i  have checked the Bandwidth summary and also the dataset

and the result on picture:FAZ bandwidth summary, but the real time interface consumption from the Fortigate as picture on below request: FG interface consumption 

 

please advice for the difference on time line

and the huge difference on bandwidth calculation 

Osama
New Contributor

this is from FG Fortiview

hzhao_FTNT

Hi there, you have to put the to_char() in inner query.

 

select timestamp as hodex, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in from ###(select to_char(from_itime(itime), 'MM-DD HH:MI')  as timestamp, sum(coalesce(sentbyte, 0)) as traffic_out, sum(coalesce(rcvdbyte, 0)) as traffic_in from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by timestamp having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by timestamp desc)### t group by hodex order by hodex

 

FAZ bandwidth summary is to calculate total bytes transferred in certain period. When you select 1 day report period, the time interval is 1 hr. So this chart is for total volume, not bandwidth rate.

FGT bandwidth chart will show bandwidth rate in kbps instead.

If you want to see bandwidth rate in FAZ, please go to FortiView->System->Resource Usage

 

regards,

hz

 

 

 

fikri
New Contributor

if you guys use FAZ version 5.4.2

 

On the resource usage you can see the bandwitdh rate. But I'm not sure the measurement in bytes /kb/mb or else.

 

refer to the image below, any guys have any opinion on this

hzhao_FTNT

The bandwidth rate here is in kbps, but it is not based on interface. If you are interested, I can provide a report for you to run similar chart in report.

 

regards,

hz

fikri

Thanks for the info. Means total interface rate ?

so you have a method to run the report base on interface base on rate?..

Top Kudoed Authors