Not Equal Results between Dataset and Report Chart

[AtiT]

Hello,

I have a FAZ-VM running on 5.2.7. I have a problem with the different output when I test the dataset and when I generate the report.

I issued the following commands but the results are the same: # diagnose sql remove hcache All hcache tables will be erased! Do you want to continue? (y/n)y Done.

# diagnose sql remove query-cache Done.

# diagnose sql remove tmp-table Done.

What I get is attached.

Is it a bug or I missed something?

[hzhao_FTNT]

Hi AtiT,

As you know, report is generated by merging many hcache tables, but dataset test console not. All datasets in report must follow certain rules. Could you share your dataset?

Thanks,

hz

[AtiT]

Hello hzhao,

This is the dataset for the Event log type:

SELECT wtime, COUNT(*) AS clients FROM ( SELECT t1.time AS wtime, t1.source AS wsrce, smac AS wsmac FROM (     (SELECT TO_CHAR(TO_TIMESTAMP(`itime`)::timestamp, 'YYYY-MM-DD') AS time,     IPSTR(`srcip`) AS source,     COUNT(*) AS totalnum     FROM $log     WHERE $filter     AND `action`='disclaimer'     AND `logdesc`='Disclaimer accepted'     AND `status`='success'     AND IPSTR(`srcip`)!='0.0.0.0'     AND NULLIFNA(IPSTR(`srcip`)) IS NOT NULL     GROUP BY time, source     ORDER BY time ASC     ) AS t1     INNER JOIN     (SELECT TO_CHAR(TO_TIMESTAMP(`itime`)::timestamp, 'YYYY-MM-DD') AS time,     IPSTR(`srcip`) AS source,     `stamac` AS smac,     COUNT(*) AS totalnum     FROM $log     WHERE $filter     AND `subtype`='wireless'     AND `action`='client-ip-detected'     AND IPSTR(`srcip`)!='0.0.0.0'     AND NULLIFNA(IPSTR(`srcip`)) IS NOT NULL     GROUP BY time, source, smac     ORDER BY time ASC     ) AS t2     ON t1.source=t2.source ) GROUP BY wtime, wsrce, wsmac ORDER BY wtime ASC ) AS x GROUP BY wtime ORDER BY wtime ASC

[hzhao_FTNT]

Hi AtiT,

You have to put ### for inner queries:

###(...)### AS t1 INNER JOIN ###(...)### AS t2

regards,

hz

[AtiT]

Thank you very much, the ### fixed this issue.

Can I read more about the hcache and where to use the ### somewhere?

[hzhao_FTNT]

When report start to run, system will check ### for all subqueries which need to be merged first. Since there are many log tables will be queried, based on your original dataset, both sub and main queries will be executed for each log table, then system merge the results. But for modified dataset, only subqueries inside ###()### will be merged first, then execute main query at last. 

regards,

hz

[AtiT]

Hello,

I have a problem with the cached results again. I have a different results for two simple queries. Can anyone test it and say what is wrong or why the report has different output?

Shouldn't be the same?

===== test1 =====

SELECT `srcip` AS source, COUNT(*) AS totalnum FROM $log WHERE $filter GROUP BY source ORDER BY totalnum DESC

===== test2 =====

SELECT source, totalnum FROM ###( SELECT `srcip` AS source, COUNT(*) AS totalnum FROM $log WHERE $filter GROUP BY source ORDER BY totalnum DESC )### a

Tested on FAZ 5.4.3 and FAZ 5.6.0.

[hzhao_FTNT]

Hi there,

Test1 looks good to me, but it  seems you missed an aggregation function in test2, so the totalnum will be only from one log table. 

SELECT source, sum(totalnum) as totalnum FROM ###( SELECT `srcip` AS source, COUNT(*) AS totalnum FROM $log WHERE $filter GROUP BY source ORDER BY totalnum DESC )### a group by source order by totalnum desc

regards,

Huai zhao

[AtiT]

Hello,

Yes, that's look good for me. Thank you!

I just do not understand why I should do the sum() on the totalnum column as I have the results from the previous SELECT 'table a' and I do a SELECT on this result. The table exists just another select to show all entries from that table.

In other words -> show me the same as previous select = show me the results of table 'a' = show me the same results as test1.

Probably it will be some internal information that is related to caching and not publishable, I just want to understand how things work and why I am doing something...

[hzhao_FTNT]

Hi AtiT,

Depend on log rate, we need to query many log tables during report running. If we directly query all log tables, it will be easy to see "out of memory" in DB server, that's the reason why we developed hcache system. Under current hcache mechanism, the inner query with "###()###" will be applied to each log table, one log table will generate one hcache table(this will be done automatically when you schedule a report or enable auto-cache on it). During report running, the outer query will only work on those hcache tables, not log tables. 

Dataset test console is for testing purpose, it will query log tables directly, please do not select long time period if your log rate is high. In your case, test2 should work fine when you only have one log table, but if you have many log tables, it will take totalnum from the first log table only.

Below are some common issues which may not be detected by dataset test console:

•"$filter" is not applied •No "###" for inner query •"distinct/limit" is used in inner query -- for example: wrong: select sum(user_count) as user_count from ###(select count(distinct `user`) as user_count ...... right: select count(distinct user_src) as user_count from ###(select `user` as user_src from ... group by `user`... •aggregation function only applied to inner query ---- like test2 in your case •No column alias for column with function-- for example: wrong: select count(*) from $log... right: select count(*) as total_num from $log.... •No “order by” -- in some cases, these will affect report accuracy •Try to join log tables (for example, join traffic log with IPS log)   I hope these info helped.   Regards, hz