Are there a mysql queries I can use to get the Flow Statistics page data?

Products

• CA Network Flow Analysis (NetQos / NFA)

Issue/Introduction

Are there a mysql queries I can use to get the Flow Statistics page data?

Environment

Component : NQRPTA - REPORTERANALYZER

Resolution

You can run the Mysql query below on the NFA Console to get the data from the Flow Statistics table for each Harvester, just substitute the unix timestamps at the end of the query for the start and end time.

mysql reporter -unetqos -pnetqos

SELECT routerAndIfCount.harvesterId as harvesterId, routerAndIfCount.address as ipAddress, routerAndIfCount.description as description, routerAndIfCount.routerCount as routerCount, routerAndIfCount.interfaceCount as interfaceCount, min(f.flowRate) as minFlowRate, max(f.flowRate) as maxFlowRate, CAST(avg(f.flowRate) as UNSIGNED) as avgFlowRate, CAST(percentile(flowRate, 95)as UNSIGNED) as pct95FlowRate, sum(f.droppedFlows) as droppeFlows, sum(f.droppedPackets) as droppedPackts, sum(f.mapFailures) as mapFailures, sum(f.noFlowsFound) as noFlowsFound, sum(f.headerFailures) as headerFailures, sum(f.routerReboots) as routerReboots FROM ( SELECT count(DISTINCT(a.RouterId)) as routerCount, count(a.IfIndex) as interfaceCount, h.harvesterId as harvesterId, h.address as address, h.description as description FROM harvesters h INNER JOIN agents_physical_view a ON h.address = a.HarvesterAddress GROUP BY h.address) routerAndIfCount INNER JOIN flow_stats f ON routerAndIfCount.harvesterId=f.harvesterId WHERE f.pointTime >= 1584403200 and f.pointTime <= 1584563770 GROUP BY routerAndIfCount.harvesterId ORDER BY routerAndIfCount.harvesterId;

To get the list of routers for a specific Harvester you can run the query below using the HarvesterID of the Harvester you want to get the router data for.

select id, min(flowRate) as minIFFlows, max(flowRate) as maxIFFlows, avg(flowRate) as avgIFFlows, percentile(flowRate, 95) as pct95IFFlows, name, ip, interfaces from (select (select count(interfaces.id) from interfaces where interfaces.routerid = routers.id ) as interfaces, routers.ID as id, (sum(interfaceflows.InFlows + interfaceflows.OutFlows)/15) as flowRate, routers.sysName as name, INET6_NTOA(routers.routerAddress) as ip from interfaceflows, routers where interfaceflows.endtime >= 1584520860 and interfaceflows.endtime <= 1584567660 and routers.harvesterID = 3 and interfaceflows.AgentID in (select agents_all_view.ID from agents_all_view where agents_all_view.routerid = routers.id) group by routers.id, interfaceflows.endtime) x group by id;

Additional Information

If you would like to export the results to a CSV file you can run the command like below from a regular command prompt without logging into mysql first.


mysql -t -e -D reporter -unetqos -pnetqos  "SELECT routerAndIfCount.harvesterId as harvesterId, routerAndIfCount.address as ipAddress, routerAndIfCount.description as description, routerAndIfCount.routerCount as routerCount, routerAndIfCount.interfaceCount as interfaceCount, min(f.flowRate) as minFlowRate, max(f.flowRate) as maxFlowRate, CAST(avg(f.flowRate) as UNSIGNED) as avgFlowRate, CAST(percentile(flowRate, 95)as UNSIGNED) as pct95FlowRate, sum(f.droppedFlows) as droppeFlows, sum(f.droppedPackets) as droppedPackts, sum(f.mapFailures) as mapFailures, sum(f.noFlowsFound) as noFlowsFound, sum(f.headerFailures) as headerFailures, sum(f.routerReboots) as routerReboots FROM ( SELECT count(DISTINCT(a.RouterId)) as routerCount, count(a.IfIndex) as interfaceCount, h.harvesterId as harvesterId, h.address as address, h.description as description FROM harvesters h INNER JOIN agents_physical_view a ON h.address = a.HarvesterAddress GROUP BY h.address) routerAndIfCount INNER JOIN flow_stats f ON routerAndIfCount.harvesterId=f.harvesterId WHERE f.pointTime >= 1584403200 and f.pointTime <= 1584563770 GROUP BY routerAndIfCount.harvesterId ORDER BY routerAndIfCount.harvesterId;" > FlowStatisticsByHarvester.csv


mysql -t -e -D reporter -unetqos -pnetqos "select id, min(flowRate) as minIFFlows, max(flowRate) as maxIFFlows, avg(flowRate) as avgIFFlows, percentile(flowRate, 95) as pct95IFFlows, name, ip, interfaces from (select (select count(interfaces.id) from interfaces where interfaces.routerid = routers.id ) as interfaces, routers.ID as id, (sum(interfaceflows.InFlows + interfaceflows.OutFlows)/15) as flowRate, routers.sysName as name, INET6_NTOA(routers.routerAddress) as ip from interfaceflows, routers where interfaceflows.endtime >= 1584520860 and interfaceflows.endtime <= 1584567660 and routers.harvesterID = 3 and interfaceflows.AgentID in (select agents_all_view.ID from agents_all_view where agents_all_view.routerid = routers.id) group by routers.id, interfaceflows.endtime) x group by id;" > FlowStatisticsByRouter.csv