RESOLVED: Dataset OK, but report empty

Hi, maybe I' m wrong but I think that the last column has to be integer type, nothing else - string etc. I tried to create a simplne Event report with log id and message and I get data from Dataset but not in Report the resuot was No Data. When I swapped the columns to Message - Log ID then I had data in the report. Other problem is that it seems the Analyzer is doing some auto-summarization in the last column. I have data from Dataset like: msg id Disk log roll request has been sent. 1 Disk log roll request has been sent. 2 Connected to FortiAnalyzer x.x.x.x 21055 user admin logged into the fw - 1337756854 1 HA device(interface) fail 2 .... The result from the report is: msg id A client broadcasts a DHCPDISCOVER message 950768 progress IPsec phase 2 654969 install IPsec SA 327496 negotiate IPsec phase 2 327482 Disconnected from FortiAnalyzer 62.84.128.21 324698 For example for the log from dataset: Disk log roll request has been sent. 1 Disk log roll request has been sent. 2 you will get in the report: Disk log roll request has been sent. 3 This is the problem on my side I think it is some bug. Try to arrange your report that you have integer type in the last column. Mybe it will help. About the auto-summarization I wrote on KB unfortunately without answer: http://support.fortinet.com/forum/tm.asp?m=83436&p=2&tmode=1&smode=1 AtiT --------- FCNSA FCNSP

Interesting idea. This morning I came up with an idea: what if the way the FA processes the SQL when you Test a Dataset was different to the way the FA processes the SQL when you use a Dataset in a Report? After some testing, it seems that this is the case. I have had to re-write my SQL to be as follows: -------------------- start SQL select ssid, (case `ap-status` when 0 then ' Unclassified' when 1 then ' Rogue!!!' when 2 then ' Accepted' when 3 then ' Suppressed' else ' others' end) as status, manuf, rssi, `radio-band` as radio, `security-mode` as security, `on-wire`, msg as message from $log where $filter and subtype = ' wireless' and action = ' rogue-ap-detected' and `ap-status` = 0 and rssi > 20 order by ssid --------------------- end SQL Note that I have replaced double-quotes " with backticks ` for all the column names which have a dash - in them. It seems that you can get away with double quotes " when you create a Dataset and then click Test ... that will work ... but when you try to do that with a Report that has a Chart which uses that same Dataset, you will get No Data. I have logged a ticked with Fortinet in the hope of getting an explanation and/or maybe getting this problem fixed in a future firmware release. Anyway, the end of the matter is that now I am getting data in my report, so that' s the main thing.

I now have another example of a SQL SELECT statement which works when Testing a Dataset, but which won' t work when used in a report: ------------------ begin SQL select ssid, from_dtime(timestamp) as noticed, (case `ap-status` when 0 then ' Unclassified' when 1 then ' Rogue!!!' when 2 then ' Accepted' when 3 then ' Suppressed' else ' No Status' end) as status, (case `sn-detected` when ' FAP22B3U11012725' then ' Level 1 Back' when ' FAP22B3U11012738' then ' Level 2 Back' when ' FAP22B3U11013044' then ' Level 1 Front' when ' FAP22B3U11013055' then ' Level 2 Front' else ' FAP Unknown' end) as FAP, manuf, rssi, `radio-band` as radio, `security-mode` as security, `on-wire`, msg as message from $log where $filter and subtype = ' wireless' and action = ' rogue-ap-detected' and `ap-status` = 0 and rssi > 20 order by ssid, noticed ------------------ end SQL Note that it is very similar to the query I was using earlier (see above). The key difference, and the thing which is causing it to fail (as verified by running the FA in debug mode for a while) is this bit: (case `sn-detected` when ' FAP22B3U11012725' then ' Level 1 Back' when ' FAP22B3U11012738' then ' Level 2 Back' when ' FAP22B3U11013044' then ' Level 1 Front' when ' FAP22B3U11013055' then ' Level 2 Front' else ' FAP Unknown' end) as FAP, If I replace that by this: `sn-detected` as fap, then it works fine. So something about this 2nd case statement works in Test modem but fails when used in Report mode. Any suggestions?

Can it be that you can only use 1 case statement? ;)

Possibly. Is that a known restriction? EDIT: if it is, then why does it work OK in the Dataset Test but not in a Report? EDIT: removed the first case statement, left the 2nd one in; it works in the Dataset Test but NOT in the Report ... so having just one CASE statement in the SQL SELECT does not fix it.

I found the problem. Not only is there an inconsistency between the Dataset Test screen and the actual running of a Report in terms of how quote marks and backticks are used (see my previous post above), but ALSO There is another inconsistency to do with case sensitivity ... in my SQL SELECT, the 2nd CASE statement ended with " ... as FAP," (UPPER CASE TEXT) ... but the Chart object doesn' t recognise this and converts everything to lower case apparently transparently ... and this causes the Chart to be broken. I changed my SQL SELECT to use " ... as fap," instead (lower case text) and everything magically started working. So that' s two bugs I' ve found in just a week. I' m on a roll ...

Did you report the bug with FortiNet? ;)

good work, thanks a lot!

I' m going to upgrade the FA100C from v4.0 MR3 Patch 3 to Patch 5 tomorrow morning. There is at least one fix noted in the release notes for Patch 4 which looks like it will fix that case-sensitivity issue: 170686: When the alias in a query contains upper case letters, a syntax error will be reported and the report cannot be generated There may also be undocumented fixes in there I suppose. Plus I noticed this other item of interest to me in the fixes for Patch 5: 176521: The FortiAnalyzer fails to store generic syslog messages I really would like to use the FA100C as my syslog device, so that now looks like it might be possible.

Now upgraded from 4.3.3 to 4.3.5 ... one fix ... one partial fix ... one not fixed ... and one new bug! (1) problem with double-quotes vs backticks working/not-working between Dataset and Report looks like it is fixed. (2) problem with case-sensitity on SQL query Alias names is partially fixed; you can now specify a Dataset with an UPPER CASE Alias name, but it will display as lower-case in the Chart definition and it will display as lower-case in the Report display itself; you can work around this by setting a colum heading name override in the Chart definition (by the way, this override was in the GUI of v4.3.3 but didn' t work properly; in v4.3.5 it does work properly, so that' s good) (3) inability to delete a Chart via the GUI even if it is not referenced in any Report is still broken in v4.3.5 however you can delete it via the CLI if necessary (4) NEW BUG: column headers and column data content misaligned in Chart/Dataset display in a Report (works fine when Testing the Dataset, just not in the Report itself).