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:
interfaceflows - Stores interfaces data rates
tophostinterfaces - Stores hosts data per interface
tophostprots - Stores top Hosts by protocol data
tophosts - stores hosts data
topprothosts - stores top protocols by host
topprotinterfaces - stores top protocols by interface
topprots - stores top protocols
If you run the command below on the console, it will log you into the database.
mysql reporter
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