RESOLVED: Dataset OK, but report empty

[Frosty]

Hoping someone can give me a clue here. I am a newbie when it comes to designing reports in my FortiAnalyzer 100C. I have created a custom dataset, containing the following SQL query, which when I click the TEST link works just fine: -------------- begin SQL select id, 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 This gives me a few records which I expect to see, so I also created a custom Chart which links to my dataset. I then created a custom Report and added my custom Chart. But when I run it, the report is empty. I' ve edited the Report Options and checked that its set to report on This Week (when I view This Week in the Dataset test I see records show up) ... but I keep getting No Data. The column headers in the chart display in the report are correct (I have set it to display 3 columns: id, ssid, status If I add another column to the chart, the column appears in the report ... but still no data.

[AtiT]

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

[Frosty]

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.

[Frosty]

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?

[Matthijs]

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

[Frosty]

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.

[Frosty]

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 ...

[Matthijs]

Did you report the bug with FortiNet? ;)

[Frosty]

Yes, I' ve actually got 3 tickets open with Fortinet covering 3 distinct bugs in the FortiAnalyzer. A lot of the above " little issues" explain a lot to me about why we have struggled and struggled to get any sensible reporting out of our FA100C unit. I have had several contractors have a go at producing reports; both of them in essence " gave up" after about a day' s worth of battling to get any data out. It was only due to my sheer bloody-minded stubbornness that I persisted and persisted with testing and troubleshooting that eventually let me to find these syntax issues at the core of our inability to construct working reports. My next step is that I am going to get our Supplier to start lodging some feature requests with Fortinet for enhancements to the FA firmware. For example: (1) the Alerts system is woefully underpowered at present. I need to be able to flag specific criteria such as " Alert me to any new Unclassified WiFi APs which appear) and so on. I could write a report (at least I can now that I have worked out some of the syntactical issues) but that leads me to my next feature request: (2) I want an option available on my reports so that if a report contains nothing but an empty Dataset (no records returned) then I want to be able to suppress the report; sometimes I just want to have reports run to look for particular things, but don' t want to know if they don' t find anything.

[ede_pfau]

good work, thanks a lot!