NFA 9.3.6 To CAPC Sync Failure: Column 'routerAddress' / 'r.ID' is ambiguous

Article Id: 6999
Status: Published
Updated On: 14-02-2018 08:14
Legacy Id: TEC1044352
Products:
CA Network Flow Analysis (NetQos / NFA)
Issue/Introduction:

You may experience NFA 9.3.6 Sync failures with CAPC/NPC where you see errors in the ProductSyncWS.log:

0:00:47 3 - Pull - : 

Column 'routerAddress' in field list is ambiguous 

MySql.Data.MySqlClient.MySqlException (0x80004005): Column 'routerAddress' in field list is ambiguous 

at MySql.Data.MySqlClient.MySqlStream.ReadPacket() 

at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) 

at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& 

 

0:00:47 3 - Pull - : 

Unknown column 'r.ID' in 'field list' 

MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'r.ID' in 'field list' 

at MySql.Data.MySqlClient.MySqlStream.ReadPacket() 

at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)

 

 

Cause:

Due to some of the changes made in NFA 9.3.6, some additional SQL changes may have to be made for some customer environments.

Environment:

Network Flow Analysis 9.3.6CAPC 3.0+Windows Server 2012

Resolution:

  1. RDP to the NFA Console Server and open up a CMD prompt.
  2. Type and execute: mysql reporter
  3. Execute these 4 queries one at a time.
    1. UPDATE reporter.automaticgroup_definitions SET membershipWhere="r.ID > 0" WHERE definitionName='All Routers';
    2. UPDATE reporter.automaticgroup_definitions SET membershipWhere="folder.id={0}" WHERE definitionName='Custom Reports By Folder';
    3. UPDATE reporter.automaticgroup_definitions SET membershipWhere="av.harvesterAddress='{0}'" WHERE definitionName='Routers By Harvester';
    4. UPDATE reporter.automaticgroup_definitions SET membershipWhere="deleted='N'" WHERE definitionName='All Site to Site';
  4. You can now quit MySQL and open the CAPC/NPC GUI and execute a full re-sync to NFA.