Netgear Router Logs in Qlik Sense

When my previous router went pop I replaced it with a Netgear R7000P. When I wanted to check it was totally secured I found that the logs were less than helpful. Naturally I turned to Qlik Sense for help. The app I created is available to download and this post describes how to set this up for your own Netgear router. This entire interactive view of your logs can be built using only free components.

Netgear Router Logs – The Challenge

When I suspected that port scans were finding their way into our home network I wanted to find out exactly what was being scanned and be sure I could check that everything had been blocked successfully. The Netgear R7000P (and other models) has a web interface and recent router log entries can be found in here.

The problems with the logs here is that the log is automatically cleared down when full, lost on a reboot, can not be exported easily and even when copied and pasted out into a file it does not load sensibly into any tool. The main challenge with the final part is that every different type of network event logged is in a different format.

In order to be able to get a sensible view of the logs, in a fully automated manner, I needed to think outside of the box just a little. The approach which I opted for was having the router email the log files out and Qlik Sense Cloud to pick up those logs then load them into a dashboard.

This blog post describes how to build this solution. You can also download the Qlik Sense app and simply configure it for your own router and email. All of this can be done using the free Qlik Cloud service and an email account.

Setting up GMail

In order to not have to plumb my own email credentials into the router I opted to set up a new e-mail account. In my case I set up a new user on my G-Suite domain, the solution should work with a standard GMail account also. You can sign up for one of these here https://www.google.com/gmail/about/. Using other email providers should also work, but I have not tested for these.

To enable GMail to work it needs to be configured to allow emails to be downloaded using IMAP. This is done under the cog icon in GMail, and then on the Settings menu.

In settings you need to select Forwarding and POP/IMAP. Look down a bit and find the place to switch on IMAP, tick this radio button.

Once this is ticked click on Save Changes. You can now come out of GMail.

Configuring the Netgear Router

These next steps refer to configuring the Netgear R7000P router. The Netgear Genie interface is built into a number of their routers though, so the settings should be similar. First you need to log into the web interface. You should be able to find this by going to https://routerlogin.net/ from within your network. Hopefully you will know or can locate your username and password (if it is the default user name and password take this opportunity to change it).

The email settings are found on the Advanced tab, under Security and Email.

Once in the email settings you need to tick Turn Email Notifications On and then fill in the details. For a GMail account you can set the sender and recipient to the GMail address, the SMTP server is smtp.gmail.com and the port is 465. You will need to authenticate using your email address and password. You can set the other options however you wish. Click Apply when done. Completed settings may look like this.

In order that you can test and get on straight away with building the Sense app you will want to navigate to Administration and Logs then click Send Log.

This should then be the router configured ready to feed our app with data on a regular basis.

Creating the Qlik Sense App

In order to have a Sense app that consumes this information you first need to have a Qlik Sense Cloud account, if you have a Qlik Community login you already have one of these, if not it is free to sign up for. This previous blog post talks about the Different Versions of Sense and this one describes how you can Sign Up For A Qlik Cloud Account. The app can be created in either Cloud version, I opted for Qlik Sense Cloud For Business as I want the app to reload automatically. I’ve not tested, but I believe it should work in the free Cloud version just as well.

If you are downloading the app from Qlik Community (link below) you can upload this into Sense Cloud (using then New App and Upload An App buttons). The IMAP connector and all data has been removed from the published app, for obvious reasons.

Loading the Netgear Router Logs

Once in the app go to the Data Load Editor (from the hamburger menu at the top left). Note you will not be able to use the Data Manager for this process.

Many connectors which previously required an installation of Qlik Web Connectors are now baked into the Sense Cloud UI. This opens up many opportunities for getting data from less obvious places, such as email. Hopefully these connectors will appear in Sense Enterprise soon also, for now QWC is the way to approach this.

In Qlik Cloud select the Create New Connection button and then Mailbox IMAP.

This will bring up a new dialog, where you need to enter IMAP Server name (imap.gmail.com for GMail) and the username and password for your account. Click Test Connection and if successful Create when done.

If this is all successful you should have a new connection on the right hand side. If you are working in the pre-created app you can simply replace the placeholder connection string with your new connection string, using the Insert Connection String button. If you are building from scratch use the Select Data button instead.

If you are selecting data you will need to populate the following dialog. If you are using GMail you will probably want Inbox in the Folder box, if not you can look up the correct value by doing a preview on the Folders query. The Search Query needs to be the subject line that appears on router logs. For the R7000P it is NETGEAR R7000P Log, other routers are likely to have similar. Click Preview Data when done.

The preview will show you the messages in the Inbox that match your search criteria. You should see the mail that you sent in the previous section. If you want to load all fields, to analyse when log messages are sent, you can leave all fields ticked and click Insert Script. To make the load of the log work however you only need the text field. Deselect the tick-box next to MessagesInFolder and tick the one next to text to select then and then click Insert Script.

The code will be inserted into your load script. The renaming of the fields is not helpful, so I would remove the field list from the LOAD section and replace it with an asterisk. Also, as we are going to do a resident load give the table a name (tmpRouterLogs:). The code should look like this.

If you are using the downloaded application you can simply change the connection name to match your own and modify the text in the search criteria as required.

Running the script at this point gives you the raw text from the logs, but not in a particularly useful format. The text of each email has many different log entries in a single field.

Parsing the Log Entries

There are a number of things which need to happen in our resident load and the subsequent preceding loads. I will go through the main ones here and you can reverse engineer the rest of them from the code.

First we need to split the logs, which have arrived in a single blob of text for each email, into individual rows. For this we can split the log entry based on the line feed character. This is done using the SubField statement.

We can then do a preceding load on these lines. We only want non-blank and non-duplicate rows though (as the router will always come back from a hard reset thinking it is 2015 we may lose some rows, but I can live with that). This is done by using a WHERE statement in the preceding load.

Now that we have just the log file rows we want, on a single line each, we can start parsing by splitting up the string. For instance, the date is always found after the third space from the right of the string, so can be derived like this:

The prefix of the log entry is always in square brackets at the start of the row, so can be obtained like this:

Working out the internal IP address, on the other hand, requires a bit more work as it may appear in different relative places in the log entry based on the type of log entry. In this case a nested if statement is required to apply the right logic to the right type of line:

Deriving the other fields is done in a similar way, across a couple more preceding loads. The complete code can be downloaded from here:
https://www.quickintelligence.co.uk/NetgearRouterLogLoadScript.txt

At the end of the script the temporary table and temporary fields just need to be dropped.

When the script is complete it needs to be run by clicking the Load Data button at the top right of the Data Load Editor.

Viewing the Data

Based on the entries in the logs I have from the router so far, I have parsed the following log types:

• Admin login
• DHCP IP
• DoS attack
• email failed
• email sent to
• Initialized
• Internet connected
• Internet disconnected
• LAN access from remote
• Service blocked
• Site allowed
• Site blocked
• Time synchronized with NTP server
• USB device attached
• USB device dettached

And those from those different logs the code parses out these fields:

• DateTime (and derivations)
• Log Type
• Internal IP
• Internal Port
• External IP
• External Port
• MAC Address
• Web Site
• Other Info

The other info field is a bit of a catch all, for anything else that comes up in the log file which doesn’t warrant it’s own dimension, such as firmware version. The majority of entries though contain one or more of the other dimensions.

I’m sure that there are other log types that I have not yet encountered and some of these may just work with the code as it stands, others may need the load script to be amended. The code that is there to parse those fifteen entry types though should provide guidance on how to do this for new ones.

Once the data has been loaded clicking on the Analysis tab at the top of the page will take you to a sheet where you can start building visualisations and tables. I’m not going to cover the basics of building apps in Sense, there is plenty of getting started information online and we have a two day starter course if you wish to learn with us.

You will want to create dimensions for all fields and a measure that counts the rows. It’s then simply a case of creating a view which gives a count of log entries by each of the key dimensions, like this:

And then on another sheet a table which gives all of the log entries in a format which is much easier to view, filter and export than from the routers own interface:

Both of these sheets, and the Quick Intelligence Generic Data Profiler, are included in the app you can download from Qlik Community.

The associative power of Qlik Sense means you can then explore your router logs by clicking on any value you see, be it in a table or on a chart, and immediately filter just to relevant rows. This can enable you to zero in on threats to your network with ease.

Limitations and Expansions

Presently there is no incremental load built into this script. This is an essential part of many Qlik applications. The reason it has been left out here is that the Store function is not available in Qlik Cloud. If you are implementing on Qlik Sense Enterprise you will almost certainly want to persist to QVD at the end of the load and then only request emails that have been received since the last log entry (or within a certain amount of time of this). You can then parse the new data and add the old on from QVD. If doing this remember you will need a unique key (I would suggest the raw log line entry) to do a WHERE EXISTS on.

Now the data is loaded there is a lot more that could be done with it in terms of different views. Validation and exception reporting spring to mind as obvious next steps.

The data can also be augmented by bringing in other data sources, perhaps a spreadsheet of known MAC addresses or IP addresses, so you can spot when an unknown device or external service has connected to your router.

Alerts can be handled using the connectors available in Sense also. The SMTP connector works in a similar way to the IMAP connector. This blog post talks about sending data directly from the Sense load script, Send Data from the Qlik Load Script, note however that parts of this will not work in Cloud due to using the STORE statement.

Application Download

The application has been uploaded to the Qlik Community and can be downloaded from here:
https://community.qlik.com/t5/Qlik-Sense-Documents/Qlik-Sense-App-Parse-Router-Logs-in-Qlik-Sense-Cloud/ta-p/1558684

You can find other applications we have uploaded under my Community profile, or on our examples page here:
Qlik Sense and QlikView Examples

If you find any of our apps useful, please like and share and all of that good stuff.

If you have any comments there is space for them below. I would be particularly interested to hear from anyone who gets this working over other Netgear models, or even totally different routers.

Thanks for reading.