Watchdog System Status for DSA shows 'DSA-Last Load Timestamp x hours before check' even though the DSA is up to date

Document ID : KB000020071
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

In ReporterAnalzyer 9.0 you may see an issue where the Watchdog service reports an Error under the Administration->System Status page where it shows an error like 'DSA-Last Load Timestamp: x hours before check'.

While this usually indicates the system is behind processing data, in this case however when checking the status of the system all interfaces appear to be up to date.

Recycling the Watchdog Service on the RA console does not resolve this issue.

Solution:

  1. Check the \Netqos\Reporter\DSALoader*.log file on the DSA server for an error like below:

    1:07:35 1 - DSALoadManager - Exiting... :
    Duplicate entry '1165830' for key 'PRIMARY'
    MySql.Data.MySqlClient.MySqlException: Duplicate entry '1165830' for key 'PRIMARY'
    at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
    at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
    at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
    at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()
    at NetQoS.Data.MySqlDataComponentBase.ExecuteScalar(MySqlConnection connection, String sql, DataParameter[] dataParameters) in D:\Builds\RA\Current\CORE LIBRARIES\dotnet\NetQoS.Data\MySqlDataComponentBase.cs:line 680
    at NetQoS.Data.MySqlDataComponentBase.ExecuteScalar(String sql, DataParameter[] dataParameters) in D:\Builds\RA\Current\CORE LIBRARIES\dotnet\NetQoS.Data\MySqlDataComponentBase.cs:line 362
    at NetQoS.ReporterAnalyzer.Data.DataStorageAppliance.DSAStatisticsDc.Insert(IPAddress dsaAddress, DateTime timestamp, TimeSpan duration, UInt32 filesFetched, UInt32 filesLoaded, UInt32 filesFailed, UInt32 filesStaged, UInt64 interfaces, UInt64 protoRows, UInt64 hostRows, UInt64 convRows, UInt64 tosRows, UInt64 tosPRows, UInt64 tosHRows, UInt64 tosCRows, UInt64 asRows, UInt64 asHopRows, UInt64 interfaceFlowRows, UInt64 topHostRows, UInt64 topHostProtRows, UInt64 topHostInterfaceRows, UInt64 topProtRows, UInt64 topProtHostRows, UInt64 topProtInterfaceRows, Double bulkLoadTime, Double connectTime, Double fileValidationTime, Double fileCheckTime, Double fileDeletionTime) in D:\Builds\RA\Current\REPORTER\NetQoS.ReporterAnalyzer.Data\DataStorageAppliance\DSAStatisticsDc.cs:line 38
    at NetQoS.ReporterAnalyzer.Business.DSAStatistics.Persist() in D:\Builds\RA\Current\REPORTER\NetQoS.ReporterAnalyzer.Business\DSAStatistics.cs:line 55
    at NetQoS.ReporterAnalyzer.DSALoaderService.DSALoadManager.PersistMetrics(DSAMetrics totals) in D:\Builds\RA\Current\REPORTER\NetQoS.ReporterAnalyzer.DSALoaderService\DSALoadManager.cs:line 405
    at NetQoS.ReporterAnalyzer.DSALoaderService.DSALoadManager.LogMetrics() in D:\Builds\RA\Current\REPORTER\NetQoS.ReporterAnalyzer.DSALoaderService\DSALoadManager.cs:line 390
    at NetQoS.ReporterAnalyzer.DSALoaderService.DSALoadManager.ManagerThread() in D:\Builds\RA\Current\REPORTER\NetQoS.ReporterAnalyzer.DSALoaderService\DSALoadManager.cs:line 196
    at NetQoS.ReporterAnalyzer.DSALoaderService.DSALoadManager.ManagerThreadWrapper() in D:\Builds\RA\Current\REPORTER\NetQoS.ReporterAnalyzer.DSALoaderService\DSALoadManager.cs:line 144

  2. This error, it is referencing a duplicate Primary Key constraint on the 'nqrptr.statistics' table on the DSA server.

    The 'statistics' table is holds the data that is queried when the Watchdog service polls the DSA for a status.

    When this error appears, it keeps trying to insert a row with the ID that is found in the error message, since this is the next auto increment value RA is supposed to use.

  3. In order to break this cycle one solution is to first backup the 'statistics' table with the query below on the DSA server:

    mysql -P3308 nqrptr
    create table backup_statistics select * from statistics;

  4. After backing up the table, run the query below to clear out this table so that the statistics table can again start to be populated.

    truncate table statistics;

  5. Then recycle the 'NetQoS Reporter/Analyzer DSA Loader Service' on the DSA and the 'NetQoS Reporter/Analyzer Watchdog' service on the RA console.

  6. After about 15-30 minutes you should start seeing rows being inserted again into the 'statistics' table on the DSA, which you can verify with the query below:

    select * from statistics;

  7. Wait at least 1 hour and check the 'System Status' page again in the RA website and the error should now be gone.