Agile leadership and more

Mobile phone tracing for the office

Sven Sommerfeld -

When the Corona pandemic hit Europe 2020 one of the first measures was that all of our employees will work from home office. One of the biggest challenges though was that every week the following question came up by the employees: "When can we finally get back to the office so that we can see our colleagues face to face?"

Of course we did not take any action until the pandemic slowed down during the summer months and then slowly brought the employees back to the office on a voluntary basis.

Being back in the office for 1-2 days a week was really nice but brought a new challenge: "Who was in the office when a positive case is reported within our company?"

First action was that we recommended everyone to install the Corona tracing app of Germany on their phone. With this the employees will be informed if they have exposure but it would have not helped us to give a full attendee list to the health centers if needed.

So we did what most companies do: We started out with a Excel file where everyone had to enter his or her attendance. The problem with this was, that not all employees followed the "order" to enter their attendance and a lot was done "after the fact" they have been in the office. Which was typically a delay of 2-3 days.

I though that being an IT-company using an Excel for this does not make sense, this should be automated with no effort to the employees.

After some thinking I came to the point that only company owned devices (cell phones, laptops, ...) which everyone always carries with them are connected to our network. Guest are in a separated (by IP Range) WIFI network. So the following idea occurred to me: "Why not store the mac addresses of the connected devices and the date this device was in the office". This way we are not storing personal information and we have an automated way to figure out who was in the office at what day (not what time!). The mac addresses we already have since these devices are company owned and we need it for our inventory. On top we of course need a consent from all employees to be GDPR compliant. Since the data was not looked at to track attendance and is only used if their is a positive case reported to inform everyone impacted, all employees loved the idea and signed up for it.

So here is a small solution on the implementation of the mac address tracing app.

Architecture

For the architecture I opted for a FOSS backend which is typically headless CMS: Cockpit CMS. It is easy to install, comes with a beautiful REST API, is stable, has a good admin UI and is very fast. A bash script triggered by a cronjob will collect the connected mac addresses and store it to Cockpit. The cronjob will also take care of the deletion after 2 weeks. The following picture illustrates this in a simple way.

Mobile phone tracing for the office

Install Cockpit CMS

As a first step you will need to install Cockpit CMS. For this you will need a web server (Apache or NGINX) and PHP. In this case I will use Apache since the NGINX config is a little more tricky and I will cover it in a different post. I also assume you are doing this on a Linux machine e.g. an Ubuntu.

The following commands will take care of the Apache and PHP installation. I am also installing git here, as this will help us pull Cockpit later.

sudo apt update
sudo apt install apache2 php git

Next step is to download Cockpit and store it in your document root (which is typically /var/www/html/). Best is to create a new directory for it (e.g. cockpit) and install it in their.

cd /var/www/html
git clone https://github.com/agentejo/cockpit.git
sudo chown www-data:www-data -R cockpit

As you can see, the git clone create will create a new folder cockpit for which we will give the owner rights to www-data.

Now that this is setup navigate to http://localhost/cockpit/install to finish the installation.

When done with the installation as next step you have to set up a collection to store the data. What we want to store is the date and the mac address: So we create a collection called ConnectedMACs with two fields:

  • date
  • mac

Now you need to get the Cockpit token which is necessary to call the Cockpit API. The token can be generated under Cockpit -> Setting -> API Access ->Add custom key.

That's it. Of course you can also use the Cockpit Docker image, if you are familiar with Docker.

The tracing script

The tracing script will run a command to get all connected devices of a network, filter out the MAC address, check if the MAC is already stored, store the MAC address and delete all stored addresses after 14 days.

In order to do all this additional software is needed:

  • nmap: To get all attached devices to the network
  • curl: To call a RESTful web service
  • jq: To parse the RESTfull service call result
sudo apt update
sudo apt install nmap jq curl

So finally, here is the script:

#!/bin/sh
#$1 IP Address to lookup in the format 192.168.0.0/24
#$2 API URL until the /api => e.g. http://localhost/cockpit
#$3 Cockpit token
token=$3
url=$2

#nmap to lookup all mac addresses of the network
nmap -sP -n $1 | while read -r line ; do
    type=`echo $line | cut -d ' ' -f 1`

    #check if the line is a mac address
    if [ "$type" = "MAC" ]; then 
        mac=`echo $line | cut -d ' ' -f 3`
        today=`date +"%Y-%m-%d"`

        #check if mac address is already stored
        result=`curl -X POST \
            -H "Cockpit-Token: $token" \
            -H "Content-Type: application/json" \
            -d '{"filter": { "date": "'$today'", "mac": "'$mac'" } }' \
            $url/api/collections/get/ConnectedMACs`
        total=`echo $result | jq -r '.total'`
        
        #if the total property of the result is 0, it is not yet stored
        if [ "$total" = 0 ]; then
            #store the mac
            postdata=`curl -X POST \
                -H "Cockpit-Token: $token" \
                -H "Content-Type: application/json" \
                -d '{"data": { "date": "'$today'", "mac": "'$mac'" } }' \
                $url/api/collections/save/ConnectedMACs`
            success=`echo $postdata | jq -r '._created'`
            if [ $success != "" ]; then
                echo "Successfully added "$mac
            else
                echo "Error while storing"
            fi
        fi
    fi
done

#delete all older than 14 days
xdaysago=`date '+"%Y-%m-%d"' -d "-14 days"`
curl -X POST \
    -H "Cockpit-Token: $token" \
    -H "Content-Type: application/json" \
    -d '{"filter": {"date": {"$lte":'$xdaysago'}}}' \
    $url/cockpit/api/collections/remove/ConnectedMACs

Store this script somewhere and make it executable:

sudo chmod +x /path/to/script.sh

In the script you can see that three parameters are needed to run it:

  1. IP Address range to lookup (format xxx.xxx.xxx.xxx/xx, all hosts in 192.168.0.0 is represented with 192.168.0.0/24)
  2. The URL of the cockpit installation (https://localhost/cockpit)
  3. The token to call the Cockpit API (as described previously)

So here is a sample call:

/path/to/script.sh 192.168.0.0/24 http://localhost/cockpit xxxxxxxxxxxxxx

After the script has run (typically it takes 3-5 seconds, depending on the size of your network) you will the see the results in Cockpit:

(Please note that of course non of our employees is working on a Saturday, these MACs have been taken from my private network)

Last but not least: Run it automatically

In order to run the script automatically, you have to set up a cronjob. Open your crontab editor with:

crontab -e

And add the following line at the end of the file:

*/15 7-18 * * 1-5 /path/to/script.sh 192.168.0.0/24 http://localhost/cockpit xxxxxxxxxxxxxx

This will execute the script every 15 minutes between 7am and 6pm from Monday to Friday.

That's it, hope this will help you. I'd be glad to hear from you about this solution.

Site note

Of course at our company we fully support to work remotely. Some of our employees prefer to work from the office though as per their situation at home. Everything in the office is regulated by our Covid hygiene regulation.