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

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.