Friday, November 23, 2012

Google Analytics Cookie Parser

I recently watched an excellent webcast on the SANS website archive about ‘Not So Private Browsing”. In this webcast, Google Analytics cookies are covered, and the wealth of information that can be found in them. I also located a great article on the DFI News website that covers these cookies as well.
I won’t go into detail here, as both of the above mentioned resources do a great job. But, briefly, the Google Analytics cookies can contain information such as keywords, number of visits, and the first and second most recent visit. According to the SANS webcast, approximately 80% of websites use Google Analytics, so there is a good chance you may find some of these in your exams.

There are three types of cookies that contain information of value: __utma, __utmb and __utmz. The four main (debatable, I know) browsers store them differently. Internet Explorer stores them in a text file, Firefox and Chrome in an SQLite database, and Safari in a plist file.

The values in the data base look something like this:
  • __utma: 191645736.1125870631.1349411172.1349411172.1349411172.1
  • __utmb: 140029553.1.10.1349409002 
  • __utmz: 140029553.1349409002.1.1.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=php%20email%20throttling 
For example, the values highlighted in red are timestamps in Unix epoch, and the "1" in blue is the number of hits.  As I mentioned before, both SANS and the DFI News article break down how to parse these out in detail.

I have written one tool that will parse the Google Analytics cookies for these four browsers, GA Cookie Cruncher:

Internet Explorer - point the tool to the folder containing the cookies (either export out the cookies folder, or mount the image). The tool will read each cookie within the folder, determine if it has these values and parse them accordingly.

Chrome – point the tool to the cookies sqlite database (either exported from your image, or mounted). The tool will query the database for all the Google Analytics values and parse accordingly.

Safari (Mac) – point the tool the the cookies.plist file. It will parse the plist file and the the Google Analytics cookies within.

Firefox- The Firefox cookies are stored in an SQLite database. Unfortunately, the wrapper library I used can not access this SQLite Database. I also tried to test the Firefox cookies database with the free SQLite Browser which could not read it either. So far, the only tool I have been able to access this database with is the SQLite Manager plugin for Firefox.

The work around I implemented is load the Firefox cookies database into the SQLite Manager plugin. From there, export out the mz_cookies table into a CSV file. This csv file can then be parsed by the program. I know, extra work, sigh - but it’s still better then manually parsing through that data.

I have  included a little hint in the "Browser Information" box to remind you where the default location of these cookies are for whatever browser you select. I cant event remember where I put my keys, so I thought this might be helpful.

The program creates 3 files in CSV format: %Browesername%_UTMA, %Browesername%_UTMB and %Browesername%_UTMZ

 Here is some sample output from Internet Explorer from a __utmz cookie:

 Now, I haven’t tested this on every browser version out there, and I have seen some variations on the way the cookies are stored. Some initial tests indicate that IE 9 does not seem to track these values, but more research will need to be done to confirm (thanks to cheeky4n6monkey for the testing).  If the tool does not read your cookie file, I'm happy to help, just shoot me an email.

Download the GA Cookie Cruncher here.