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

Report for Specific User and Destination IP Range

I'm using the following dataset and when I run the report with a filter for 'User', it will not select a single user for the date range, it returns all users who have accessed the DSTIP range:

 

[style="background-color: #ffff00;"]select   dstip,[/style] [style="background-color: #ffff00;"] `user`, srcip, [/style] [style="background-color: #ffff00;"]sum(coalesce(sentbyte,0)+coalesce(rcvdbyte,0)) AS Bandwidth, [/style] [style="background-color: #ffff00;"]sum($browse_time) as browsetime,[/style] [style="background-color: #ffff00;"]from_itime(itime) as date_time[/style] [style="background-color: #ffff00;"]from $log [/style] [style="background-color: #ffff00;"]where[/style] [style="background-color: #ffff00;"]dstip between '198.45.48.0' and '198.45.63.255'[/style] [style="background-color: #ffff00;"]group by `user`, date_time, dstip, srcip, hostname[/style] [style="background-color: #ffff00;"]order by user asc, date_time asc[/style]

 

Can anyone help me fix this so I can select a single user.

 

Thanks

 

 

6 REPLIES 6
hzhao_FTNT
Staff
Staff

Hi there, if you know the user name, please apply a user filter in Report->Advanced Settings->Add Filter. BTW, if you group by itime, you won't see aggreted bandwidth/browsetime during report period, suggest to remove it or use macro like $DAY_OF_MONTH, $DAY_OF_WEEK, $HOUR_OF_DAY... Please also add $filter into your dataset, report filter won't work without it.

 

Regards,

hz

Bill_K

HZ:  I used the "User" filter in Advanced settings and it did not work.  NOTE:  I've used the "User" filter for other reports and it worked appropriately.  My thought is that the DSTIP range is overriding the filter applied under Advanced Settings.

 

Appreciate any help you can provide

Bill

hzhao_FTNT

Hi Bill,

 

I have tested below dataset with "user" filter applied, it looks OK to me. Both user and dstip filter can be applied

select `user` as usr, srcip, dstip, sum(coalesce(sentbyte,0)+coalesce(rcvdbyte,0)) AS Bandwidth, sum($browse_time) as browsetime from $log where $filter and dstip between '198.45.48.0' and '198.45.63.255' group by usr, dstip, srcip order by usr asc

 

Regards,

hz

Bill_K

That worked perfectly - Thanks!!!

Ronildo
New Contributor

Perfec Thank you ;)

Alby23
Contributor II

You need to add the $filter to the where clause.

 

Just like this.

 

[<font]select   dstip, [<font] `user`, srcip, [<font]sum(coalesce(sentbyte,0)+coalesce(rcvdbyte,0)) AS Bandwidth, [<font]sum($browse_time) as browsetime, [<font]from_itime(itime) as date_time [<font]from $log [<font]where $filter and [<font]dstip between '198.45.48.0' and '198.45.63.255' [<font]group by `user`, date_time, dstip, srcip, hostname [<font]order by user asc, date_time asc

Labels
Top Kudoed Authors