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

create report based on other report top list

Hello, I have a report#1 which shows the top 10 bandwidth users of the category 'social networking'.

Now, I want to create a second report#2 which details which social networking sites each of report#1's users went to.

 

My question is, how do I tell my second report#2 to only use the top 10 users that were selected in report#1 ?

 

Thank you kindly for any help.

 

2 Solutions
chall_FTNT
Staff
Staff

The short answer is that the results of 1 report cannot be referenced by another report.

 

However, you can design a SQL query (aka dataset) to nest your queries.  The inner query gives the results in your report #1.  The outer query uses that output as its input.

 

For an example of a predefined dataset with nested queries, see:

App-Risk-Web-Browsing-Summary-Category

 

Notice that the inner query is bounded by ###(    )### t.  This ensures that the inner table has an hcache table generated for it & so report generation works properly.

Chris Hall
Fortinet Technical Support

View solution in original post

hzhao_FTNT

Right, this can be done in one report only

dataset like below:

select user_src, hostname, sum(bandwidth) as bandwidth from ###(select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, hostname, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-traffic where $filter and hostname is not null and logid_to_int(logid) not in (4, 7, 14) and (countweb>0 or ((logver is null or logver<52) and (hostname is not null or utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter')))) and catdesc='Social Networking' group by user_src, hostname having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc)### t group by user_src, hostname order by bandwidth desc

 

create a chart based on it, set table type to "drilldown".

View solution in original post

2 REPLIES 2
chall_FTNT
Staff
Staff

The short answer is that the results of 1 report cannot be referenced by another report.

 

However, you can design a SQL query (aka dataset) to nest your queries.  The inner query gives the results in your report #1.  The outer query uses that output as its input.

 

For an example of a predefined dataset with nested queries, see:

App-Risk-Web-Browsing-Summary-Category

 

Notice that the inner query is bounded by ###(    )### t.  This ensures that the inner table has an hcache table generated for it & so report generation works properly.

Chris Hall
Fortinet Technical Support
hzhao_FTNT

Right, this can be done in one report only

dataset like below:

select user_src, hostname, sum(bandwidth) as bandwidth from ###(select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, hostname, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-traffic where $filter and hostname is not null and logid_to_int(logid) not in (4, 7, 14) and (countweb>0 or ((logver is null or logver<52) and (hostname is not null or utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter')))) and catdesc='Social Networking' group by user_src, hostname having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc)### t group by user_src, hostname order by bandwidth desc

 

create a chart based on it, set table type to "drilldown".

Labels
Top Kudoed Authors