Report for Specific User and Destination IP Range

[Bill_K]

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

[hzhao_FTNT]

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]

Perfec Thank you ;)

[Alby23]

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