How to export CSV report for flows per second for all routers

Products

• CA Network Flow Analysis (NetQos / NFA)

Issue/Introduction

We need to export all flows per second data for all routers in our NFA inventory.

 

I can export one at a time at the Interfaces > Flows page, under Flows there is an Export to CSV option, but how can export this data for all interfaces?

Environment

Release : 9.3

Component : NQRPTA - REPORTERANALYZER

Resolution

You can use the query below which is is similar to what is displayed on the Interface -> Flows page for individual interfaces.

If you are going to do this for all devices it will return a lot of data.

The two unix timestamps in bold below are the start and end time, you just need to update that with a unix timestamp for the time frame that you want to report on.

mysql -D reporter -t -e "select a.routeraddress, a.ifindex, a.name , a.id, from_unixtime(((1568834400 DIV 900) * 900) + (ceiling((i.ENDTIME - 1568834400) / 900) * 900)) UtcTimeStamp, cast(sum(1) as unsigned int) RecordCount, sum(i.InFlows) FlowsIn, sum(i.OutFlows) FlowsOut, sum(i.InFlows + i.OutFlows) FlowsTotal from interfaceflows i join agents_all_view a where i.AgentID = 61 and i.ENDTIME > 1568834400 - 900 and i.ENDTIME <= 1572466800 group by ceiling((i.ENDTIME - 1568834400) / 900) order by ENDTIME desc;" > FlowPerSecond.csv

This will create a csv file in the directory where you run the command.

It will have the Router Address, ifindex, name of the interface, the time period for the data point, flows in, flows out, and total flows.