A Breath of Fresh Air - Turning Data into Improved Indoor Air Quality with Splunk

Stale and Stuffy meetings?

Suffering from severe headaches during meetings, feeling fatigued and lethargic due to lengthy powerpoints and monologues (you know, the ones that go on and on)? If that sounds oh so familiar to you, we have good news: it’s not you. And (usually) neither are your colleagues nor their presentations to blame. More often than not, the culprit for a “meeting hangover” is “bad”, stale air.

The higher the carbon dioxide (CO2) content in the air, the worse the air quality is. CO2 levels measured by ppm (part per million) determine the air quality.¹ CO2 levels exceeding 1.000 - 1.200 ppm can have adverse health outcomes that can cause drowsiness, fatigue and reduced cognitive performance (and a strong desire to clock off early).

Measuring CO2 concentration in the air

Not long ago I used Splunk to install a small system to measure the CO2 concentration in meeting rooms. The goal was to figure out how good or bad the air quality was during individual meetings and workshops to eventually point out the significance of ventilation. The system consisted of a simple USB-CO2 measuring device (approximate costs 70 €) and a Raspberry Pi (about 60 €) posing as a server that collects data and sends it to Splunk. You can find the long term results of my CO2-measurements in the panel below:

The end of the fiscal year (February 2020) - a time with plenty of meetings - as well as the beginning of the lockdown in March 2020 are clearly showing in the graphic.

Like a breath of fresh air - that you can monitor

Frequent indoor ventilation for improved air hygiene is a common recommendation.² However, the suggestion of “frequent ventilation” is just as vague as the advice to “wash your hands thoroughly”. Hygiene plans of schools across Germany highlight the importance of fresh air and advise frequent forced ventilation. Yet, the recommended minimum flow of forced ventilation varies from state to state. What’s more, it can be challenging to impose a one-size-fits-all regulation without further knowledge of classroom sizes and the number of students within them. 

CO2 measurements are a reliable means to determine the percentage of fresh air within a room. Fresh air outdoors contains approximately 400 ppm of CO2. The estimated amount of CO2 exhaled by a human is 40.000 ppm. Once the concoction reaches a proportion of > 1.000 ppm, it’s safe to assume that the indoor air quality is bad and improvements need to be made.

The data enables us to determine the ideal frequency of air movement and the efficiency of ventilation actions taken. Some rooms enjoy a constant supply of fresh air via an open door to the hallway or a ducted ventilation system as two examples. By measuring the CO2 levels, we are able to establish whether the air quality actually improves as there is a possibility that the newly introduced air is not as fresh as assumed.

Fortunately, we are able to measure the CO2 level with reasonably priced sensors and apply Splunk to safely collate relevant data, automatically analyse them real-time and issue appropriate alerts. Imagine teachers receiving notifications on their smartphones (or smart-watches/smart-devices) to alert them of CO2 levels within the classroom reaching critical levels and the need to ventilate. Or envision a scenario where all you need to do is scan a QR code (thanks to Splunk AR) on the door to receive information regarding the air quality in the room before you even enter it. Wouldn’t that be fantastic?

Here is a quote cited by the guidebook for indoor hygiene in school buildings of the German Environment Agency (Umweltbundesamt):

“Ventilation traffic lights can be an instrument to visualise air quality indoors since humans don’t possess a sensory organ that enables them to perceive the CO2 level in the air. This can be a means to put a “ventilation-behavioural training in practise ”.”³

An investment of just 150 € per room would allow for conference rooms, classrooms, offices and many other closed indoor facilities to become safer environments. Moreover, the negative side effects of stale air such as meeting hangovers, fatigue and reduced cognitive performance, as well as airborne droplet transmission can be reduced.

Example - Monitoring CO2 levels in classrooms

Let’s take a closer look at a specific example:

The following example dashboard of a (fictitious) school shows an overview of all classrooms and their current CO2 levels in real-time, as well as a prediction for the upcoming 10 minutes (if lacking a supply of fresh air). I’ve added weather data as well as the number of students/smartphones for an improved evaluation of the situation. The numbers in each room show the current readings. The traffic lights pertain to the forecast which allows for timely actions when traffic lights switch to yellow or red.

While most CO2 measuring devices already have their own built-in traffic lights, the application of Splunk addresses the following use cases:

1. Longterm storage of CO2 data
2. Real-time alerts on mobile devices
3. Possibility of forecasting air quality in the next X minutes and advance warning alerts
4. External data (e.g. meteorological data, number of students, events like window or door movement) can be added

Example - Forecast Visualisation

The following example shows a forecast visualisation which serves as the basis for our alarm set up. Should the “prediction”-line exceed 1.000 ppm within the next 30 minutes an alert will be triggered:

You could also create your own experiment using the Splunk Machine Learning Toolkit (MLTK) and refine your forecasts by using the “StateSpaceForecast”-algorithm.

The Machine Learning Toolkit can help you operationalise the model. When using searches it will be available with “apply model” and can be used for alerts.  

I would recommend creating additional mobile alerts and mobile dashboards, once dashboards are ready and alarms have been tested. This will enable registered users to receive alerts (as a push notification) directly onto their smartphones (or smart devices) and allows them to look at the corresponding dashboard. 

Here is an example of an iPhone push notification and the mobile dashboard:

Even augmented reality dashboards can be created in the easiest way. It won’t take more than a QR-code on the door or the window in order to access all relevant climate information. The viewer has the option to position the data at will:

Technical Implementation:

Ready to try it yourself? Below are short step-by-step instructions for the technical implementation of your automated Splunk CO2 measuring system:

Required hardware:

1. Mini CO2-Monitor (e.g. TFA Dostmann AirCO2ntrol or any other CO2 sensor that is readable through IC2 or USB) My personal experience with this device in continuous operation is very good: I’ve collected data over a period of 2 years without any outages. The display shows the data and small warning LED’s in familiar traffic light green, amber and red to indicate the hazard level which I believe is a big advantage.
2. Raspberry Pi 4 – of course. There’s Arduino and other types of minicomputers as well but what makes the Raspberry Pi so appealing to me is that it comes with all the capabilities of a full-blown Linux-server. Moreover, Splunk Universal Forwarder is available for Linux on the Armv6. If you’re planning to work on a bigger project with multiple rooms, you have the option to use more budget friendly Arduinos with connected CO2 sensors that transmit their data via HEC (HTTP Event Collector) to Splunk. 

Software

1. Linux on Raspberry Pi
2. A Python-script (monitor.py) to retrieve data via USB with a minor modification to ensure the data is written into a log file:

with open('/var/log/co2monitor.log', 'a') as out:

                    out.write(eventtime + "," "TMP,%3.1f" % (tmp) + "," "M01" + '\n')

                    out.write(eventtime + "," "CO2,%4i" %(co2)  + "," "M01" + '\n')

    You will need to deactivate the “decrypt”-function if your device (with newer firmware) is sending     unencrypted data. Alternatively, you could use a python-module.* 

      3. Splunk Universal Forwarder to forward the data. Another option is to send the data directly via HEC, but in case of a network outage, you wouldn’t have any buffer. 

      4. An accessible Splunk-server 

Installation

1. Install Splunk Universal Forwarder on Raspbian 
2. Set up script, modify and test 
3. Connect the CO2-measuring device via USB (with the provided cable) with Raspi 
4. Start the script: 

sudo nohup /opt/jobs/monitor.py /dev/hidraw0 &

   5. Set up Splunk Input: 

[monitor:///var/log/co2monitor.log]

disabled = false

sourcetype = metrics_csv

index = raspi

   6. On the Splunk-server:

•     Create dashboards 
•     Set up machine learning (or just predict) 
•     Use Cloud Gateway for Mobile und create mobile alerts and dashboards

Conclusion:

A small investment and a bit of Splunk know-how is all you need to analyse and monitor CO2 concentration data within enclosed spaces and make predictions based on it. In other words, it will enable you to turn data into better indoor air.

By implementing these types of systems, the likes of office managers and school heads would be able to ensure that the minimum requirements of internal ventilation and fresh air supply are met. This would guarantee that fatigue and meeting hangovers are a thing of the past and would contribute towards compliance with health and safety regulations in regards to ventilation. Monitoring and collating the data over a period of time would make these efforts documentable.      

<sup>1. Find out more about the concept of carbon dioxide sensors here: https://en.wikipedia.org/wiki/Carbon_dioxide_sensor 2.  https://www.epa.gov/coronavirus/ventilation-and-coronavirus-covid-19 3.  https://www.umweltbundesamt.de/sites/default/files/medien/publikation/long/3689.pdf
* Thanks to Alexander Sötz for the hint!</sup>

^{**This article has been translated and adapated from the German version: Gesund und wach bleiben – Wie ihr mit Splunk Daten in bessere Raumluft verwandelt}

----------------------------------------------------
Thanks!
Tomas Baublys