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 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.
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
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 ?
It is not possible to put them into one chart, please create a separate chart for it.
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 |
---|---|
1688 | |
1087 | |
752 | |
446 | |
227 |
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.