DX Unified Infrastructure Management

Hello everyone,
We have a client with CA NFA and we want to integrate it with Grafana. I want to know if this is only possible with MySql or if there is another alternative.

Hi Carlos, I believe from a CA 'support' perspective, if you tried to integrate NFA with a third party tool like Grafana (new to me) it would possibly invalidate your NFA support contract should you (or your client) encounter any software issues with NFA. This is the first time I'm hearing of any intended integration with this product.  Regards, Shaun.

Shaun, Grafana is a dashboarding tool. 

It is tricky to pull data from mysql as most of it is stored on the harvester in a custom Mysql storage engine. 

You can pull directly from mysql on the console to get the data that appears in the Enterprise Overview Page, those tables are in the reporter database.

interafaceflows is the main table, then you can see the rest by running:

mysql reporter

show tables like '%top%';

You can use the command below to see the columns for each table.

"desc table <tablename>;" 

The agentID maps to the id column of the agents_all_view table if you want to map the data to specific interfaces/devices.

We have not seen anyone else attempt any type of integration between these products.  We have however had a lot of customers ask for an API solution for NFA and I believe some work has started on that.  There is an open Idea that Product Management has been asking for use cases on.  If you can comment on how an API solution would benefit you and how you would like to use it to integration with Grafana or other software, I would post some comment in the idea below:

API for NFA 

Some additional details on querying the data on the console and querying the data on the harvesters which has the most detailed 1 min and 15 min data that I had shared with another customer.

Most of the data for NFA is stored on the Harvesters and accessed through a customer mysql storage engine which is very tricky to query against as it is not indexed so you have to have very specific where clauses. Also some of the protocolID's are stored in hex so they need to be converted.  So it is not s straightforward process.  I provided some additional details on this towards the bottom of this email if you wish to try to query for data that way.

The easier option is to query the Console server, which stores all of the data for the Enterprise Overview page, so you may be able to get what you need from there.

On the console, the tables you can query for data are:

If you run the command below on the console, it will log you into the database.

Then you can run a "desc <table name>" to view what columns are in that table to see if it will be of use to you.

The AgentID is the interface's ID's and can be mapped back to the ID column in the agents_all_view table.

If you want to access the database remotely, the list of databases, ports, usernames, and passwords can be found in the kb below:
https://comm.support.ca.com/kb/what-databases-does-nfa-use-and-what-is-their-default-port-usernames-and-passwords/kb000037316

If you wish to query the harvesters directly to see the 1 minute and 15 minute data that you see on regular interface reports, as I noted above it is a bit more complicated.
However you can get an idea of how to formulate the queries by enabling mysql query logging on the harvester, and loading a view in NFA that you want to mimic through a direct mysql query.  The query logging logs every query that hits the database while it is enabled, so you can use the queries in the query log as a template for building out your own queries. 

To enable mysql query logging on the harvesters run the following:
      mysql -P3307

Then run 

SET GLOBAL general_log_file='log.log';set global general_log = 'ON';

Then load your view in NFA, and then immediately shut off the query logging by running:

set global general_log = 'OFF';

The log filed log.log will be in the \CA\NFA\netflow\data\ directory on the harvester. 

The timestamps in the file are unix timestamps, you can use the link below to covert the timestamps:

As I noted above, Protocol data is stored in hex, the first 2 bytes are the protocol ID, and the other bytes are the port number. 
You can use mysql to convert these values from hex to a readable format that will show the protocol ID and the port number. 

For example... the first section of the query below will display the protocol id number, the second part will show the port number. 
select ((protocol >> 16) & 0xFF) as protocol_num, (protocol & 0xffff) from host_traffic; 

Applications that have a protocol ID of 6 which is TCP traffic, or 17 which is UDP traffic, will use the port number as well in protocol based views in NFA. 

Applications that have other protocol ID's may display 0 for the port number, as they will be classified stricly by their protocol ID. For example protocolID 50 is displayed as "ESP" data. 
Some commonly used protocol ID's are listed here: https://technet.microsoft.com/en-us/library/cc959827.aspx 

Below is a full query that can be used to display data just as it does in the gui, this is what the code uses when it queries the database: 
select ((protocol >> 16) & 0xFF) as protocol_num, (protocol & 0xffff), sum(outoctets) Bytes from protocol_traffic where (router=inet_aton('10.1.192.110') and interface=5) and TimeStamp > 1512683460 - 60 and TimeStamp <= 1512685680 and protocol not in (4,0,1,2,3) group by protocol having Bytes > 0 order by Bytes desc limit 12

Thank you very much Christopher for the information, it's very helpful.

I believe MySQL is your only option to retrieve data from NFA. 

I think this is an interesting use case. Heard that an API in design phase to grab flow data from NFA finally out to other third party tools. We have also several use cases to export flow data but due to missing API we were not able to implement them so far.