On summer duty
BaardBouvet
July 6, 2018
Here at Sesam we’re using a Team Calendar in Confluence to register holidays. This is a simple tool that shows when people are on holiday for instance. This is how it looks.
This year I’m responsible for making sure we have enough people on work during the summer. When looking at the calendar I realised that it’s easy to see who is on holiday next week, but I couldn’t find a simple way to see who is not on holiday next week. My initial plan was to pull out pen and paper and just do this manually. But that’s not very fun. So I wanted to code it instead.
To-do:
- Get the holiday data from Confluence
- Generate “at work” events for each user
- Visualize the generated events
Getting the data from Confluence
I pulled up the dev console while browsing Confluence and saw that it called the Confluence REST api to pull down events as JSON. Unfortunately the API was called for a bunch of sub calendars (one for “leave”, “holiday”, etc) I considered doing the same until I saw the “Export to iCalendar” link. I clicked this and got a complete .ics file for the entire team calendar. It looked like this.
BEGIN:VCALENDAR
PRODID:-//Atlassian Confluence//Calendar Plugin 1.0//EN
VERSION:2.0
CALSCALE:GREGORIAN
[..]
BEGIN:VEVENT
DTSTAMP:20180705T144944Z
DTSTART;VALUE=DATE:20180622
DTEND;VALUE=DATE:20180630
SUMMARY:Baard H Rehn Johansen
CATEGORIES:travel
SUBCALENDAR-ID:b1518d2f-f990-44ba-84aa-e26d0ffba18d
PARENT-CALENDAR-ID:8602678d-bb38-4589-8fff-9b7e085556b1
PARENT-CALENDAR-NAME:
SUBSCRIPTION-ID:
SUBCALENDAR-TZ-ID:Europe/Oslo
SUBCALENDAR-NAME:Sesam kalender
EVENT-ID:4144
EVENT-ALLDAY:true
UID:20180418T083102Z-945927757@wiki.bouvet.no
DESCRIPTION:
ORGANIZER;X-CONFLUENCE-USER-KEY=ff80818140d573350140d57410540275;CN=Baard
H Rehn Johansen;CUTYPE=INDIVIDUAL:mailto:baard.johansen@sesam.io
CREATED:20180418T083102Z
LAST-MODIFIED:20180620T070433Z
ATTENDEE;X-CONFLUENCE-USER-KEY=ff80818140d573350140d57410540275;CN=Baard
H Rehn Johansen;CUTYPE=INDIVIDUAL:mailto:baard.johansen@sesam.io
SEQUENCE:2
X-CONFLUENCE-SUBCALENDAR-TYPE:travel
STATUS:CONFIRMED
END:VEVENT
[..]
END:VCALENDAR
Generating “at work” events
I pulled up my JS editor and found the node-ical library that could parse the ics file. For my plan to work I needed to find all the events for a single user so that I could generate events between these events. Even though everyone could register holiday for anyone else, the attendee field had high quality. The code to generate the “inverted” events was like this:
- Group events by attendee
- Sort the users events by start date
- For each of the users event, generate a new event that starts at the end of that event and lasts until the start of the next event
Visualizing the generated events
I had already opened the iCalendar file in my Thunderbird Mail Client and saw that the format was well suited for calendars. So I changed my script to generate an iCalendar file that contained the generated events. I opened the generated file in Thunderbird and voila, I had solved the problem I initially planned to solve with pen and paper. I proudly published the file I generated on Slack so everyone could benefit from it by importing it into their own mail clients.
Retrospective
Even though it perhaps took a little longer than the pen and paper approach, I had a reusable script that I could run whenever I needed to check who was at work during the summer. The script was a bit hackish because I didn’t separate the “inversion” logic and the iCalendar generation. A much bigger problem was that I realised that I had to publish this script on Slack every now and then, because people update the Team Calendar every now and then. I had to run this script periodically, cache the output and serve this to the other team members. I also had to make sure this process didn’t fail, otherwise the inverted calendar might be old and wrong. This was beginning to look like an integration problem.
Enter the Sesam Datahub
We have a Sesam Datahub here at Sesam for doing our own data integration, so I thought I should eat my own dogfood and try to migrate my calendar-stuff into Sesam before rolling out my ad hoc solution. I wrote up new To-do list:
- Get the calendar events into a Sesam dataset
- Generate new inverted events into a new dataset
- Publish the new dataset as an iCalendar endpoint
Get the calendar events into a Sesam dataset
Unfortunately Sesam doesn’t come bundled with an iCalendar parser, so I had to write the connector myself. I used the NodeJS template and moved the tiny bit of parser code from my old script there. I had to convert the parsed calendar into entities, and I ended up emitting one entity per event in the calendar. I intentionally avoided doing any other logic in the connector so that it can be reused in other projects. The can be found on the Sesam Community.
I added this connector as a system in Sesam.
{
"_id": "confluence",
"type": "system:microservice",
"docker": {
"environment": {
"COOKIE": "[confluence security cookies here]",
"URL": "https://wiki.bouvet.no/rest/calendar-services/1.0/calendar/export/subcalendar/8602678d-bb38-4589-8fff-9b7e085556b1.ics?os_authType=basic&isSubscribe=false"
},
"image": "sesamcommunity/ical-nodejs",
"port": 5000
}
}
I then had to add a pipe to read this data into a dataset.
{
"_id": "calendar",
"type": "pipe",
"source": {
"type": "json",
"system": "confluence",
"url": "/entities"
}
}
I now had the Team Calendar events in Sesam.
Generate inverted events into a new dataset
As part of the dog feeding I wanted to port my “inversion” logic from JS to Sesam DTL. First I had to implement the “group by attendee” part. This is a bit tricky, but it can be solved with a couple of pipes. First you pull out the unique attendees as a separate dataset.
{
"_id": "calendar-attendees",
"type": "pipe",
"source": {
"type": "dataset",
"dataset": "calendar"
},
"transform": {
"type": "dtl",
"rules": {
"default": [
["filter",
["matches", "*@sesam.io", "_S.attendee.val"]
],
["add", "_id",
["first", "_S.attendee.val"]
]
]
}
}
}
This allows us to use this a starting point for the next pipe and for each attendee hop to the original dataset and fetch all the events for that user. Next step is to implement the inversion logic. This requires looping over the events and be able to peek on the next event. I solved this by generating an object that contained both the current and next object during the the iteration. I also had to parse the timestamp string so that I could add 1 year to the last event that we don’t know when will end.
{
"_id": "inverted-calendar-per-attendee",
"type": "pipe",
"source": {
"type": "dataset",
"dataset": "calendar-attendees"
},
"transform": {
"type": "dtl",
"rules": {
"default": [
["add", "_events",
["sorted", "_.start",
["apply-hops", "calendar", {
"datasets": ["calendar c"],
"where": [
["eq", "_S._id", "c.attendee.val"]
]
}]
]
],
["create-child",
["apply", "invert",
["map",
["dict", "event",
["nth", "_.", "_T._events"], "next-event",
["nth",
["+", 1, "_."], "_T._events"]
],
["range", 0,
["count", "_T._events"]
]
]
]
]
],
"calendar": [
["copy", "*"],
["add", "_start",
["datetime-parse", "%Y-%m-%dT%H:%M:%S.%fZ", "_S.start"]
],
["add", "_end",
["datetime-parse", "%Y-%m-%dT%H:%M:%S.%fZ", "_S.end"]
]
],
"invert": [
["if",
["gt",
["length", "_S.event.description"], 1],
["add", "summary",
["concat", "_S.event.attendee.params.CN", ": Back from ", "_S.event.description"]
],
["add", "summary",
["concat", "_S.event.attendee.params.CN", ": Back at work"]
]
],
["if",
["is-null", "_S.next-event"],
[
["add", "uid",
["concat", "end--", "_S.event.uid"]
],
["add", "summary",
["concat", "_T.summary", " (after last registered leave)"]
],
["add", "end",
["datetime-plus", "year", 1, "_S.event._end"]
]
],
[
["add", "uid",
["concat", "_S.event.uid", "--", "_S.next-event.uid"]
],
["add", "end", "_S.next-event._start"]
]
],
["add", "start", "_S.event._end"],
["add", "type", "VEVENT"],
["add", "_id", "_T.uid"]
]
}
},
"sink": {
"track_children": true
}
}
This produced a dataset for each user with a list of that users inverted events. I flattened this into one big list of events with the following pipe.
{
"_id": "inverted-calendar",
"type": "pipe",
"source": {
"type": "dataset",
"dataset": "inverted-calendar-per-attendee"
},
"transform": {
"type": "emit_children"
}
}
Now I had a dataset for all the inverted events, but the format was JSON, not iCalendar.
Publish the new dataset as an iCalendar endpoint
Sesam can publish the data to JSON, RDF, CSV and other formats, but doesn’t come bundled with iCalendar. In my hacking script I just generated iCalendar on the fly during the inversion process, but this time I was forced to generate an intermediate representation.
Again, I used the nodejs template to generate the publisher. The publisher reads a dataset as JSON and generated a valid iCalendar file. Like in my first script, this generator currently only supports a limited set of iCalendar (uid, summary, start and end). This can later be expanded to support other iCalendar goodies. The connector can be found on the Sesam Community.
I added this as a new system on our Sesam.
{
"_id": "inverted-calendar-ical-endpoint",
"type": "system:microservice",
"docker": {
"environment": {
"JWT": "[ access token so the connector can access the url ]",
"URL": "https://[your subscription here]/api/datasets/inverted-calendar/entities?history=false&deleted=false"
},
"image": "sesamcommunity/ical-endpoint",
"port": 5000
}
}
This connector can then be accessed using the Sesam system proxy api as https://[your subscription here]/api/systems/inverted-calendar-ical-endpoint/proxy/ical
This is secured by default, but you can make this public by giving “Everyone” the “Read microservice proxy” permission on this system.
I sent out this url on Slack instead, and for fun added it as an external iCalendar in Confluence:
Summary
Playing with data is fun. There’s nothing wrong with writing hackish scripts, but they quickly grow and become hard to maintain. By using Sesam you are encouraged to make reusable components and build your integration using a standard model. This can be valuable in the long term.