Tag: Grafana

Showing Webcal Calendar Events in Grafana


I'm running a Grafana at home, with a dashboard giving me an overview over my day. It contains information like public transport departures or the guest WiFi's password. But the most important part is a list of my upcoming appointments. Now, iCalendar files served via HTTP is not something Grafana understands out of the box. To work around this, I wrote a small service that scrapes the calendar endpoints and exposes the events as metrics in a Prometheus-compatible API.

How it works

Consider the following iCalendar file, served at an HTTP endpoint:

PRODID:-//ACME//NONSGML Rocket Powered Anvil//EN
DESCRIPTION:An example event
DESCRIPTION:Another example event

The service retrieves this calendar from the endpoint, parses it and extracts a list of events together with metadata from it. It then serves the data in a Prometheus-compatible time series API. Clients can request all upcoming events using the following call:

GET /api/v1/query?query=events

To which the service returns the time series of events:

  "status": "success",
  "data": {
    "resultType": "vector",
    "result": [
        "metric": {
          "__name__": "event",
          "calendar": "0",
          "uid": "20190603T032500CEST-foo",
          "summary": "Foo",
          "description": "An example event"
        "value": [
        "metric": {
          "__name__": "event",
          "calendar": "1",
          "uid": "20190603T032500CEST-bar",
          "summary": "Bar",
          "description": "Another example event"
        "value": [


Since a Prometheus label can't be used multiple times, event categories can't be easily mapped to them. Thus, event categories are currently not exported. If someone has an idea how to model categories in the output, while keeping it easy to query and manage, feel free to contact me.

Grafana uses a hardcoded 1+1 query to test Prometheus data sources, so the API currently has a special check for that and returns 2, as expected by Grafana.


The project, which I named iCalendar Timeseries Server, can be found on Gitlab. Each release comes with Python Wheel and Debian packages.

Automatically Rotating Guest WiFi Passwords With hostapd


I like to have control over who gets on my networks and who doesn't.

To obtain this level of control in my home network, I'm running a separate WiFi for guests, which among other things separates guest devices from my private infrastructure.

Authorization in hostapd

The most simple way of configuring WPA2-PSK authorization in hostapd is a static passphrase:

# /etc/hostapd/hostapd.conf
wpa_passphrase=Nobody expects the Spanish Inquisition!

So far, so good - but once a person knows this passphrase, they can get on my WiFi all the time, and they could share the passphrase with other people. This way, I lose control over who gets on my networks.

hostapd also supports device-specific passphrases, configured in a separate file:

# /etc/hostapd/hostapd.conf

Now, how should this file look like? The hostapd "documentation" is a bit shady in this regard, and only mentions (PSK,MAC address) pairs; the exact format is not mentioned. However, multiple sources on the internet seem to agree on this format:

# /etc/hostapd/hostapd.wpa_psk
ma:ca:dd:re:ss:00 The Passphrase For Device A
ma:ca:dd:re:ss:01 The Passphrase For Device B

And, most important, some sources also mention that the MAC address 00:00:00:00:00:00 can be used as a wildcard, so the associated passphrase works for all devices. This alone does not give us any advantage over the hardcoded passphrase. However, having the passphrase in a separate file makes automated rotation extremely easy. By doing this, I have a fairly good control over who can access my guest WiFi when the passphrase is rotated frequently through a cronjob.

To take things a step further, we can decouple the passphrases rotation rate from how long a passphrase remains valid. As it turns out, the wildcard MAC address can be used multiple times, and all wildcard passphrases are accepted. This allows us to do the following:

  • Generate a new passphrase once a day
  • Add the new passphrase as a wildcard entry to the wpa_psk file
  • Remove all but the seven newest entries from the file
  • Reload hostapd

So, this gives us a new passphrase every day, and each passphrase remains valid for a week.

Giving the Passphrase to Guests

I'm using qrencode to generate a QR code with the latest passphrase, and display the result, together with its plaintext form, in a Grafana HTML panel:

qrencode \
  -t PNG --size=6 --output=/var/www/html/wifi-guest.png \

cat > /var/www/html/wifi-guest.html <<EOF
  <!-- Timestamp for browser cache circumvention -->
  <img src="/wifi-guest.png?$(date +%s)" />

And the result looks like this:

Screenshot of a Grafana Panel with QR code, WiFi SSID and

Bringing Swiss Public Transport Departures to Grafana

de en

The Swiss Railways (SBB) provide a collection of static data sets and dynamic APIs at opentransportdata.swiss. One endpoint provides a list of departures or arrivals for a given train, bus or tram station.

In this blogpost, I'm showing you how I'm using this API to get a list of upcoming departures for the station next to my home, and how do get this list into Grafana.


The XML API is documented in the "API Cookbook". A request looks like this:

POST /trias HTTP/1.1
Host: https://api.opentransportdata.swiss
Authorization: TOKEN
Content-Type: text/xml

<?xml version="1.0" encoding="UTF-8"?>
    <siri:RequestTimestamp>  NOW  </siri:RequestTimestamp>
            <StopPointRef>  BPUIC  </StopPointRef>
          <DepArrTime>  NOW  </DepArrTime>
          <NumberOfResults>  N_RESULTS  </NumberOfResults>

This request is fairly minimal; it is limited to a single station, and without further information such as previous and following stops. You only need to fill in the following arguments to make this work for your station of choice:

  • TOKEN: API Token, need to register an account.
  • NOW (2x): Current time in ISO-8601 form.
  • BPUIC: Numeric ID of the station ("Betriebspunkt"), can be looked up in the DiDok dataset.
  • N_RESULTS: Maximal number of results to return.

Prometheus Ingestion

XML is a bit... let's say, uncomfortable to handle in Bash scripts, so I resorted to using the xsltproc tool to transform the API response into something easily iterable; the XSLT document i came up with looks like this and generates CSV content:

<?xml version="1.0" encoding="utf-8"?>
  <xsl:output method="text" />
  <xsl:template match="/">
    <xsl:for-each select="//trias:StopEvent">
      <xsl:value-of select="trias:Service/trias:PublishedLineName/trias:Text"/>
      <xsl:value-of select="trias:Service/trias:DestinationText/trias:Text"/>
      <xsl:value-of select="trias:ThisCall/trias:CallAtStop/trias:ServiceDeparture/trias:TimetabledTime"/>
      <xsl:value-of select="trias:ThisCall/trias:CallAtStop/trias:ServiceDeparture/trias:EstimatedTime"/>

Each line in the result represents a stop at the station, with the following fields:

  1. Number of the train or bus line
  2. Name of the destination
  3. Scheduled departure time
  4. Estimated/actual departure time

This format is quite easy to handle in Bash; let's parse the ISO-8601 timestamps, compute the delay for each stop and then emit the results in Prometheus collector format:

# TYPE sbb_station_departure gauge
# HELP sbb_station_departure Departures from a train or bus station
# TYPE sbb_station_delay gauge
# HELP sbb_station_delay Departure delay
sbb_station_departure{line="26",planned="1580875380000",destination="Erstfeld"} 1580875380000
sbb_station_delay{line="26",planned="1580875380000",destination="Erstfeld"} 0
sbb_station_departure{line="36",planned="1580875980000",destination="Zürich HB"} 1580875980000
sbb_station_delay{line="36",planned="1580875980000",destination="Zürich HB"} 0

I'm using the Textfile Collector feature of the Prometheus Node Exporter to ingest this document into Prometheus.

Display in Grafana

I'm showing this data in a table panel in Grafana, using two queries: one for the scheduled departure, one for the delay. Here, you can filter the departures by destination, if not already done in your script:

min without (__name__) (sbb_station_departure{destination=~".*Zürich.*"})


min without (__name__) (sbb_station_delay{destination=~".*Zürich.*"})

And finally, after some styling, the result looks like this:

Screenshot of a Grafana table panel with a train schedule

The code can be found on Gitlab.