How do I query NFA 9.3.6 and later databases to properly show IP addresses?

Products

• CA Network Flow Analysis (NetQos / NFA)

Issue/Introduction

NFA 9.3.6 introduced some major database changes, one of these changes includes changing the data type of many of the columns which stored ip addresses.

For example when querying the harvester.routers table by running "select * from routers;" it will return a misaligned result and the Router column, which holds the router's IP address, displays a special character instead of numbers.  Using the old inet_ntoa() mysql function will also not properly translate this router column into a readible IP address as it would in earlier releases. 

 

These changes were made to make way for future features such as IPv6 support in future releases.  

Environment

NFA 9.3.6 and later.

Resolution

In order to decode the new data type, you need to use the new inet6_ntoa() mysql function.

Below are a few sample queries that can be used on your NFA servers using the new inet6_ntoa function:

For the reporter.routers table:

select ID, inet6_ntoa(routerAddress), sysname, deviceAlias, sysUpTime, from_unixtime(lastData), from_unixtime(lastReboot), from_unixtime(lastRefresh), from_unixtime(lastDiscovery), from_unixtime(lastHarvesterUpdate), firstPollError, nextPollRetry, harvesterID, profileID, snmpVersion, snmpPort, snmpTimeout, snmpRetry, snmpMaxRows, ifNumber, interfaceCount, agentCount, from_unixtime(dnsLastLookupTime), from_unixtime(dnsExpireTime), from_unixtime(syncUpdateTime), routerName, from_unixtime(routerUpdatedOn), templateId, snmpProxyAddress, dnsProxyAddress, tenantId, domainID from routers;

 

For the harvester.interfaces table:

select ID, inet6_ntoa(router),EphID, PstID, Enabled from interfaces order by router;

 

For the harvester.routers table:

select routerID, inet6_ntoa(router), from_unixtime(sysuptime), from_unixtime(obstime), state, from_unixtime(updatedon), pollstate, stateretry, profileid, from_unixtime(discoveryTime), from_unixtime(routerpolltime), from_unixtime(interfacesPollTime), from_unixtime(mappedTime), from_unixtime(pushtime), from_unixtime(reboottime), snmpversion, snmpPort, snmpRetries, snmpMaxRepetitions, TenantID from routers;

 

For the harvester.interfaces table to find a specific device's interfaces:

select ID, inet6_ntoa(router),EphID, PstID, Enabled from interfaces where inet6_ntoa(router)='xx.x.x.x';