by acls us

Parsing TFL Tube Line Status with Pentaho

TFL Pentaho In a previous article I wrong about using TRL Tube Line Status via the old API (rather than the new Unified API), formatting and storing the data in Zabbix to be displayed on a Grafana Dashboard. In this article, I'm using the new TFL Unified API to grab TFL Tube Line Status in a JSON structure and parsing that data using Pentaho to produce a simple CSV file with a Date/Time, a Tube Line Name and a Status of the Tube Line.

Having signed up for access to the new TFL Unified API and having had a look at the example RESTful web calls for data, I fired up an example query to get the TFL Tube Line Statuses.

The first element of the JSON Array returned looks like this;

"$type": "Tfl.Api.Presentation.Entities.Line, Tfl.Api.Presentation.Entities",
"id": "bakerloo",
"name": "Bakerloo",
"modeName": "tube",
"disruptions": [],
"created": "2018-06-12T17:24:37.747Z",
"modified": "2018-06-12T17:24:37.747Z",
"$type": "Tfl.Api.Presentation.Entities.LineStatus, Tfl.Api.Presentation.Entities",
"id": 0,
"statusSeverity": 10,
"statusSeverityDescription": "Good Service",
"created": "0001-01-01T00:00:00",
"validityPeriods": []
"routeSections": [],
"$type": "Tfl.Api.Presentation.Entities.LineServiceTypeInfo, Tfl.Api.Presentation.Entities",
"name": "Regular",
"uri": "/Line/Route?ids=Bakerloo&serviceTypes=Regular"
"$type": "Tfl.Api.Presentation.Entities.Crowding, Tfl.Api.Presentation.Entities"

However, all I wanted was the TFL Tube Line Name "name": "Bakerloo" and the Status "statusSeverityDescription": "Good Service" (marked in yellow above). The following Pentaho Transformation does the work required


The step "Generate Rows" is just used to set the API URL to be used by the "HTTP Client" step which send the RESTful API request to the TRL Servers. The returned JSON data is passed to the "JSON Input" step which parses out the Line Name and Line Status data. The "Get System Info" step just added the current Date/Time that is sent with the parsed data to the "Text file output" step that write the CSV file.

The only "tricky" bit is parsing the returned JSON structure. This uses JSONPath definitions to tell the JSON parser step where to find the data in the JSON structure.

For the Line Name the JSONPath was "$.[*].name" which instructs the JSON parser to get the "name" from each top level array element. For the Line Status the JSONPath was "$.[*].lineStatuses[0].statusSeverityDescription" which instructs the JSON parser to get the "statusSeverityDescription" from the first element of the "lineStatuses" array from each top level array element.

The results being...

2018/06/22 15:12:41,"Bakerloo","Good Service"
2018/06/22 15:12:41,"Central","Good Service"
2018/06/22 15:12:41,"Circle","Good Service"
2018/06/22 15:12:41,"District","Good Service"
2018/06/22 15:12:41,"Hammersmith & City","Good Service"
2018/06/22 15:12:41,"Jubilee","Good Service"
2018/06/22 15:12:41,"Metropolitan","Good Service"
2018/06/22 15:12:41,"Northern","Good Service"
2018/06/22 15:12:41,"Piccadilly","Good Service"
2018/06/22 15:12:41,"Victoria","Good Service"
2018/06/22 15:12:41,"Waterloo & City","Good Service"



Asterisk/FreePBX caught by the CODECs


Asterisk FreePBX

Having just installed and migrated a client from an old Asterisk/FreePBX system running on CentOS 5 at Rackspace to a nice new installation on Ubuntu 16.04 at Amazon Lightsail and having deleted the old system as the testing all came out clean, 24 hours running passed and then strange reports of calls that just go dead after connecting. Most calls ran through OK but some particular called numbers rang (just once) were connected but then no audio at all.

Looking through the Asterisk logs didn't show anything to write home about. The FreePBX script ran through ok, started the SIP call via a SIP Trunk Provider, all the correct responses came back, the RTP audio stream was established but the client could not hear any audio on the call. After a while the call clears down. Nothing odd showing at all in the log.

Checked all the Firewall rules to see if anything might be getting in the way of the RTP audio stream but all the rules looked fine. Then it was time to look deeper and run a packet trace. This needed to capture the SIP and RTP conversations to a capture file to download and analyse via Wireshark. "tcpdump udp portrange 1024-65535 -vvvv -s 0 -w dump.pcap" to capture the traffic.

Loaded up the capture file in Wireshark and again every thing looked ok. The SIP handshaking to INVITE the call all showed OK, the RTP handshake and stream all looks OK. The great VOIP analysis tools in Wireshark allowed me to unpick the RTP audio stream and play it ok. In this particular case an announcement to say the offices were closed.

The announcement got me thinking "was the end point another VOIP system?". It turned out that the numbers that had been reported to me as having this problem were VOIP so I started to look at the CODECs involved. The SIP Trunk was setup, as it had been in the old Asterisk/FreePBX, to use ulaw and alaw CODECs. I noticed from the packet capture that the RTP stream had decided to use alaw (G 711 A). So, initially just as a test, I changed the SIP Provider Trunk to use ulaw (G 711 U) only. Tested some calls, all working ok now.

FreePBX Asterisk 13.1.0 with a SIP Trunk Provider making SIP/RTP connections to another VOIP system with an alaw (G 711 A) CODEC fails to decode the audio, change the Trunk to use a ulaw (G 711 U) CODEC, all is well.

Simple Grafana Dashboard for SugarCRM



A simple Grafana Dashboard created for a client using SugarCRM. This used a direct MySQL client connection to a hosted SugarCRM service with a backed MySQL database. This used the latest Grafana MySQL DataSource to access the MySQL Database.


Grafana Dashboard