FortiAnalyzer reports - how to group subdomains to only show the root domain

Most Fortinet FortiAnalyzer reports use the full domain including subdomains, e.g. www.google.com or maps.google.com. If you want to group subdomains into the root-domain (e.g. google.com), then you might clone the existing reports & dataset and adjust the new dataset from:

Original Dataset:

select domain, string_agg(distinct catdesc, ', ') as agg_catdesc, sum(requests) as requests from ###(select hostname as domain, catdesc, count(*) as requests from $log where $filter and (eventtype is null or logver>=52) and hostname is not null and catdesc is not null and action!='blocked' group by domain, catdesc /*SkipSTART*/order by requests desc/*SkipEND*/)### t group by domain order by requests desc

Changed Dataset:

select domain, string_agg(distinct catdesc, ', ') as agg_catdesc, sum(requests) as requests from ###( select coalesce (nullifna (root_domain (hostname)), ipstr(dstip)) as domain, catdesc, count(*) as requests from $log where $filter and (eventtype is null or logver>=52) and hostname is not null and catdesc is not null and action!='blocked' group by domain, catdesc /*SkipSTART*/order by requests desc/*SkipEND*/)### t group by domain order by requests desc

Dataset change:

select hostname as domain
->
select coalesce (nullifna (root_domain(hostname)), ipstr(dstip)) as domain

Result screenshots:

Before adjusting the dataset:

After adjusting the dataset:

Source:

https://kb.fortinet.com/kb/microsites/search.do?cmd=displayKC&docType=kc&externalId=fortianalyzer-v560-dataset-referencepdf&sliceId=&docTypeID=DT_PRODUCTDOCUMENTATION_1_1&dialogID=130376293&stateId=1%200%20130378047%27)