Custom Report - Detailed Firewall Changes

[jgrycza]

I am trying to create a custom report that gives me the details on changes made to our FortiGate firewalls.  I have created the following dataset:

Detailed-Firewall-Changes -

select * from $log-event where $filter and type='event' and subtype='system' and cfgattr!='' order by itime desc

Which, when I test I can see the results of something I did specifically to test this dataset.  However, when I create my custom chart and report I receive no results.

I created the chart, Detailed Firewall Changes, selected the dataset "Detailed-Firewall-Changes" and selected the fields from the dataset that I want to see.  However, it keeps coming back with "No matching log data for this report".  I don't understand where the disconnect is between the dataset, the chart and the report.

Any help would be greatly appreciated.

[hzhao_FTNT]

Hi there, "select *" is not supported since chart need to know exact field name before rendering. Please change "*" into real field name and try again.

Regards,

hz

[jgrycza]

I made the changes to use the field names rather than the "*" in the select statement.  I also went into the chart and re-selected the dataset, re-did the fields I want in the chart, and even re-added the chart to the report and I still get "No matching log data for this report".

Anything else I am missing?

[hzhao_FTNT]

Pls post your full query, FAZ version, FGT version, I will have a try.

hz

[jgrycza]

Here is the information you requested.

Query:

select devid, user, ui, action, cfgpath, cfgobj, cfgattr, msg from $log-event where $filter and type='event' and subtype='system' and cfgattr!='' order by itime desc

FAZ version: v5.2.2-build0706 150415

FGT versions:

- 200D: v5.2.3,build670

- 100D: v5.2.3,build670

Thank you,

Jeremy

[hzhao_FTNT · Accepted solution]

pls try:

select devid, `user` as user, ui, action, cfgpath, cfgobj, cfgattr, msg from $log-event where $filter and type='event' and subtype='system' and cfgattr is not null order by itime desc

user can not be queried without ``. If you still see no results in report, run below CLI to remove hcache.

dia sql remove hcache

regards,

hz

[jgrycza]

OK, I copied the sql statement as written, still no results listed.  I then did what you recommended and cleared the hcache and still no results get pulled into the report.  Rather frustrating that I can see the items in the dataset test, but not in the report.  Any other thoughts?   Could this be a bug in the FAZ software?

[jgrycza]

I'm wondering could it be an issue with the chart layout?  I have attached a print screen.

[hzhao_FTNT]

From your screenshot, it seems you didn't select user data-binding in your second column, not sure if you have other wrong config on other columns. Please check every data-binding in your chart config. 

jgrycza wrote:

I'm wondering could it be an issue with the chart layout?  I have attached a print screen.

[jgrycza]

That was it.  I corrected the data-binding and now my report works.  Thank you for your help.