Watchdog System Status for DSA shows 'DSA-Last Load Timestamp x hours before check' even though the DSA is up to date
SUMMARY
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', 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
This information is also available as a tech doc on Support.ca.com at the following link:
https://support.ca.com/irj/portal/anonymous/redirArticles?reqPage=search&searchID=TEC596128