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

[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

[sarathd24]

Hi hz,

Thank you so much for your reply. I really appreciate it. Yes you were right it seems that ip_subnet function doesnt support /22 subnets as of now.But let me explain what were trying here. I need to get the top 10 users per subnet and we have multiple subnets. As per your suggestions when we give 10.22.64.0 / 22 it is just displaying 10.22.64.0 and showing all information of all my subnets. 

Is there any way where I can generate a report like:

For 10.22.64.0/22 - The top ten user list

for 10.22.68.0/22 - The top ten user list  followed by summary of that subnet

likewise for all the /22 subnets I have. ?

'Subnet Top User by Bandwidth' is the pre configured report but it showing only /24 mask. Would be great if I could some how manipulate it to give me /22. Is there any solution for this or Am I stuck ?

[hzhao_FTNT]

Hi there.

I think the easiest way is create a report and insert predefined chart "Top Users by Bandwidth" couple of times, then add different subnet filter to each chart. To add a chart filter, just double click the chart in layout edit and add a filter "srcip equal to 10.22.64.0/22".

Regards,

hz

[sarathd24]

Hi hz,

Thank you so much for your tip. Did as you advised. I am filtering the chart and getting an output. It is almost correct, because the problem here is instead of giving me one /22 result it is give 4 * /24 networks as result. Though out put is logically correct, I am not getting what what the customer is asking for. Is there any way where I could combine/consolidate the 4 * /24 into 1 and get an report. ?

Like in the chart. It would select one particular network which we specify and give us the top user by bandwidth and in the end it also give us an summary. In our scenario it is giving us 4 * /24  top users and summary for each. Is there any way we could combine the 4 and get one big table and a summary in the end. ?

P.S: Thanks again for your support so far. I really appreciate it.

[hzhao_FTNT]

You can put 4 charts into one table when you apply filter to srcip equal to 4subnets.

For example srcip equal to 10.22.64.0/22 10.22.65.0/22 10.22.66.0/22 10.22.67.0/22 will add filter  (("srcip" <<= inet('10.22.64.0/22') OR "srcip" <<= inet('10.22.65.0/22') OR "srcip" <<= inet('10.22.66.0/22') OR "srcip" <<= inet('10.22.67.0/22')) into sql query.

Not sure what kind of summary you are looking for, but probably you will need a separate table for it.

[sarathd24]

Hi Hz,

Guess I was not very clear last time hence I have attached a picture with this post, In which the first part is the report which I got after following the suggestions you gave and the second part is the kind of report I want.

What I have done in the report is just added the filter 10.22.0.0/22 - and report came out 4 * /24 subnets. Is there any way I could combine them. ?

[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,

That worked great ! Thank you so much. :)

One last query is there any way were I could add another data point to that report ?

I need to the add the total number of active user at that particular time. Is there a way to do that ?

[hzhao_FTNT]

Hi there, please try our predefined chart "Active Traffic Users" and apply srcip filter to it. This is an "Area" chart, if you prefer table chart, just clone it and change chart type to table.

regards,

hz