NFA Custom Reports are getting stuck in Queued or Defined but not completing

Document ID : KB000032476
Last Modified Date : 18/10/2018
Show Technical Document Details
Introduction:

Custom Reports fail to run and stay in either the "Queued" or "Defined" status.

Background:
This may happen after an upgrade to NFA 9.3.2 or NFA 9.3.3 if the reporter.flow_forensics_deleted table already has a row that NFA is trying to insert.

The <Install Dir>\Reporter\logs\ReportService*.log shows errors like:
15:23:20 -1 - Query: 
reporter@127.0.0.1:3308 
Duplicate entry '1' for key 'PRIMARY' 
insert into flowforensicsreport_definitions_deleted select FlowForensicsReportID, FlowForensicsReportTypeID, UserID, ReportFolderID, Name, Description, StartTime, EndTime, LastExecutionStartTime, LastExecutionEndTime, LastExecutionError, NextExecutionTime, unix_timestamp() from flowforensicsreport_definitions where FlowForensicsReportID = 1; 
(0.0000 ms) 
at NetQoS.Data.MySqlDataComponentBase.ExecuteNonQuery(MySqlConnection connection, String sql, DataParameter[] dataParameters) 
at NetQoS.Data.MySqlDataComponentBase.ExecuteNonQuery(String sql, DataParameter[] dataParameters) 
at NetQoS.ReporterAnalyzer.Data.ReporterAnalyzer.FlowForensicsReportDefinitionsDc.InsertIntoDeletedTableByFlowForensicsReportId(UInt32 flowForensicsReportId) 
at NetQoS.ReporterAnalyzer.Business.FlowForensicsReport.DeleteByFlowForensicsReportId(UInt32 flowForensicsReportId) 
at NetQoS.ReporterAnalyzer.Business.FlowForensicsReport.DeleteStale() 
at NetQoS.ReporterAnalyzer.ReportService.ReportService.timer_Elapsed(Object sender, ElapsedEventArgs e) 
at System.Timers.Timer.MyTimerCallback(Object state) 
at System.Threading._TimerCallback.TimerCallback_Context(Object state) 
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) 
at System.Threading._TimerCallback.PerformTimerCallback(Object state) 
 
 
Environment:
9.3.x
9.5.0
Instructions:
1. RDP to the NFA Console and open a command prompt
2. Run the following to truncate the flowforensicsreport_definitions_deleted  table:
mysql -P3308 -D reporter -t -e "truncate flowforensicsreport_definitions_deleted;"
3. Recycle the "Netqos ReporterAnalyzer Report Service"
4. Run the Custom Report again.
5. If the report still doesn't run, try recycling the "NetQoS Reporter/Analyzer Query Services" service and resubmitting the report.
Additional Information:
Being that this table only stores a record of deleted Flow Forensics reports, there should be no harm in deleting it.  However we do make a backup of the table in step 2, so that the table can still be referenced if needed.