Trouble creating custom charts/datasets separating data into different IP ranges

[mwalsh1]

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]

High Risk Apps Crossing the Network (Students)

High Risk Apps Crossing the Network (Non-Students)

High Risk Apps Crossing the Network (Total) -- This one using the predefined dataset/chart[/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