Welcome to the follow-up post to Flying on-time with Trellis! At the end of Part 1 I mentioned I would link my flight history with this dataset so I could map my own journey in addition to showing overall statistics. Now that the April 2017 data has been released I have a good 6 months of my personal travel history to show. With over 10hrs of delays, April was my motivation to start Splunking this kind of data!
Purpose: Use lookup editor, trellis and drilldown to show my flight history.
Splunk Version: Splunk 6.6 Data Sources: FAA Airline Statistics
Apps: Federal Aviation Administration, Missile Map, Lookup Editor
Related Blog Posts: Dashboard Digest Episode 6 - Travelling on Time with Trellis, Discovered Intelligence Blog Post on Trellis, What's New in Splunk 6.6
So what does my past 6 months of travel look like? Well, I’ve spent a lot of time in the airport..
There are 3 useful features I’ll be pointing out in this post.
- Lookup Editor was used to easily link my personal flight history to the overall FAA dataset.
- UI Drilldown allowed me to set tokens to interactively show a new trellis panel with extra flight delay details.
- Trellis layout to depict delays by Destination over time.
First, in order to link my flight history with the FAA data set I created a csv file in the lookup editor. All I needed was to list a few details to make sure I could join each flight uniquely. This included the flight number, date, origin and destination. Some flights didn’t exist in the FAA data set due to being a small carrier so I added “missing flight” columns to account for them. I saved the file and called it sl_flight_history.csv
The larger FAA data set contains about 12 million events for the past 2-3 years. Refer to Part 1 to see what kind of analytics I created from just that data set. To join my 55 flights to the larger data set I used the following SPL. Note the use of “tstats” which takes advantage of indexed extractions due to the FAA data being a structured data set coming from CSVs. This allows me to analyze hundreds of millions of rows in seconds!
I’ve now focused on my 55 flights out of the millions in the data set. Dashboarding time! I wanted to break down where my delays have been, for how long, and what reasons.
To take it a step further however, I wanted to see what airports had the most delay time in April. Because the dashboard was already pretty full I figured it would be a good time to add a drilldown. Thanks to the new UI Drilldown capabilities it was super easy. Upon clicking any of the data in the “Delays by Reason” or “Delays by Carrier” charts I could bring up a new chart using Trellis to show by airport statistics.
First I created the chart I want to drill down to, a Trellis layout of total delayed minutes by Airport over time. To learn more about creating a Trellis layout see Part 1 of this blog post.
Turns out much of my delays existed in going to SFO which was due to runway re-paving and reduced runways available when there is fog. Oy!
Next I edited the charts to add drilldown and set a token when clicked. This token could then be used with the “depends” clause in Simple XML to interactively show the trellis chart when clicked.
Then edit the source XML and add the "depends" clause to the trellis chart.
Imagine if the airlines customer service representatives could quickly map out each customer’s journey, history and experience including every minute of delay and why. NOW imagine being more proactive and having Splunk actively monitor every customer’s journey including their real-time social media responses, call wait times, and promotion effectiveness all through just one platform!
That’s it for today, Happy Splunking!
See previous Dashboard Digest series below:
Dashboard Digest – Episode 1
Dashboard Digest – Episode 2 – Waves
Dashboard Digest – Episode 2 Part Deux – Hurricane Matthew
Dashboard Digest – Episode 3 – Splunk HQ Water and Energy
Dashboard Digest – Episode 4 – NFL Predictions
Dashboard Digest – Episode 5 – Maps!
Dashboard Digest – Episode 6 – Traveling on Time with Trellis