Report Generation Failed in Multiple log type reports.

[Vojaganto]

Hi!

I'm creating a report. In the case, I've created a dataset with multiple log types (I matched data of traffic and App_ctrl using the sessionid).

The dataset run ok. The validation dataset run ok.

But when I will create a chart in the report, the report don't run and give me "Report Generation Failed"

Someone know the solution of this? The why this happens?

Regards!

[AtiT]

Hello,

it can be a dataset issue also. Could you post your dataset and the FAZ vesion also?

[Vojaganto]

Query 1:

SELECT UPPER(app_ctrl.user) as user, SUM(tr.duration) as duration, app_ctrl.app as application FROM $log as app_ctrl INNER JOIN (SELECT sessionid, duration FROM $log-traffic) AS tr ON tr.sessionid= app_ctrl.sessionid

WHERE LOWER(appcat)='video/audio' GROUP BY app_ctrl.user, application ORDER BY user

----

Query 2:

WITH app_ctrl AS ( SELECT UPPER("user") as user, app, appcat, sessionid FROM $log-app-ctrl ), tr AS ( SELECT duration, sessionid FROM $log-traffic)

SELECT app_ctrl.user as user, SUM(tr.duration) as duration, app_ctrl.app as application FROM app_ctrl INNER JOIN tr ON tr.sessionid = app_ctrl.sessionid

WHERE LOWER(app_ctrl.appcat)='video/audio' GROUP BY app_ctrl.user, application ORDER BY app_ctrl.user

---

In both datasets, the result are the same.

FortiAnalyzer 5.6

Thanks AtiT!

[AtiT]

Hello, I tested it on FAZ version 6.0.4 with the same results. I am not an expert of SQL but I think there is a combination of Fortinet implementation, caching and the query logic you wrote.

Your query: SELECT UPPER(app_ctrl.user) as user,        SUM(tr.duration) as duration,        app_ctrl.app as application FROM $log as app_ctrl INNER JOIN (   SELECT sessionid,          duration   FROM $log-traffic ) AS tr ON tr.sessionid=app_ctrl.sessionid WHERE LOWER(appcat)='video/audio' GROUP BY app_ctrl.user,

         application ORDER BY user

I think that the query should be cached so the cache indicator ### (or how Fortinet calls this) is missing:

SELECT UPPER(app_ctrl.user) as user,        SUM(tr.duration) as duration,        app_ctrl.app as application FROM $log as app_ctrl INNER JOIN ###(   SELECT sessionid,          duration FROM $log-traffic )### AS tr ON tr.sessionid=app_ctrl.sessionid WHERE LOWER(appcat)='video/audio' GROUP BY app_ctrl.user,

         application ORDER BY user

Now the report runs OK but in my case the result is empty. There is a problem still with caching, I think.

Another problem that your qurey looks something like (from my ponit of view):

select <your first query here> as table1 inner join ( <your second query here> ) table2 on table1.object=table2.object

But I think it should be something like: select * from ( <your first query here> ) table1 inner join ( <your second query here> ) table2 on table1.object=table2.object It means two queries in () and than join them. I think that the "AS" is not needed to have a table name.

According to yout dataset what you want to achieve I think that you not need to join two tables, everything is in the traffic log. You can try something like:

select upper(coalesce(nullifna(`user`), nullifna(`unauthuser`))) as user_src,        `app` as application,         sum(duration) as duration from $log where $filter       and logid_to_int(`logid`) not in (4, 7, 14, 20)       and lower(`appcat`)='video/audio' group by user_src,          application order by user_src asc,          duration desc

But still I think that this is not accurate enough. I watched a movie on netflix for almost 6 minutes and I found that there are lot of simultaneous sessions at the same time. So when I generate a report it shows me something around 24 minutes and 43 seconds. But the logs shows netflix applications in around 6 minutes - see the attached screenshot.

So probably you will need some other method to do this or there is something I do not know about, yet :)