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

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

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