create report based on other report top list

[fran1942]

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.

[chall_FTNT · Accepted solution]

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.

[chall_FTNT · Accepted solution]

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.

[hzhao_FTNT · Accepted solution]

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".