How can I get a list of routers in NFA that do not have any active interfaces in a specific period of time?

Products

• Network Flow Analysis

Issue/Introduction

How can I get a list of routers in NFA that do not have any active interfaces in a specific period of time?

 

 

Environment

NFA 10.x and up

Resolution

Below are the steps to generate a list of routers that do not have any interfaces that have any flow data in the last week.  You can change 604800 to any amount of seconds to adjust this to any time frame you are looking for:

 

1. Open a command prompt on the NFA Console server.
2. Run the command below:

mysql -P3308 -D reporter -unetqos -pnetqos -t -e "select distinct inet6_ntoa(routerAddress), devicename, id from routers where id not in (select routerid from interfaces where lastflow >= (unix_timestamp() -604800)); " > RoutersWithoutFlow1week.txt

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

 

Additional Information

There are numerous other variations of commands that can created and sent and output provided.  For example to following can be used to provide a list of Devices, their name and the last time the interface was seen in the last 24 hours, and write the output to a text file.

mysql -P3308 -D reporter -unetqos -pnetqos -t -e "select inet6_ntoa(r.routeraddress) as 'Device IP', r.sysname as 'Device Name', i.name as 'Interface Name', from_unixtime(i.lastflow) as 'Last Seen' from routers r inner join interfaces i on r.id=routerid where i.lastflow >= (unix_timestamp() -86400) order by routeraddress;" > NewListOfInterfacesLastSeenInLast24Hours.txt

 

You can adjust this based on the data you require.