days for generating a report on FAZ 400C

[ksm]

Hi,

we have this kind of issues on our FORTIANALYZER since we have it, but actually it is extremly slow for generating reports, even very small ones. So It is difficult to understand. Generate a report can take until 14 days for 10 pages...

Could you telle me if there is any way to troubleshoot for one report how it has be made ?

Thanks in advance for the advices.

FAZ 400C

Logs /sec : often below the sustained Log rate of 200Actual Log insert time 14000 seconds. ( Working on a report for quite 3 days... )

Number of devices : 75 / 300 allowed

Gb / day : 2.41 / 15 Licensed

Firmware : v5.2.5-build3175 160119 (GA)

JS

[hzhao_FTNT]

Please run below CLI during report running.

dia log device

dia test application fortilogd 2

dia test application sqllogd 2

get system report auto-cache

exe sql-report list-schedule <adom-name>

dia de en

dia sql status sqlreportd

dia sql status run-sql-rpt  (several times during report running)

get sys performance

exe top (several times during report running)

Regards,

hz

[ksm]

Hi,

thanks a lot for your answer, I will do all of that and will post a reply ASAP.

JS

[ksm]

Hi,

here is my putty.log with all commands, some typed several times as requested.

The report is running for quite 3 days now.

Thanks in advance for your help. I have to also analyse all this now, if I can see something weird.

JS

[hzhao_FTNT]

Hi there, I saw there are many reports which name contain "RAPPORT MENSUEL", are they using same charts but with different device filter? If yes, our new "report group" feature will help you, I will send you detailed instruction. If not, please open a ticket and post ticket number here, I need remote access to your FAZ.

Regards,

hz

[ksm]

Hi there,

thanks for your quick answer.

effectively, these reports have the same structure, strictly same charts. Each report is for one different device or cluster.

I have discovered the report group functionnality some days ago, and have put it on another Adom, but I am OK for receiving your instructions :)

Best regards.

JS

[hzhao_FTNT]

Please try below steps:

1. stop current hcache building process by rebuilding hcache in a future date:

dia sql rebuild-report-hcache "2016-05-01 00:00:00" "2016-06-01 00:00:00"

2. config report group by below CLI:

config system report group edit 1 set adom root config group-by edit devid next edit vd end set report-like RAPPORT end

3. rebuild hcache for the report period you need, for example, if you mostly  run last 7 days report, then

dia sql rebuild-report-hcache "2016-03-30 00:00:00" "2016-04-07 00:00:00"

4. wait for hcache creation, you can run CLI "dia test app sqlrptcached 2" to monitor it, when you see pending=0 or 1 in first line "Number of log table read", the hcache tables are ready, you can run report now. The first report running will be a little slower, but all the rest should be a lot faster.

Good luck!

hz

[ksm]

Hi, thanks for your answer. I haven't tried at this time your conf. I will do it after your next reply I guess. Here is the situation, I have tried to setup report group in my root Adom, my second Adom was already configured. All reports , since those last modifications, have been unblocked and generated, but all are empty. Maybe my report-group conf is not correct. Before trying your last steps, here is my report-group configuration, could you tell me if you see something wrong ? "config system report group     edit 1         set adom "Trapil"         set case-insensitive disable             config group-by                 edit "test"                     set var-expression "Rapport"                 next             end         set report-like "Rapport"     next     edit 2         set adom "Trapil"         set case-insensitive disable             config group-by                 edit "test2"                     set var-expression "RAPPORT MENSUEL"                 next             end         set report-like "RAPPORT MENSUEL"     next     edit 3         set adom "root"             config group-by                 edit "test3"                     set var-expression "RAPPORT MENSUEL"                 next             end         set report-like "RAPPORT MENSUEL"     next end" I can see all my reports, classified in groups, here is an example for root Adom : "FortiAnalyzer-400C # exe sql-report list-schedule root NAME   SCHEDULED  AUTO-CACHE  REPORT GROUP      REPORT TITLE ================================================================================                                                                                                                                                             == 1      -          -           -                 Security Analysis 10     V          V           3(test3)          RAPPORT MENSUEL CARRARE 10000  -          -           -                 User Security Analysis 10001  -          -           -                 Application and Risk Analysis 10002  -          -           -                 Bandwidth and Applications Repor                                                                                                                                                             t 10003  -          -           -                 Email Report 10004  -          -           -                 Admin and System Events Report 10005  -          -           -                 User Report 10006  -          -           -                 Threat Report 10007  -          -           -                 IPS Report 10008  -          -           -                 Detailed Application Usage and R                                                                                                                                                             isk 10009  -          -           -                 Applications - Top 20 Categories                                                                                                                                                              and Applications (Bandwidth) 10010  -          -           -                 Applications - Top 20 Categories                                                                                                                                                              and Applications (Session) 10011  -          -           -                 Applications - Top Allowed and B                                                                                                                                                             locked with Timestamps 10012  -          -           -                 Websites - Detailed Browsing Log 10013  -          -           -                 Websites - Hourly Website Hits 10017  -          -           -                 Websites - Top 500 Websites Visi                                                                                                                                                             ted by Users (Bandwidth) 10018  -          -           -                 Websites - Top 500 Websites Visi                                                                                                                                                             ted by Users (Session) 11     V          V           3(test3)          RAPPORT MENSUEL CEMCAT 12     V          V           3(test3)          RAPPORT MENSUEL CFM 13     V          V           3(test3)          RAPPORT MENSUEL COMEPA BAGNOLET 14     V          V           3(test3)          RAPPORT MENSUEL COMEPA NANTEUIL 15     V          V           3(test3)          RAPPORT MENSUEL DEHON VINCENNES 16     V          V           3(test3)          RAPPORT MENSUEL HM AVOCATS 18     -          -           3(test3)          RAPPORT MENSUEL test TERRA 2 19     V          V           3(test3)          RAPPORT MENSUEL KERBEROS CHATOU 2      -          -           -                 Client Reputation 20     V          V           3(test3)          RAPPORT MENSUEL KERBEROS DATA CE                                                                                                                                                             NTER 21     V          V           3(test3)          RAPPORT MENSUEL KLEKOON DATA CEN                                                                                                                                                             TER 22     V          V           3(test3)          RAPPORT MENSUEL KLEKOON SIEGE 23     V          V           3(test3)          RAPPORT MENSUEL NONY 24     V          V           3(test3)          RAPPORT MENSUEL PACKSERVICES VAU                                                                                                                                                             X-LE-PENIL 25     V          V           3(test3)          RAPPORT MENSUEL SPPIFinance 26     V          V           3(test3)          RAPPORT MENSUEL SUNTEC 27     V          V           3(test3)          RAPPORT MENSUEL TECHEVENT DATA C                                                                                                                                                             ENTER 28     V          V           3(test3)          RAPPORT MENSUEL TECHEVENT SIEGE 29     V          V           3(test3)          RAPPORT MENSUEL TERRA 3      -          -           -                 Wireless PCI Compliance 30     V          V           3(test3)          RAPPORT MENSUEL VILLA ARSON 31     -          -           -                 test 33     V          V           3(test3)          RAPPORT MENSUEL PACKSERVICES RES                                                                                                                                                             TOMURET 4      -          -           -                 VPN Report 5      -          -           -                 Web Usage Report 6      -          -           3(test3)          RAPPORT MENSUEL KERBEROS TEMPLAT                                                                                                                                                             E 60004  -          -           -                 Template - Admin and System Even                                                                                                                                                             ts Report 60009  -          -           -                 Template - Top 20 Categories and                                                                                                                                                              Applications (Bandwidth) 60012  -          -           -                 Template - User Detailed Browsin                                                                                                                                                             g Log 60013  -          -           -                 Template - Hourly Website Hits 60017  -          -           -                 Template - User Top 500 Websites                                                                                                                                                              by Bandwidth 60018  -          -           -                 Template - User Top 500 Websites                                                                                                                                                              by Session 60019  -          -           -                 Template - Application Risk and                                                                                                                                                              Control 60020  -          -           -                 Template - FortiClient Default R                                                                                                                                                             eport 7      -          -           -                 WiFi Network Summary 8      -          -           3(test3)          RAPPORT MENSUEL KERBEROS CHATOU_                                                                                                                                                             1404210279 9      V          V           3(test3)          RAPPORT MENSUEL BFReseaux" In your informations, as I saw it in a release note too, I do not understand "edit devid" and "edit vd". So maybe that's the point because I do not mention these informations in my report group config. Could you enlight me about this ? JS

[ksm]

For your information I tried the hcache rebuild, but, apparently, nothing happens :

"FortiAnalyzer-400C # dia sql rebuild-report-hcache "2016-05-01 00:00:00" "2016-06-01 00:00:00" All the current pending hcache request will be erased! Do you want to continue? (y/n)y 0 SQL tables in pending list for report hcache. Done. FortiAnalyzer-400C # dia sql rebuild-report-hcache "2016-03-01 00:00:00" "2016-04-01 00:00:00" All the current pending hcache request will be erased! Do you want to continue? (y/n)y 1905 SQL tables in pending list for report hcache. Done. FortiAnalyzer-400C # dia test app sqlrptcached 2 Number of log table read: all=4145(fortiview=1995, rpt=2150) pending=1905 Number of log table done: all=4144(fortiview=1995, rpt=2149) FortiAnalyzer-400C # dia test app sqlrptcached 2 Number of log table read: all=4145(fortiview=1995, rpt=2150) pending=1907 Number of log table done: all=4144(fortiview=1995, rpt=2149) FortiAnalyzer-400C # dia test app sqlrptcached 2 Number of log table read: all=4145(fortiview=1995, rpt=2150) pending=1908 Number of log table done: all=4144(fortiview=1995, rpt=2149) FortiAnalyzer-400C #"

one hour after tring the rebuild, it does not decrease.

JS

[hzhao_FTNT]

Hi JS,

What's your mostly used report period? If last 7 days, please stop current hcache rebuilding and start rebuilding for the last 7days only. BTW, please also open a ticket and leave your remote session info in the the ticket, then post the number here. I will follow up in the ticket.

Regards,

hz