Why does the Browse Time is null for some applications in FortiAnalyzer?

[NadaY]

I'm using this dataset to retrieve browsing time per application, in which I need to know exactly the browsing time per certain application, I noticed that for some applications the browsing time is zero, however they are used for a period of time and the traffic is more than zero! note that for majority of them they are interanlly used applications to communciate wioth internal servers (SSH, LDAP, SAMBA, ...). what do I  need to edit the query to retrieve the browsing time?

====================================================================================

SELECT app, ebtr_value(ebtr_agg_flat(browsetime), NULL, $timespan) AS browsetime, sum(bandwidth) AS bandwidth, sum(traffic_in) AS traffic_in, sum(traffic_out) AS traffic_out FROM ###( SELECT app, ebtr_agg_flat(browsetime) AS browsetime, sum(bandwidth) AS bandwidth, sum(traffic_in) AS traffic_in, sum(traffic_out) AS traffic_out FROM ( SELECT app, ebtr_agg_flat($browse_time) AS browsetime, sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS bandwidth, sum(COALESCE(rcvdbyte, 0)) AS traffic_in, sum(COALESCE(sentbyte, 0)) AS traffic_out FROM $log-traffic WHERE $filter AND (logflag & 1 > 0) AND app IS NOT NULL GROUP BY app ) t GROUP BY app /*SkipSTART*/ ORDER BY ebtr_value(ebtr_agg_flat(browsetime), NULL, NULL) DESC /*SkipEND*/ )### t GROUP BY app ORDER BY browsetime DESC 

===========================================================================

[srajeswaran]

Browsing time is calculated as a sum of ebtime , could you please download the raw log for the specific application and check if there is non-zero ebtime value? If the value is zero, then it is not a fortianalyzer query issue. If the values are non-zero we can try modifying the query.

https://community.fortinet.com/t5/FortiAnalyzer/Technical-Note-Calculating-the-browse-time-for-a-spe...

[NadaY]

Thank you for the quick responce, I download the log for that specific application (SQL-NAVIGATOR) and found that ebtime is unavailable at all in the report (even with zero value), while I download for another application (Microsoft.Outlook.Office.365) in which ebtime is available and have value. see attached screenshot. I wonder if we can modify the query to calculate ebtime as duration is available in raw data.

[srajeswaran]

I think your query is correct, thats why the browsing time is appearing for the applications with valid ebtime. If the ebtime value is not present, it is taken as zero.

Please correct me if I misunderstood your question.

[NadaY]

Thank you but I need to have the browsing time for these applications in my report, what do I need to do to update ebtime based on duration value?

[NadaY]

I used the below query to get the duration per application but the results are not grouped ny application, can you help in this?

==============================================================

SELECT from_dtime(dtime) AS timestamp, user_src, website, catdesc, CAST(sum(dura) / 60 AS DECIMAL(18, 2)) AS dura, sum(bandwidth) AS bandwidth, app FROM ###( SELECT dtime, COALESCE( nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`) ) AS user_src, hostname AS website, catdesc, sum(COALESCE(duration, 0)) AS dura, sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS bandwidth, app FROM $log-traffic WHERE $filter AND (logflag & 1 > 0) AND ACTION IN ('accept', 'close', 'timeout') GROUP BY dtime, user_src, website, catdesc, app HAVING sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) > 0 ORDER BY bandwidth DESC )### t GROUP BY dtime, user_src, website, catdesc, app ORDER BY dura DESC