Reverse Engineer City of Albuquerque Web Server Logs API

25 Feb

I recently stumbled across a Code For America experiment that shows live information from the City of Albuquerque Web Server. This is cool, but if you read this blog, you know I want the service! Give me that REST endpoint. I set out to reverse engineering their application and will show you two real-time endpoints with some different parameters.

The Application

The Code for America application is clearly hitting an endpoint. It’s just the way it has to work. So where is the endpoint? Well, a cursory glance at the code reveals two: realtime and historic. So hit the endpoint

https://shrouded-fjord-5635.herokuapp.com/realtime?

and you get the following error:

{“error”:{“errors”:[{“domain”:”global”,”reason”:”required”,”message”:”Required parameter: ids”,”locationType”:”parameter”,”location”:”ids”}],”code”:400,”message”:”Required parameter: ids”}}

Interesting. we need a parameter ids. Hit the endpoint with the parameter.

{“error”:{“errors”:[{“domain”:”global”,”reason”:”invalidParameter”,”message”:”Invalid value ”. Values must match the following regular expression: ‘ga:[0-9]+'”,”locationType”:”parameter”,”location”:”ids”}],”code”:400,”message”:”Invalid value ”. Values must match the following regular expression: ‘ga:[0-9]+'”}}

Now I know the value of

ids=ga: SOME NUMBER

The number is in the code, so I grabbed it. Reading the code, you can see the structure of the endpoint.

endpoint: function(){
return “/realtime?ids=ga:”+matrix.settings.profileId+”&metrics=rt:activeUsers&max-results=10”
}

So let’s give it a try.

{
“kind”: “analytics#realtimeData”,
“id”: “https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:48754&metrics=rt:activeUsers”,
“query”: {
“ids”: “ga:48754”,
“metrics”: [
“rt:activeUsers”
],
“max-results”: 1000
},
“totalResults”: 1,
“selfLink”: “https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:48754&metrics=rt:activeUsers”,
“profileInfo”: {
“profileId”: “48754”,
“accountId”: “86004”,
“webPropertyId”: “UA-86004-1”,
“internalWebPropertyId”: “97739”,
“profileName”: “2. http://www.cabq.gov All Traffic”,
“tableId”: “realtime:48754”
},
“columnHeaders”: [
{
“name”: “rt:activeUsers”,
“columnType”: “METRIC”,
“dataType”: “INTEGER”
}
],
“totalsForAllResults”: {
“rt:activeUsers”: “172”
},
“rows”: [
[
“172”
]
]
}

There you have it: 172 active users. We can pass another parameter

metrics=rt:pageViews

Then we see:

{“kind”:”analytics#realtimeData”,”id”:”https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:48754&metrics=rt:pageviews”,”query”:{“ids”:”ga:48754″,”metrics”:[“rt:pageviews”],”max-results”:1000},”totalResults”:1,”selfLink”:”https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:48754&metrics=rt:pageviews”,”profileInfo”:{“profileId”:”48754″,”accountId”:”86004″,”webPropertyId”:”UA-86004-1″,”internalWebPropertyId”:”97739″,”profileName”:”2. http://www.cabq.gov All Traffic”,”tableId”:”realtime:48754″},”columnHeaders”:[{“name”:”rt:pageviews”,”columnType”:”METRIC”,”dataType”:”INTEGER”}],”totalsForAllResults”:{“rt:pageviews”:”2172″},“rows”:[[“2172”]]}

I formatted the first result nicely and will just post JSON blobs so as not to take up to much space. Now you see 2172 views. My guess is that this is within either today or some time time frame of today.

What are people looking at? Lets change our metrics to pageviews and get the pagetitle. We also pass the largest possible result value.

metrics=rt:pageviews&dimensions=rt:pageTitle&max-results=10000

Right now, this is what we get.

{“kind”:”analytics#realtimeData”,”id”:”https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:48754&dimensions=rt:pageTitle&metrics=rt:pageviews&max-results=10000″,”query”:{“ids”:”ga:48754″,”dimensions”:”rt:pageTitle”,”metrics”:[“rt:pageviews”],”max-results”:10000},”totalResults”:454,”selfLink”:”https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:48754&dimensions=rt:pageTitle&metrics=rt:pageviews&max-results=10000″,”profileInfo”:{“profileId”:”48754″,”accountId”:”86004″,”webPropertyId”:”UA-86004-1″,”internalWebPropertyId”:”97739″,”profileName”:”2. http://www.cabq.gov All Traffic”,”tableId”:”realtime:48754″},”columnHeaders”:[{“name”:”rt:pageTitle”,”columnType”:”DIMENSION”,”dataType”:”STRING”},{“name”:”rt:pageviews”,”columnType”:”METRIC”,”dataType”:”INTEGER”}],”totalsForAllResults”:{“rt:pageviews”:”2127″},”rows”:[[“2015 Spring Green Waste Pick-up — City of Albuquerque”,”1″],[“4th Magic Treehouse: Dinosaurs after Dark — City of Albuquerque”,”1″],[“A Night in the 40’s: Big Band Swing — City of Albuquerque”,”1″],[“ABQ BioPark — City of Albuquerque”,”12″],[“ABQ RIDE Providing Free Transportation to Veterans with V.A. Hospital Cards — City of Albuquerque”,”5″],[“ABQ Recycles — City of Albuquerque”,”2″],[“ABQ View — City of Albuquerque”,”24″],[“ABQ Volunteers — City of Albuquerque”,”6″],[“ACH Services Information — City of Albuquerque”,”1″],[“About 311 — City of Albuquerque”,”1″],[“About Us — City of Albuquerque”,”2″],[“About the BioPark — City of Albuquerque”,”1″],[“About the Councilor — City of Albuquerque”,”1″],[“Activities & Education Programs — City of Albuquerque”,”1″],[“Activities Catalog — City of Albuquerque”,”1″],[“Address Atlas Pages — City of Albuquerque”,”2″],[“Address Query – Advanced Map Viewer — City of Albuquerque”,”5″],[“Address Report — City of Albuquerque”,”19″],[“Administration, Two AFSCME Unions Reach Agreement — City of Albuquerque”,”1″],[“Admission & Hours — City of Albuquerque”,”5″],[“Advanced Map Viewer User Guide — City of Albuquerque”,”1″],[“Advanced Map Viewer — City of Albuquerque”,”14″],[“Agendas from Greater Albuquerque Bicycling Advisory Committee Meetings — City of Albuquerque”,”1″],

Notice there were only 454 total results. Each record looks like

[“ABQ RIDE Providing Free Transportation to Veterans with V.A. Hospital Cards — City of Albuquerque”,”5″]

This is pageTitle comma PageViews as stated in the columnHeaders property below.

columnHeaders”:[{“name”:”rt:pageTitle”,”columnType”:”DIMENSION”,”dataType”:”STRING”},{“name”:”rt:pageviews”,”columnType”:”METRIC”,”dataType”:”INTEGER”}],

Where is the page with the title ABQ View – City of Albuquerque? Using pagePath we can find out.

{“kind”:”analytics#realtimeData”,”id”:”https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:48754&dimensions=rt:pagePath,rt:pageTitle&metrics=rt:pageviews&max-results=10000″,”query”:{“ids”:”ga:48754″,”dimensions”:”rt:pagePath,rt:pageTitle”,”metrics”:[“rt:pageviews”],”max-results”:10000},”totalResults”:704,”selfLink”:”https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:48754&dimensions=rt:pagePath,rt:pageTitle&metrics=rt:pageviews&max-results=10000″,”profileInfo”:{“profileId”:”48754″,”accountId”:”86004″,”webPropertyId”:”UA-86004-1″,”internalWebPropertyId”:”97739″,”profileName”:”2. http://www.cabq.gov All Traffic”,”tableId”:”realtime:48754″},”columnHeaders”:[{“name”:”rt:pagePath”,”columnType”:”DIMENSION”,”dataType”:”STRING”},{“name”:”rt:pageTitle”,”columnType”:”DIMENSION”,”dataType”:”STRING”},{“name”:”rt:pageviews”,”columnType”:”METRIC”,”dataType”:”INTEGER”}],”totalsForAllResults”:{“rt:pageviews”:”2173″},”rows”:[[“/311/311-Information/about-311″,”About 311 — City of Albuquerque”,”1″],[“/311/311-Information/contacting-311″,”How to Contact 311 — City of Albuquerque”,”1″],[“/311/311-Information/contacting-311/dialing-311/index.html”,”Dialing 311 — City of Albuquerque”,”1″],[“/311/resident-services”,”Online Resident Services — City of Albuquerque”,”1″],[“/311/resident-services/online-resident-services/folder_summary_view?b_start:int=15&-C=”,”Online Resident Services — City of Albuquerque”,”1″],[“/311/resident-services/online-resident-services/folder_summary_view?b_start:int=30&-C=”,”Online Resident Services — City of Albuquerque”,”1″],[“/a-z”,”Albuquerque A-Z — City of Albuquerque”,”27″],[“/a-z/a-z”,”Albuquerque A-Z — City of Albuquerque”,”7″],[“/about/offsite.html”,”Leaving http://www.cabq.gov External Link Disclaimer — City of Albuquerque”,”1″],[“/abq-apps”,”ABQ Apps — City of Albuquerque”,”1″],[“/abq-data”,”ABQ Data — City of Albuquerque”,”1″],[“/abq-view/index.html”,”ABQ View — City of Albuquerque”,”27″],[“/abq-volunteers”,”ABQ Volunteers — City of Albuquerque”,”9″],[“/abq-volunteers/index.html”,”ABQ Volunteers — City of Albuquerque”,”1″],[“/acl_users/credentials_cookie_auth/require_login?came_from=https://www.cabq.gov/abq-volunteers/volunteering-announcement-form”,”City of Albuquerque”,”1″],[“/airport”,”Airport — City of Albuquerque”,”7″],[“/airport/airlines-flight-services”,”Airlines — City of Albuquerque”,”7″]

Here is a single record:

[“/311/311-Information/about-311″,”About 311 — City of Albuquerque”,”1″]

And appending the URL to cabq.gov gives us:

Capture

 

Now you can see what people are looking at on the City of Albuquerque website. Try gathering statistics and compile the most popular page for a day as Code for America has done.

I said I was only going to show the real-time but I had to grab historic.

Historic Data

You can grab historic data by changing the endpoint to /historic. The metric values change to:

start-date

end-date

They are formatted as YYYY-MM-DD. You can also pass dimensions of:

nthMinute

nthHour

And here is an example of a session from 2015-02-21 to 2015-02-23 using Hours:

https://shrouded-fjord-5635.herokuapp.com//historic?ids=ga:48754&dimensions=ga:nthHour&metrics=ga:sessions&start-date=2015-02-21&end-date=2015-02-23

{“kind”:”analytics#gaData”,”id”:”https://www.googleapis.com/analytics/v3/data/ga?ids=ga:48754&dimensions=ga:nthHour&metrics=ga:sessions&start-date=2015-02-21&end-date=2015-02-23″,”query”:{“start-date”:”2015-02-21″,”end-date”:”2015-02-23″,”ids”:”ga:48754″,”dimensions”:”ga:nthHour”,”metrics”:[“ga:sessions”],”start-index”:1,”max-results”:1000},”itemsPerPage”:1000,”totalResults”:72,”selfLink”:”https://www.googleapis.com/analytics/v3/data/ga?ids=ga:48754&dimensions=ga:nthHour&metrics=ga:sessions&start-date=2015-02-21&end-date=2015-02-23″,”profileInfo”:{“profileId”:”48754″,”accountId”:”86004″,”webPropertyId”:”UA-86004-1″,”internalWebPropertyId”:”97739″,”profileName”:”2. http://www.cabq.gov All Traffic”,”tableId”:”ga:48754″},”containsSampledData”:false,”columnHeaders”:[{“name”:”ga:nthHour”,”columnType”:”DIMENSION”,”dataType”:”STRING”},{“name”:”ga:sessions”,”columnType”:”METRIC”,”dataType”:”INTEGER”}],”totalsForAllResults”:{“ga:sessions”:”48097″},“rows”:[[“000000″,”242”],[“000001″,”110”],[“000002″,”91”],[“000003″,”91”],[“000004″,”106”],[“000005″,”147”],[“000006″,”308”],[“000007″,”671”],[“000008″,”912”],[“000009″,”1098”],[“000010″,”1119”],[“000011″,”1019”],[“000012″,”1016”],[“000013″,”959”],[“000014″,”901”],[“000015″,”880”],[“000016″,”800”],[“000017″,”665”],[“000018″,”693”],[“000019″,”646”],[“000020″,”610”],[“000021″,”543”],[“000022″,”443”],[“000023″,”289”],[“000024″,”219”],[“000025″,”133”],[“000026″,”100”],[“000027″,”84”],[“000028″,”81”],[“000029″,”149”],[“000030″,”231”],[“000031″,”524”],[“000032″,”744”],[“000033″,”859”],[“000034″,”894”],[“000035″,”848”],[“000036″,”861”],[“000037″,”892”],[“000038″,”860”],[“000039″,”832”],[“000040″,”842”],[“000041″,”809”],[“000042″,”744”],[“000043″,”650”],[“000044″,”711”],[“000045″,”583”],[“000046″,”483”],[“000047″,”305”],[“000048″,”220”],[“000049″,”115”],[“000050″,”98”],[“000051″,”56”],[“000052″,”127”],[“000053″,”269”],[“000054″,”481”],[“000055″,”787”],[“000056″,”1269”],[“000057″,”1467”],[“000058″,”1595”],[“000059″,”1613”],[“000060″,”1462”],[“000061″,”1446”],[“000062″,”1528”],[“000063″,”1545”],[“000064″,”1343”],[“000065″,”995”],[“000066″,”792”],[“000067″,”742”],[“000068″,”812”],[“000069″,”728”],[“000070″,”508”],[“000071″,”302”]]}

A single records contains the hour and the number of sessions:

[“000064″,”1343”]

What to the People of Albuquerque want from their Government?

By analyzing what the people of Albuquerque search for on the City website, we can come to some conclusions as to what they deem important or what concerns they have. To do so, we need the pages they looked at over a period of time. We can use the historic endpoint and pass pageTitle to find out.

https://shrouded-fjord-5635.herokuapp.com//historic?ids=ga:48754&dimensions=ga:nthHour&metrics=ga:pageviews&dimensions=ga:pageTitle&sessions&start-date=2015-02-21&end-date=2015-02-23

{“kind”:”analytics#gaData”,”id”:”https://www.googleapis.com/analytics/v3/data/ga?ids=ga:48754&dimensions=ga:pageTitle&metrics=ga:pageviews&start-date=2015-02-21&end-date=2015-02-23″,”query”:{“start-date”:”2015-02-21″,”end-date”:”2015-02-23″,”ids”:”ga:48754″,”dimensions”:”ga:pageTitle”,”metrics”:[“ga:pageviews”],”start-index”:1,”max-results”:1000},”itemsPerPage”:1000,”totalResults”:3090,”selfLink”:”https://www.googleapis.com/analytics/v3/data/ga?ids=ga:48754&dimensions=ga:pageTitle&metrics=ga:pageviews&start-date=2015-02-21&end-date=2015-02-23″,”nextLink”:”https://www.googleapis.com/analytics/v3/data/ga?ids=ga:48754&dimensions=ga:pageTitle&metrics=ga:pageviews&start-date=2015-02-21&end-date=2015-02-23&start-index=1001&max-results=1000″,”profileInfo”:{“profileId”:”48754″,”accountId”:”86004″,”webPropertyId”:”UA-86004-1″,”internalWebPropertyId”:”97739″,”profileName”:”2. http://www.cabq.gov All Traffic”,”tableId”:”ga:48754″},”containsSampledData”:false,”columnHeaders”:[{“name”:”ga:pageTitle”,”columnType”:”DIMENSION”,”dataType”:”STRING”},{“name”:”ga:pageviews”,”columnType”:”METRIC”,”dataType”:”INTEGER”}],”totalsForAllResults”:{“ga:pageviews”:”129345″},”rows”:[[“\”A Senior I Know\” Essay Contest — City of Albuquerque”,”30″],[“\”On the Commons\” Magazine Features Albuquerque Open Space — City of Albuquerque”,”3″],[“\”We Love Our BioPark\” Video Contest Launches — City of Albuquerque”,”3″],[“\”Website Transparency Team\”: Week of April 18, 2011 — City of Albuquerque”,”1″],[“‘Pacific Coral Reef’ Exhibit Now Open — City of Albuquerque”,”1″],[“(Memorial) Tribute Tree Program — City of Albuquerque”,”3″],[“(not set)”,”13″],[“01-28-2015 – 15-11-104F – Follow-up – On-Call Contractors – Department of Municipal Development — City of Albuquerque”,”1″],[“01-28-2015 – 15-12-107F – Follow-up – Health and Social Service Centers – Department of Family and Community Services — City of Albuquerque”,”1″],[“02-17-12 CASE# 12-202 Whistleblower Complaint against the Solid Waste Management Department — City of Albuquerque”,”1″],[“02-28-07 — 06-04-105F Follow-Up – Environmental Health Department Expenditures — City of Albuquerque”,”2″],[“04-16-09 — 07-204 – Final Investigative Report – Stolen Vehicles, SWMD — City of Albuquerque”,”1″],[“06-25-12 Case# 12-213 Collision Involving a Motor Coach Operator — City of Albuquerque”,”1″],[“06-25-2014 Case # 204 Water Authority — City of Albuquerque”,”1″],[“1 – Educated, literate residents — City of Albuquerque”,”1″],[“1-11-10 RYAB Facilitated Meeting Notes — City of Albuquerque”,”1″],[“1.1 Adult Educational Achievement Rates — City of Albuquerque”,”2″],[“10 – Basic needs provided for — City of Albuquerque”,”1″],[“10-Year Spending Trends — City of Albuquerque”,”8″],[“10.1 Residents Living in Poverty — City of Albuquerque”,”1″],[“10.2 Unemployment Rate — City of Albuquerque”,”1″],[“11 – The public is safe — City of Albuquerque”,”1″],[“11-19-07 — 07-202 – Investigation – Alleged Excessive Overtime Claim, ABQ Ride Department — City of Albuquerque”,”1″],

Conclusion

I have given you the endpoints. It is up to you to parse out the data. The code for that is on almost every post on this blog. Just grab any AJAX request up to JSON.parse() and grab results.rows[x].[0 through 1]. Something like that should be close.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: