Analyzer Subnetting Error.

[sarathd24]

Hello,

I am basically trying to create a report which will have the networks displayed in one column and beside that the values of who the maximum band width user is , the maximum session an so on will be there.

The network which I am working on is a /22 network for which I am editing the data set so that I get reporting based on /22 subnets ,

select ip_subnet(`srcip`) as subnet, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out, count(*) as sessions from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and ( ( `srcip` <<=inet('10.22.64.0/22') OR `srcname` = '10.22.64.0/22'))  group by subnet having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc

The Analyzer instead of taking the /22 subnet mask it is taking as /24 and giving me a report which obviously is not the one what I want. Would be great if someone could point out were I am going on. I have attached the output as well do have a look.

[hzhao_FTNT · Accepted solution]

Hi there, currently the function ip_subnet only support subnet 0/8/16/24/32, please do not use it. In your case, you can use a fixed string to make it work.

select '10.22.64.0/22' as subnet,  sum(coalesce(sent........

regards,

hz

[hzhao_FTNT · Accepted solution]

Again, maybe you need a fixed string for it. try:

select  (case when `srcip` <<= inet('10.22.0.0/22')  then '10.22.0.0/22' else 'Others' end) as subnet, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(`sentbyte`, 0)+coalesce(`rcvdbyte`, 0)) as bandwidth from $log where $filter  and logid_to_int(logid) not in (4, 7, 14) and `srcip` <<= inet('10.22.0.0/22') group by subnet, user_src order by bandwidth desc

[sarathd24]

Hi hz,

I tried the "Active traffic users" charts but it was overwhelming me with informations. I found another chart "Bandwidth- app -trafffic-statistics" this has the thing which I want. From that chart I am trying to extract the total number of users and add it to the bandwidth top utilization chart but every time I try to editing the data set I am getting script error. I have attached an image of what I am trying to do, Please see if you can help me with this as well.

[hzhao_FTNT · Accepted solution]

So you need distinct user numbers?

try:

select subnet, count(distinct user_src), sum(bandwidth) as bandwidth from ###(select (case when `srcip` <<= inet('10.22.0.0/22')  then '10.22.0.0/22' else 'Others' end) as subnet, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(`sentbyte`, 0)+coalesce(`rcvdbyte`, 0)) as bandwidth from $log where $filter  and logid_to_int(logid) not in (4, 7, 14) and `srcip` <<= inet('10.22.0.0/22') group by subnet, user_src order by bandwidth desc)### t group by subnet order by bandwidth desc

[sarathd24]

Yes I need distinct users, but I want that to be added to "Top Users by bandwidth" data set. You helped me to edit the dataset, it is working great but I need an additional information along with with the summary it is providing me.

The " top users by  bandwidth" is giving me top 10 subnet based bandwidth utilization along with the summary of total bandwith used. I just need to add this " Total no of user " to the "Top users by bandwidth". Is it possible or should I just create a separate chart for the top users and import that chart in the report which I created for the top bandwidth users ?

[hzhao_FTNT]

It is not possible to put them into one chart, please create a separate chart for it.