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.
Solved! Go to Solution.
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
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
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
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
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
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 ?
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
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.
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.
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. ?
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
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 ?
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
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
User | Count |
---|---|
1679 | |
1085 | |
752 | |
446 | |
226 |
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2024 Fortinet, Inc. All Rights Reserved.