Need dataset timeline for thread feed blocked

[rhap4boy]

How do I create a dataset timeline for thread feed blocked?

I have a fortigate policy that blocks IPs obtained from Thread feed.  I would like to know in FortiAnalyzer how to create a dataset that counts the number of blocks by this policy daily over time.

[rhap4boy]

Is this the correct way of doing it?

select $flex_timescale as timestamp, COUNT(*) as totalnum from $log where $filter group by timestamp order by timestamp

and also tried this

select $flex_timescale as timestamp, COUNT(*) as totalnum from $log where $filter and policyid=117 group by timestamp order by timestamp

How do I do it so that the filtering is done by the Filter Settings in Report rather in the dataset? I tried both dataset above with the appropriate filters and the results are not the same.

[Debbie_FTNT]

Hey rhap,

in its basics, the dataset should roughly provide what you're looking for.

The '$flex_timescale' variable doesn't always mean 'per day', the timescale depends on the timeframe you run the report for (if you run it for a month, the timesteps might be 1 day, if you run the report for a day, the timesteps might be 1 hour).
The '$filter' variable is what applies the report/chart level filtering (what device, timeframe, etc the report will be run for).

If you filter for policy ID 117 in the report AND in the dataset, this shouldn't make too much of a difference.

If you put the filter 'policyid=117' in statically in the dataset, then no matter what filters you set in report, FortiAnalyzer will also always filter on the policy ID as well.

You mentioned you received different results? How different were they?

[rhap4boy]

Thank you for the quick response Debbie_FTNT, see below reply

[rhap4boy]

Here is the chart dataset without policyid=117

Here is the chart dataset with policyid=117

 

Here is the filters on the report settings on both reports

Dataset query is the only thing I changed between the two reports.

 Report time period is set to today

If you look at the test query, the result for today totalnum doesn't match what is in the chart report with policyid=117.

[rhap4boy]

Also I changed the Report Filter Log Field to use PolicyID instead of (Rule) with same result.

[rhap4boy]

It turns out the chart has its own filter, it doesn't take it from the report settings.

Once I put in the filter in chart, it's working now.

[Debbie_FTNT]

Hey rhap,

thanks for sharing that info with us.

I was digging through documentation to see what might be happening, but didn't consider that the report filter wouldn't apply for some reason.

I'm not sure if report filter and chart filter would interfere with each other; if you're seeing inconstant results for some reason, it might be a good idea to open a ticket with FortiAnalyzer team to investigate.