Skip to main content
Bill_K
Bill_K
New Member
October 1, 2015
Question

Report for Specific User and Destination IP Range

  • October 1, 2015
  • 2 replies
  • 11679 views

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

 

 

    2 replies

    hzhao_FTNT
    Staff
    hzhao_FTNT
    Staff
    October 1, 2015

    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
    Bill_KAuthor
    New Member
    October 2, 2015

    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
    Staff
    hzhao_FTNT
    Staff
    October 2, 2015

    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

    Alby23
    Alby23
    New Member
    July 12, 2016

    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

    Terms & ConditionsAccessibility statement