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

Analyzer Subnetting Error.

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.

3 Solutions
hzhao_FTNT
Staff
Staff

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

View solution in original post

hzhao_FTNT

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

View solution in original post

hzhao_FTNT

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

View solution in original post

13 REPLIES 13
hzhao_FTNT
Staff
Staff

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

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

Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors