Civic Hax

A blog probably about FOIA and civic hacking.

That Time the City of Seattle Accidentally Gave Me 32m Emails for 40 Dollars

October 19, 2018 — Matt Chapman

Background

In my last post, I wrote about my adventure of requesting metadata for both phone calls and emails from the City of Chicago Office of the Mayor. The work there - and its associated frustration - sent me down a path of sending requests throughout the US to both learn whether these sorts of problems are systemic (megaspoiler: they are) and to also start mapping communication across the United States. Since then, I’ve submitted over a hundred requests for email metadata across the United States – at least two per state.

The first large batch of requests for email metadata were sent to the largest cities of fourteen arbitrary states in a trial run of sorts. In the end of that batch, only two cities were willing to continue with the request - Houston and Seattle. Houston complied surprisingly quickly and snail mailed the metadata for 6m emails.

Seattle on the other hand...

The Request

On April 2, 2017 I sent this fairly boilerplate request to Seattle's IT department:

For all emails sent to/from any Seattle owned email address in 2017, please provide the following information:

1. From address
2. To address
3. bcc addresses
4. cc addresses
5. Time
6. Date

Technically this request can done with a single line powershell command. At a policy level, though, it usually gets a lot of pushback. Seattle's first response included a bit of gobsmackery that I’ve almost become used to:

Based on my preliminary research, there have been 5.5 million emails sent and 26.8 million emails received by seattle.gov email addresses in the past 90 days. This is a significant amount of records that will need to be reviewed prior to sharing them with you. Do you have a more targeted list of email addresses you might be interested in? If not, I will work to find out how long review will take and will be in touch.

Of course, I still want the records, so -

I would like to stick with this request as-is for all ~32m emails.  Since this request is for metadata only, the amount of review needed should be relatively small.

Fee Estimate of 33 Million Dollars

A week later, I received this glorious response. Each paragraph is interesting in itself, so let’s break most of it down piece by piece.

Rewording of request

This acknowledges receipt of your public disclosure request C012032-041017 received on April 03, 2017 regarding:

    All emails sent to/from any Seattle owned email address in 2017 including metadata:1. From address2. To address3. bcc addresses4. cc addresses5. Time6. Date

Notice the change of language from the original wording. Their rewording completely changes the scope of the request so that it's not just for metadata, but also the emails contents. No idea why they did that.

Salary Fees

With that being said, Seattle IT estimates spending 30 seconds to two minutes to review each email.  It has been estimated that this work will take approximately 320 years of staff time at an expense of $33 million in salary.

Wot.

Normally, a flustered public records officer would just reject a giant request for being for “unduly burdensome”… but this sort of estimate is practically unheard of. So much so that other FOIA nerds have told me that this is the second biggest request they've ever seen. The passive aggression is thick. Needless to say, it's not something I'm willing to pay for!

The estimate of 30s per metadata entry is also a bit suspect. Especially with the use of Excel, which would be useful for removing duplicates, etc.

Storage Fees

We estimate that this request contains 8-10 terabytes of information, for which we could need to stand up an FTP server through which the requester will be able to download cleared email meta data.  As allowed under RCW 42.56.120, we would charge the requester for the actual copying costs of fulfilling this request.  Based on the Seattle IT cost model used for internal City charge backs, the anticipated cost to the requester is $2,480 per year plus $2.11 per gigabyte of storage.  We are still working on the storage requirements for this effort.  If we assume 10 TB of storage, this would require $21,606.40/year in requester fees.

Heh. Any sysadmin can tell you that The costs of storage doesn’t exactly come from the storage medium itself; administration costs, supporting hardware, etc, are the bulk of the costs. But come on, let’s be realistic here. There’s very little room for good faith in their cost estimates – especially since the last time a single gigabyte cost that much was between 2002 and 2004.

That said – some other interesting things going on here:

  1. Their file size estimation is huge. For comparison, that Houston’s email metadata dump was only 1.2GB.
  2. The fact that they mention “meta data” [sic] implies that they did acknowledge that the request was for metadata.
  3. Seattle already uses Amazon S3 to store public records requests’ data. At the time, S3 was charging $.023/GB

Continue anyway?

At this time, the City anticipates that it will be able to provide a first installment of records on or about May 29, 2017. However, please note that this time estimate may change depending on the clarification you provide and as we continue to process your request.  If the City does not hear from you within 30 days, the City will consider your request closed.

Oddly, they don't actually close out the request and instead ask whether I wanted to continue or not. I responded to their amazing email by asking how many records I'd receive on May 29th, but never received an answer back.

Reversal of the Original Cost Estimate

On June 5, they sent a new response admitting that their initial fee estimation was wrong, and asked for $1.25 for two days’ (out of three months) of records:

At this point I can send you an exel spreadsheet with the data points you are requesting.  The cost for the first installment is $1.25 for emails sent or received on January 1 and 2, 2017.  Because the spreadsheet does not contain the body of the email and just the metadata that you requested, no review will be necessary, and we'll be able to get this information to you at a faster pace than the 320 years quoted you earlier. 

Because they're asking for a single check for $1.25 for just two days’ worth of metadata – and wouldn’t send anything until that first check came in, my interpretation is that they’re taking a page from /r/maliciouscompliance and just making this request as painful as possible just for the simple sake of making it difficult. So in response, I preemptively sent them fourteen separate checks. The first thirteen checks were all around ~$1.25. That seemed to work, since they never asked for a single payment afterwards.

From there, my inbox went mostly silent for two months, and I mostly forgot about the request, though they eventually cashed all of the checks and made me an account for their public records portal.

SNAFU

Fast forward to August 22, when I randomly added that email account back to my phone. Unexpectedly, it turned out they actually finished the request! And without a bill for millions of dollars! Sure enough, their public records request portal had about 400 files available to download, which all in all contained metadata for about 32 million emails. Neat!

Problem though... they accidentally included the first 256 characters of all 32 million emails.

Here are some things I found in the emails:

  1. Usernames and passwords.
  2. Credit card numbers.
  3. Social security numbers and drivers licenses.
  4. Ongoing police investigations and arrest reports.
  5. Texts of cheating husbands to their lovers.
  6. FBI Investigations.
  7. Zabbix alerts.

In other words... they just leaked to me a massive dataset filled with intimately private information. In the process, they very likely broke many laws, including the Privacy Act of 1974 and many of WA's own public records laws. Frankly, I'm still at a loss of words.

It’s hard to say how any of this happened exactly, but odds are that a combination of request’s rewording and the original public records officer going on vacation led to a communication breakdown. I don’t want to dwell on the mistake itself, so I’ll stop it at that.

Side note to Seattle's IT department - clean up your disks. You shouldn't have that many disks at 100%!

Raising the Issue

I responded as passively as possible in the hopes that they’d catch their mistake on their own:

The responsive records are not consistent with my request and includes much more info than I initially requested. Could you please revisit this request and provide the records responsive to my initial request?

Their response:

The information that you requestedis located in columns: 
From address = column J
To address = column K
bcc address = column M
cc address = column L
Time and date = column R  of the reports.  
The records were generatedfrom a system report and I am unable to limit the report to generate only thefields you requested.  The City has no duty to create a record that doesnot exist.  As such, we have provided all records responsive to yourrequest and consider your request closed.

Disregarding the fact that they used a very common tactic of denying information on the basis that its disclosure would require the creation of new records… they didn’t get the point. I explained what information they leaked, and made it very clear how I was going to escalate this:

Please address this matter as if it was a large data breach. For now, I will be raising this matter to the WA Office of Privacy and Data Protection. None of the files provided to me have been shared with anyone else, nor do I have any future intention of sharing.

Their response:

Thank you for your email and bringing this inadvertent error to our attention so quickly. We have temporarily suspended access to GovQA while we look into the cause of this issue. We are also working on reprocessing your request and anticipate providing you with corrected copies of the records you requested through GovQA next week. In the meantime, please do not review, share, copy or otherwise use these records for any purpose.

We are sorry for any inconvenience.

Phone Call

Not too long after that, after contacting some folks on Seattle’s Open Data Slack, I found my way onto a conference phone call with both Seattle’s Chief Technology officer and their Chief Privacy Officer and we discussed what happened, and what should happen with the records. They thanked me for bringing the situation to their attention and all that, but the mood of the call was as if both parties had a knife behind their back. Somewhere towards the end of the call, I asked them if it was okay to keep the emails. Why not at least ask, right?

Funny enough, in the middle of that question, my internet died and interrupted the call for the first time in the six months I lived in that house. Odd. It came back ten minutes later, and I dialed back into the conference line, but the mood of the call pretty much 180’d. They told me:

  1. All files were to be deleted.
  2. Seattle would hire Kroll to scan my hard drives to prove deletion.
  3. Agreeing to #1 and #2 would give me full legal indemnification.

This isn't something I'm even remotely cool with, so we ended the call a couple minutes later, and agreed to have our lawyers speak going forward.

Deleting the Files

After that call, I asked my lawyer to reach out to their lawyer and was pretty much told that Seattle was approaching the problem as if they were pursuing Computer Fraud And Abuse (CFAA) charges. For information that they sent. Jiminey Cricket..

So, I deleted the files.

Most of what happened next over a month or so was mostly between their lawyer and mine, so there’s not really that much for me to say. Early on I suggested that I write an affidavit that explains what happened, how I deleted the files, and I validated that the files were deleted. They mostly agreed, but still wanted to throw some silly assurance things my way – including asking me to run a bash script to overwrite any unused disk space with random bits. I eventually ran zerofree and fstrim instead, and they accepted the affidavit. No more legal threats from there.

Seattle’s Reaction

About a week after the phone call, a Seattle city employee contacted Seattle’s KIRO7 about the incident. In KIRO7's investigation, they learned that, Seattle hadn’t sent any disclosure of the leak - something required by WA’s public records request law. Only after their investigation did Seattle actually notify its employees about the emails leak. Link to their story (video inside).

A week later, another article was published by Seattle’s Crosscut which goes into a lot of detail, including some history of Seattle's IT department. This line towards the bottom still makes me laugh a little:

The buffer against potential legal and administrative chaos in this scenario is only that Chapman has turned out to be, as Armbruster described him, a "good Samaritan." Efforts to track down Chapman were not successful; Crosscut contacted several Matthew Chapmans who denied being the requester.

On January 19th, Seattle's CTO, Michael Mattmiller gave his resignation. Whether his resignation is related to the email leak is hard to say, but I just think the timing makes it worth mentioning.

Finally – The Metadata

Starting January 26th, Seattle started sending installments of the email metadata I requested. So far they've sent 27 million emails. As of the writing of this post, there are only two departments who haven’t provided their email metadata: the Police Department and Human Services.

You can download the raw data here.

Some things about the dataset:

  1. It’s very messy – triple quotes, semicolons, commas, oh my.
  2. There are a millions of systems alerts.
  3. For seattle.gov → seattle.gov communication, there are two distinct metadata records.

In any case, it's still somewhat workable, so I've been working on a proof of concept for its use in the greater context of public records laws. Not ready to talk much about it yet, so here's is a gephi graph of one day's worth of metadata. Its layout is Yifan Hu and filtered with a k-core minimum of 5 and a minimum degree of 5:

Please reach out to me if you'd like to help model these networks.

One Last Thing: Legislative Immunity Kerfuffle

This last section might not be related, but the timing is interesting, so I feel it’s worth mentioning.

On February 23 - between the first installment of email metadata and the second - WA’s legislature attempted to pass SB6617, a bill which removes requirements for disclosure of many of their records – including email exchanges - from WA’s public records laws. What’s particularly interesting about this events of this bill is that it took less than 24 hours from the time it was read for the first time to the time that it passed at both the House and Senate and sent to the Governor’s office.

Seattle Times wrote a good article about it.

Thankfully, after the WA governor’s office received over 6,300 phone calls, 100 letters, and over 12,500 emails, the governor ended up vetoing the bill. Neat.

It's hard to say if that caused any sort of delay, but after a month and a half of waiting:

How are the installments looking? I saw that there was some recent legislative immunity kerfuffle around emails. Is that related to any delays?

And got this response:

Good news.  The recent Washington state legislative immunity kerfuffle will not impact your installments.  We have fixed the bug that was impacting our progress and are now on our way. In fact, I'll have more records for you this week.

A month later, they started sending the rest.

What’s Next?

The work done throughout this post has led to a massive trove of information that ought to be enormously useful in understanding the dynamics of one the US's biggest cities. A big hope in making this sort of information available to the public is that it will help in changing the dynamic of understanding what sorts of information is accessible.

That said, this is just one city of many which have given me email metadata. As more of it comes through, I’ll be able to map out more and more, but the difficulty in requesting those records continues to get in the way.

Once I get some of these bigger stories out of the way, I’ll start writing fewer stories and write more about public records requesting fundamentals – particularly for digital records.

Next post will be about my ongoing suit against the White House OMB for email metadata from January 2017. This past Wednesday was the first court date - where the defendent's counsel never showed up.

Hope you enjoyed!

Tags: seattle, foia, kerfuffle, metadata

A tale about requesting Chicago’s Mayor’s Office’s phone records.

August 28, 2018 — Matt Chapman

Intro

Back in 2014, I had the naive goal of finding evidence of collusion between mayoral candidates. The reasoning is longwinded and boring, so I won't go into it. My plan was to find some sort of evidence through a FOIA request or two for the mayor's phone records, find zero evidence of collusion, then move onto a different project like I normally do. What came instead was a painful year and a half struggle to get a single week's worth of phone records from Chicago's Office of the Mayor.

Hope you enjoy and learn something along the way!

Requests to Mayor's Office

My first request was simple and assumed that the mayor's office had a modern phone. On Dec 8, 2014, I sent this anonymous FOIA request to Chicago's Office of the Mayor:

Please attach all of the mayor's phone records from any city-owned phones (including cellular phones) over the past 4 years.

Ten days later, I received a rejection back stating they didn't have any of the mayor's phone records:

A FOIA request must be directed to the department that maintains the records you are seeking.

The Mayor’s Office does not have any documents responsive to your request.

Then, to test testing whether it was just the mayor whose records their office didn't maintain, I sent another request to the Office of the Mayor - this time specifically for the FOIA officer's phone records and got the same response.

Maybe another department has the records?

VoIP Logs Request

An outstanding question I had (and still have, to some extent) is whether or not server logs are accessible through FOIA. So, to kill two birds with one stone, I sent a request for VoIP server logs to Chicago's Department of Innovation and Technology (DoIT):

Please attach in a standard text, compressed format, all VoIP server logs that would contain phone numbers dialed between the dates of 11/24/14 and 12/04/14 for [the mayor's phone].

Ten days later (and five days late), I received a response that my request was being reviewed. Because they were late to respond, IL FOIA says that they can no longer reject my request if it's unduly burdensome - one of the more interesting statutory pieces of IL FOIA.

The phone... records?

A month goes by, and they send back a two page PDF with phone numbers whose last four digits are redacted:

...along with a two and a half page letter explaining why. I really encourage you to read it.

tl;dr of their response and its records:

  1. They claim that the review/redaction process would be extremely unduly burdensome - even though they were 5 days late!
  2. The pdf includes 83 separate phone calls, with 45 unique phone numbers.
  3. The last four digit of each phone number is removed.
  4. Government issued cell phones’ numbers have been removed completely for privacy reasons.
  5. Private phone numbers aren’t being redacted to the same extent as government cell phones.
  6. Government desk phones are redacted.

Their response is particularly strange, because IL FOIA says:

"disclosure of information that bears on the public duties of public employees and officials shall not be considered an invasion of personal privacy."

With the help of my lawyer, I sent an email to Chicago explaining this... and never received a response. Time to appeal!

Request for Review

In many IL FOIA rejections, the following text is written at the bottom:

You have a right of review of this denial by the IL Attorney General's Public Access Counselor, who can be contacted at 500 S. Second St., Springfield, IL 62706 or by telephone at (217)(558)-0486. You may also seek judicial review of a denial under 5 ILCS 140/11 of FOIA.

I went the first route by submitting a Request for Review (RFR). The RFR letter can be boiled down to:

  1. They stopped responding.
  2. Redaction favors the government personnel’s privacy over individuals’, despite FOIA statute.
  3. Their response to the original request took ten days.

Seven Months Later

Turns out RFRs are very, very slow. So - seven months later, I received a RFR closing letter with a non-binding opinion saying that Chicago should send the records I requested. Their reason mostly boils down to Chicago not giving sufficient reason to call the request unduly burdensome.

A long month later - August 11, 2015: - Chicago responds with this, saying that my original request was for VoIP server logs, which Chicago doesn’t have:

[H]e requested "VoIP server logs," which the Department has established it does not possess. As a result, the City respectfully disagrees with your direction to produce records showing telephone numbers, as there is not an outstanding FOIA request for responsive records in the possession of the Department.

Sure enough, his phone is pretty ancient:

Image Source

Do Over

And so after nine months of what felt like wasted effort, I submitted another request that same day:

Please attach [...] phone numbers dialed between the dates of 11/24/14 and 12/04/14 for [the Office of the Mayor]

Two weeks later - this time with an on-time extension letter - I'm sent another file that looks like this:

The exact same file. They even sent the same rejection reasons!

Lawsuit

On 12/2/2015, Loevy & Loevy filed suit against Chicago’s DoIT. The summary of the complaint is that we disagree with their claim that to review and redact the phone records would be extremely unduly burdensome. My part in this was waiting while my lawyer did all the work. I wasn't really involved in this part, so there's really not much for me to write about.

Lawsuit conclusion

Six months later, on May 11, 2016, the city settled and gave me four pages of phone logs - most of which were still redacted. Some battles, eh?

Interesting bits from the court document:

...DoIT and its counsel became aware that, in its August 24, 2015 response, DoIT had inadvertently misidentified the universe of responsive numbers. DoIT identified approximately 130 additional phone numbers dialed from the phones dialed within Suite 507 of City Hall, bringing the total to 171

...FOIA only compels the production of listed numbers belonging to businesses, governmental agencies and other entities, and only those numbers which are not work-issued cell phones.

...DoIT asserted that compliance with plaintiff request was overly burdensome pursuant to Section 3(g) of FOIA. On those grounds —rather than provide no numbers at all — DoIT redacted the last four digits of all phone numbers provided

...in other words, they "googled" each number to determine whether that number was publically listed, and, if so, to whom it belonged. This resulted in the identification of 57 out of the 137 responsive numbers...

Lawsuit Records

All in all, the phone records contained:

  1. 171 unique phone log entries: 57 unredacted and 114 redacted.
  2. 32 unique unredacted phone numbers.
  3. 44 unique redacted phone numbers.

From there, there really wasn't much to work with. Most of the phone calls were day-to-day calls to places like flower shops, doctors and restaurants.

Still, some numbers are interesting:

  1. A four-hour hotel: Prestige Club: Aura
  2. Investigative services: Statewide Investigative Services and Kennealy & O'Callaghanh
  3. Michael Madigan

Data: Lawsuit Records

Going deeper

With all of that done – a year and a half in total for one request - I wasn’t feeling satisfied and dug deeper. This time, I started approaching it methodically to build a toolchain of sorts. So, to determine whether the same length of time could be requested without another lawsuit:

Please provide me with the to/from telephone numbers, duration, time and date of all calls dialed from 121 N La Salle St #507, Chicago, IL 60602 for the below dates.

April 6-9, 2015
November 23-25, 2015

And sure enough, two weeks later, I received two pdfs with phone records – this time with times, dates, from number and call length! Much faster now! Still, it’s lame that they’re still redacting a lot, and there wasn't anything interesting in these records.

Data: Long Distance, Local

Full year of records

How about for a full year for a small set of previously released phone numbers?

Please provide to me, for the year of 2014, the datetime and dialed-from number for the below numbers from the [Office of the Mayor]

(312) 942-1222 [Statewide Investigative Services]
(505) 747-4912 [Azura Investigations]
(708) 272-6000 [Aura - Prestige Club]
(773) 783-4855 [Kennealy & O'Callaghanh]
(312) 606-9999 [Siam Rice]
(312) 553-5698 [Some guy named Norman Kwong]

Again, success!

Siam Rice: 55 calls!
Statewide Investigative Services: 8 calls
Keannealy & O'Callaghanh: 10 calls
Some guy named Norman Kwong: 3 calls

(Interestingly enough, they didn't give me the prestige club phone numbers. Heh!)

Data: Full year records

Full year of records - City hall

And finally, another request for previously released numbers – across all of city hall in 2014 and 2015:

The phone numbers, names and call times to and from the phone numbers listed below during 2014 and 2015 [within city hall]

(312) 942-1222 [Statewide Investigative Services]
(773) 783-4855 [Kennealy & O'Callaghanh]
(312) 346-4321 [Madigan & Getzendanner]
(773) 581-8000 [Michael Madigan]
(708) 272-6000 [Aura - Prestige Club]

Data: City hall full year

This means that a few methods of retrieving phone records are possible:

  1. A week's worth of (mostly redacted) records from a high profile office.
  2. A phone records through an office as big as the City Hall.
  3. The use of requesting unredacted phone numbers for future requests.

Phone directory woes

Of the 27 distinct Chicago phone numbers found within the last request’s records, only five of them could be resolved to a phone number found in Chicago’s phone directory:

DEAL, AARON J
KLINZMAN, GRANT T
EMANUEL, RAHM
NELSON, ASHLI RENEE
MAGANA, JASMINE M

This is a problem that I haven't solved for yet, but it should be easy enough by requesting a full phone directory from Chicago's DoIT. Anyone up for that challenge? ;)

Emails?

This probably deserves its own blog post, but I wanted tease it a bit, because it leads into other posts.

I sent this request with the presumption that the redaction of emails would take a very long time:

From all emails sent from [the Office of the Mayor] between 11/24/14 and 12/04/14, please provide me to all domain names for all email addresses in the to/cc/bcc. From each email, include the sender's address and sent times.

Two months later, I received a 1,751 page document with full email addresses for to, from cc, and bcc, including the times of 18,860 separate emails to and from the mayor’s office. Neat - it only took about a year and a half to figure out how to parse the damn thing, though....

Interestingly, the mayor's email address isn't in there that often...

Data: Email Metadata

What's next?

This whole process was a complete and total pain. The usefulness of knowing the ongoings of our government - especially at its highest levels - are critical for ensuring that our government is open and honest. It really shouldn't have been this difficult, but it was. The difficulties led me down an interesting path of doing many similar requests - and boy are there stories.

Next post: The time Seattle accidentally sent me 30m emails for ~$30.

Code

Scraping Chicago's phone directory

Parsing 1,700 page email pdf

Tags: foia, phone, email, data, chicago