Single user usage report query

[markwen]

Oh hai! I' ve been tasked with a mighty quest to retrieve a report of destinations, ranked by bandwidth usage for a specific user. So far I' ve created a report with a user filter for the user in question. However, I' m unsure if there' s a relevant dataset which will give me this information (FAz 5.0.2). Does such a thing exist? Or is it custom query time? Thanks in advance, Mark.

[alexandru_serghie1]

I' m having the same problem. I' m also struggling with the 5.0 P1 that has lots of bugs and I don' t have a support account so I can download the newest firmware by myself.

[billp]

From what I understand, the FAZ 5.03 update will be a significant improvement with better user reporting. I think it' s due in June.

[AtiT]

Hi, I' m not sure if there is any relevant dataset for you - I didn' t find one, but you can create some. It depends whether you want data directly from traffic log (destionation IP) or from the webfilter log (hostname). For hostname you can use something like this: SELECT COALESCE(NULLIFNA(`user`),`srcip`) AS user_src,`hostname`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND `type`=' webfilter' AND `status`=' passthrough' AND NULLIFNA(`hostname`) IS NOT NULL GROUP BY user_src, `hostname` HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC For destination IP: SELECT COALESCE(NULLIFNA(`user`),`srcip`) AS user_src,`dstip`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND `type`=' traffic' AND NULLIFNA(`dstip`) IS NOT NULL GROUP BY user_src, `dstip` HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC After that just set a user filter in the report settings. In chart you can enable the user_src filed to check if the report is working correctly for the defined user and for the final report just delete it from the chart (not dataset). Some results are attached - see the image.

[Ebenezer]

Thanks for that post AtiT. Is there a way to display the bandwidth in MB or GB rather than in bytes when using the ranked format? If I use raw, I get the bandwidth in MB/GB but I only get one entry per user whereas in ranked I can get 5 hostnames per user.

[AtiT]

Hi Ebenezer, I' m a little bit confused or not understand what do you mean with: " I only get one entry per user whereas in ranked I can get 5 hostnames per user." Can you send some dataset/chart what you are using? I like the raw format because I can get more columns, in ranked format only 3. But I also use ranked format see the attached image below. In the dataset I set for the bandwidh bandwith/1024 for kB and bandwidh/1048576 for MB (1024*1024) etc. And in the chart I write the column title: Bandwidth [MB]. I know that it is not the best solution but I didn' t figure out how to do it automaticaly in ranked format - is it possible? My dataset: SELECT `dstcountry`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))/1048576 AS bandwidth FROM $log WHERE `status`=' accept' GROUP BY `dstcountry` HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC

[Ebenezer]

Thanks AtiT. I don' t get the bandwidth as MB/GB in raw format as well. In RAW format is it supposed to automatically show in MB/GB. Is it possible to get reports similar to the ones on Fortigate 100D where you get per user reports for top 5 users with top websites and applications?

[AtiT]

Hi Ebenezer, I have never tried to generate report directly on FTG. I didn' t find any dataset and chart configuration on FTG100D but I think it is possible via CLI: # config report dataset (dataset) # ? edit add/edit a table value delete delete a table value purge clear all table value rename rename a table entry get get dynamic and system information show show configuration end end and save last config (dataset) # edit test1 new entry ' test1' added (test1) # ? config config object set modify value unset set to default value get get dynamic and system information show show configuration next config next table entry abort end and discard last config end end and save last config (test1) # set ? query SQL query statement (test1) # set query ? <string> please input string value ...etc.. It seems that it is possible via CLI. (this is for FortiOS 4 MR3 Patch 12)