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.
Hello Rosa Technocrat, and thank you for your interest in this problem.
The first error when I validate the exact same SQL code is "ERROR: 'group by' or 'order by' clause is expected in hcache."
Then I tried the following code
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 where $filter group by ts,dom order by ts asc
LEFT JOIN
$log r ON r.dtime >= t.interval_start
AND r.dtime < t.interval_start + 3600
where $filter
group by r.dtime order by r.dtime
LEFT JOIN
###(SELECT qname FROM $log where $filter group by qname order by qname)### q ON r.qname = q.qname
GROUP BY
t.interval_start, q.qname order by t.interval_start
ORDER BY
t.interval_start, q.qname) tt where $filter and cnt>100 group by ts,dom,cnt order by ts asc, cnt desc;
For this I get Validation=OK but syntax error on LEFT JOIN.
