Skip to main content
Vojaganto
Vojaganto
New Member
January 24, 2019
Question

Report Generation Failed in Multiple log type reports.

  • January 24, 2019
  • 2 replies
  • 5157 views

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!

    2 replies

    AtiT
    AtiT
    New Member
    January 24, 2019

    Hello,

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

    Vojaganto
    VojagantoAuthor
    New Member
    January 24, 2019

    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
    AtiT
    New Member
    January 27, 2019

    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 :)

    Terms & ConditionsAccessibility statement