Posts filed under Programming

Cisco ISE MAC Addresss Database Clean Up

Imagine having 15.000+ MAC addresses in a Cisco ISE database. All these MAC addresses are used to gain access to wireless networks protected with WPA2-PSK and MAC-filtering. But how to make sure that they are all (still) valid?

Remove MAC Addresses After Change In Authentication

Finally, the time has come to implement 802.1x on the wireless network for a substantial amount of these devices. These devices are consist mainly of Windows machines or Thin Clients. Both of those are managed through either the Microsoft Active Directory or a Thin Client Management Suite. So, applying setting related to 802.1x are pretty straight forward to distribute. There are however some Windows / Thin client devices that will remain on the MAC-filtering wifi networks for numerous reasons.

After a few tests the migration of the new 802.1x devices has started, but is leaving us with a MAC Address database filled with addresses that can be removed, since they are no longer used…. But how to do that? Cisco ISE has a lot of features, and is capable of generating rich reports about almost everything. However it has no way of reporting on dot1x devices that might still remain in the MAC address database as well. That is where I had to become creative.

First I explored the Cisco ISE Monitoring API, but that only gives active connections. There’s no way of exploring past (successful) authentications/authorizations. I needed a way to get current and past successful dot1x authentications and compare the MAC addresses associated with those entries to the MAC address database, and remove those from that database.

Eventually, I found two paths to accomplish this; First through the reporting module. There you can export all RADIUS authentications to CSV. Filtering these results in Excel, or through Python scripting, you are able to extract the MAC Addresses that successfully authenticated with dot1x. Feed these MAC addresses to a script and remove them through the Cisco ISE ERS API. Or if you’ve got nothing else to do; do it by hand.

The other path is by following the syslog output and parsing that feed. The downside to this is that you have to have syslog file access or add an additional syslog server to Cisco ISE that you may access (e.g. your scripting machine). The syslog version makes a a bit more tricky, since the (syslog)log lines are very long and you have to combine the correct lines to get the full message. Parsing CSV is much easier, so I followed that path first.

Dormant/Obsolete MAC Addresses

Another issue with static MAC addresses (and even local accounts) is that they tend to remain indefinitely in the MAC database. Lang after devices have been decommissioned, the MAC address remains. Which leaves a security hole to be exploited.

By using the generated ‘RADIUS Authentications’ reports over a longer time (e.g. 90 days) you can do a cross reference with MAC addresses in the database and recent successful authentications of that MAC address.
There are some caveats though;

  1. you need a session-timeout on the network (either statically defined on the network device) or by RADIUS return attribute, so that devices have to re-authenticate periodically. Otherwise you might not see a valid device in the logging and removed it by mistake.

  2. RADIUS Reporting goes only 30 days back, so you have to combine several (scheduled) reports to achieve a longer time span. There used to be a custom time frame option, but seems to have disappeared in version 2.6

Update Python Netaddr OUI Database

For a small project I needed to validate ~1500 MAC addresses on validity and their vendor Organizational Unique Identifier id (OUI). So a bit of Python scripting was in order.

I used a regular expression for basic MAC address validation, and the netaddr module to check the OUI of the MAC Address. A simple example of the code is shown below

#!/usr/bin/env python3

import netaddr as na
import re

_mac = '88-E9-FE-1F-65-7D'
if re.match('[0-9a-f]{2}([-:]?)[0-9a-f]{2}(\\1[0-9a-f]{2}){4}$', _mac.lower()):
    print(f'{_mac} - {na.EUI(_mac).oui.registration().org}')

Checking this MAC address online gives a normal result.


Python (or specifically netaddr) not so much, so there some work to be done. The error clearly shows that the OUI for that MAC address is not registered (in netaddr’s local database).


The problem is that the OUI ‘database’ from netaddr is (extremely) out-dated, so recently assigned OUI’s are not available, and result in Python script errors.

Unfortunatelly, the netaddr documentation doesn’t give any hint on how to update this database. Some searching on the local filesystem showed that there is a oui.txt file within the directory structure of netaddr (which in my case can be seen in the error shown above).

The latest oui.txt (~0.5MB larger than the netaddr version) can be downloaded @ IEEE (the organization were hardware vendors can request new OUI’s). The file location is:
I downloaded the file and replaced the original netaddr version. Running the code again gave no solution, since I got the same error. So back to the drawing board.

In the same directory as the oui.txt is a file called oui.idx. This file contains the decimal value of the OUI, and an offset. It turns out that the netaddr codeused this idx file to quickly skip to the actual vendor information in the oui.txt file. And since my idx file was based on the old oui.txt the vendor could still not be found.

The idx file cannot be found on the internet. It’s not something IEEE provides. It’s a file generated from the information in the oui.txt file.

Solution: In the netaddr directory where the oui.txt and oui.idx is located is a script. Run that script, and it creates a new idx file based on the oui.txt file in that directory (as shown in the following example).

myhost:eui myname$ pwd
myhost:eui myname$ ls -la
total 12856
drwxr-xr-x   9 myname  admin      288 Jan 26 13:37 .
drwxr-xr-x  11 myname  admin      352 Jan 26 13:37 ..
-rw-r--r--   1 myname  admin    24990 Jan 26 13:37
drwxr-xr-x   4 myname  admin      128 Jan 26 13:37 __pycache__
-rw-r--r--   1 myname  admin    95467 Jan 26 13:37 iab.idx
-rw-r--r--   1 myname  admin  2453271 Jan 26 13:37 iab.txt
-rw-r--r--   1 myname  admin     9500 Jan 26 13:37
-rw-r--r--   1 myname  admin   419098 Jan 26 13:37 oui.idx
-rw-r--r--   1 myname  admin  3566144 Jan 26 13:37 oui.txt

myhost:eui myname$ curl --output oui.txt
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 4039k  100 4039k    0     0   370k      0  0:00:10  0:00:10 --:--:--  437k

myhost:eui myname$ python3 
myhost:eui myname$ ls -la
total 14336
drwxr-xr-x   9 myname  admin      288 Jan 26 13:37 .
drwxr-xr-x  11 myname  admin      352 Jan 26 13:37 ..
-rw-r--r--   1 myname  admin    24990 Jan 26 13:37
drwxr-xr-x   4 myname  admin      128 Jan 26 13:37 __pycache__
-rw-r--r--   1 myname  admin    95467 May 10 12:14 iab.idx
-rw-r--r--   1 myname  admin  2453271 Jan 26 13:37 iab.txt
-rw-r--r--   1 myname  admin     9500 Jan 26 13:37
-rw-r--r--   1 myname  admin   485973 May 10 12:14 oui.idx
-rw-r--r--   1 myname  admin  4136058 May 10 12:14 oui.txt
myhost:eui myname$ 

After that the output of my script was the following:

/usr/local/bin/python3.7 /Volumes/Python_Scripts/
88-E9-FE-1F-65-7D - Apple, Inc.

Process finished with exit code 0
Posted on May 10, 2019 and filed under Programming, Annoying.

Enhancing Sonoff TH16 Functionality and Domoticz Integration

In my previous blogpost, the Sonoff worked, but was lacking a manual override. The switch could only be triggered by Domoticz. Since it also has a physical push button (connected to GPIO0 (D3)), it can be switched by hand. All that needs to be done is:

  1. Create a new switch device in the Sonoff
  2. Enable 'Rules' in the Tools / advanced settings
  3. Create a rule
  4. Change the On/Off commands in the switch parameters in Domoticz
Posted on January 1, 2018 and filed under Hardware, Programming, Raspberry Pi, Tips'n Tricks, Domotica.

Flashing the Sonoff TH16 Wireless Switch

The Sonoff TH16 is an inexpensive piece of hardware that can be controlled over WiFi. Apart from the switch (that's capable of handling electrical currents up to 16A) there's an interface for temperature and humidity. The actual temp/humid sensor is sold separately (in most cases).

Posted on December 31, 2017 and filed under Gadgets, Hardware, Programming, Raspberry Pi, Tips'n Tricks, Domotica.

Installing Python Matplotlib On MacOS Sierra

I recently 'upgraded' to MacOS Sierra (Apple's latest Operating System) by doing a clean install. This resulted in a couple of challenges, including some software that could not be installed, and for which I had to find some alternatives.

Another issue I ran into is that some Python3 scripts with matplotlib wouldn't run, because matplotlib wouldn't install correctly.
I could 'pip' all I wanted, but the result was always:

$ pip3 install matplotlib
The following required packages can not be built: freetype

Some googling pointed me to some articles that freetype is/was a part of the XQuartz (X11) software that's no longer (pre)installed on MacOS Sierra. And in the past I have always upgraded my OS. The times that I did a clean install on this machine.... Must have been ages ago.

After some frustrating hours of trying to get this 'freetype' thing installed, I ran into an article on which solved my issue finally.

First I installed 'homebrew'.

$ /usr/bin/ruby -e "$(curl -fsSL"

After that I installed pkg-config and freetype:

$ brew install pkg-config

$ brew install freetype

And finally, I was able to successfully install matplotlib:

$ pip3 install matplotlib