Hi there,
My organization just recent purchased and implemented a FortiAnalyzer-200F. I've been struggling a bit to customize reports to our liking. For example, I do like the pre-defined chart for "High Risk Applications Crossing the Network" which displays the top 20 high risk apps.
As a college campus, it would be helpful to break this data down into students and non-students (employees, staff, etc...)
The SQL query for the standard dataset the chart pulls from is as follows:
select risk as d_risk, count(distinct user_src) as users, id, name, app_cat, technology, sum(bandwidth) as bandwidth, sum(sessions) as sessions from ###(select lower(app) as lowapp, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, count(*) as sessions from $log where $filter and (logflag&1>0) group by lowapp, user_src order by bandwidth desc)### t1 inner join app_mdata t2 on t1.lowapp=lower(t2.name) where risk>='4' group by id, name, app_cat, technology, risk order by d_risk desc, sessions desc
I tried to customize this into two separate datasets by adding the following code --
[size="2"]select risk as d_risk, count(distinct user_src) as users, id, name, app_cat, technology, sum(bandwidth) as bandwidth, sum(sessions) as sessions from ###(select lower(app) as lowapp, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, count(*) as sessions from $log where srcip NOT between '[Beginning of IP range]' and '[End of IP range]' $filter and (logflag&1>0) group by lowapp, user_src order by bandwidth desc)### t1 inner join app_mdata t2 on t1.lowapp=lower(t2.name) where risk>='4' group by id, name, app_cat, technology, risk order by d_risk desc, sessions desc[/size]
I also made another dataset without the word "NOT". I printed out a custom report with all three charts.
[ol]
Unfortunately, in adding up the users, sessions, and bandwidth of employees and students, it doesn't equal the total. For instance, the initial report shows 742 total sessions of the app, Ultrasurf. However, in the segmented student report, it shows 754. This doesn't make sense to me. Is my coding just wrong?
Help would be greatly appreciated!
- Matt
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.
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 |
---|---|
1517 | |
1013 | |
749 | |
443 | |
209 |
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.