PostgreSQL Datasets not working anymore in rel. 7.4
Hello,
I had the one more unpleasant surprise after upgrading the FortiAnalyzer to 7.4.5: several datasets that worked perfectly in 7.0.x stopped working with strange syntax errors, not being valid anymore. Also other datasets which were ok before, now need SQL code adjustments because hcahe requirements are very strict. Also, apparently only temporary tables are permitted, which is a change that is impossible to understand for me, and I had quite a lot of datasets that prepared tables on disk which were used by subsequent reports refering such persistent tables. No more permitted, but why?
This is an example of dataset which is no loger valid. The goal is to drill down into the DNS queries, listing the domains and query counts per each hour interval.
DROP table if exists time_intervals;
CREATE TEMP TABLE time_intervals AS
SELECT generate_series(
extract(epoch FROM (now() - interval '7 days'))::bigint,
extract(epoch FROM now())::bigint,
3600
) AS interval_start;
select cnt,from_itime(ts) as ts,dom,cnt from (
SELECT
t.interval_start as ts,
q.qname as dom,
COUNT(r.*) AS cnt
FROM
time_intervals t
LEFT JOIN
$log r ON r.dtime >= t.interval_start
AND r.dtime < t.interval_start + 3600
LEFT JOIN
(SELECT DISTINCT qname FROM $log) q ON r.qname = q.qname
GROUP BY
t.interval_start, q.qname
ORDER BY
t.interval_start, q.qname) tt where cnt>100 group by ts,dom,cnt order by ts asc, cnt desc;
And the report works in 7.0.7.
