FortiAnalyzer 5.2 - Charts with different date ranges in same report?

[SSC_Seb]

Hi all. We've recently procured a FAZ-1000E device to refresh our old FAZ-2000A running on 4.0 MR3. As part of the upgrade process, I'm currently rewriting the old reports we had customized. I've run into an annoying issue.

In 4.0 MR3, we were able to insert charts into the same report and customize each chart's date range. For example, in one report we had 2 charts with a date range of "Yesterday" followed by another chart with a date range of "Last 7 Days". In 5.2, I can't find how to do this. The only date range I can see is in the report configuration and affects all of the charts in the report. If I try to go to the properties of a chart from the report layout, I can't see a field to configure a date range for that specific chart. I can add filters but I can't find any that would allow us to change the reporting period for that chart.

Did FAZ lose the ability to have charts with different reporting periods in the same report between 4.0 MR3 and 5.2? That would be disappointing. If the functionality is back in 5.4, that'd be nice to know, but we won't be able to go to that version for some time given that we still have many FortiGate devices running 4.0 MR3 and I've been told FAZ 5.4 cannot receive those logs.

Appreciate any help, thanks.

[SSC_Seb]

I think I figured out a workaround, if anyone else needs to do something like this. Make a dataset with an SQL query that doesn't include the $filter variable used by FAZ and instead use "where to_timestamp(dtime)::date>=(current_date-7) and to_timestamp(dtime)::date<current_date" as well as any device selectors you need (since filter selections won't affect the query). Seems to work.

[RobertReynolds]

I'm trying to do the exact same thing in 5.4 having two time periods in one report and I cant see how to do it either.

Could you please post an example of a dataset using your "where to_timestamp(dtime)::date>=(current_date-7) and to_timestamp(dtime)::date<current_date" query please?

EG: I' trying to create a report showing websites and categories by bandwidth for "yesterday" as well as showing websites and categories by bandwidth for "last 7 days" in the same report, amongst many others.

[SSC_Seb]

My 5.2 dataset is using this query:

select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, sum(rcvdbyte+sentbyte) as volume from $log where to_timestamp(dtime)::date>=(current_date-7) and to_timestamp(dtime)::date<current_date and devid in ('FGTxxx','FGTxxx') group by user_src having sum(rcvdbyte+sentbyte)>0 order by volume desc

Which gives me the volume of traffic per user (IP if user is blank) over the last 7 days on a cluster of 2 devices (FGTxxx are the serial numbers). I have to specify those here since no $filter variable is used in the query (since $filter would evaluate the time period for the report, and I don't want to use it). The report that uses this is set to a period of 1 day, but this dataset query ignores that and grabs last 7 days.

[SSC_Seb]

Well, looks like I spoke too soon and I was wrong. It works in the query tester for the dataset, but apparently it's not the same in the report. Even though there's no $filter in the query, it still looks like it's filtered based on the report's chosen period.

Off to try my alternate solution; base the report on last 7 days, but change every query that wants yesterday's data to include "where to_timestamp(dtime)::date=(current_date-1)". Hopefully that works better, since the report filter will include 7 days of data and the dataset queries will further filter on yesterday when needed...

EDIT: Also note that dtime is a unix epoch field which is usually in UTC but is logged by FAZ as the local timezone, and to_timestamp does timezone interpretation as well, so the above actually doubles your offset from UTC. Use "(to_timestamp(dtime) at time zone 'UTC')::date=(current_date-1)" instead... Tons of fun.

[SSC_Seb]

Recoded as explained, and things kinda sorta look like they work... And then my dataset for admin events on a HA pair goes wrong and displays events for the past week instead of the past day, even with the dataset query explicitly checking the dtime for yesterday's date. And if I remove the chart's filter for the device, somehow magically the chart now display yesterday's events only. So figure that one out; query with a device filter somehow ignores the part of the query checking the date for yesterday, same query works when the device filter is removed.

Just... never mind all of this. Reports are ridiculous, convoluted, obscure, obfuscated and I can't find any report writing guide or anything like that. The whole reporting system on 5.2 is pretty BS and I'm getting tired of trying to wrestle it into a usable form.