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.

Enjoy!

6 comments:

  1. Great information. Two things:

    1. I receive a compatibility error on my 32-bit Win7 machine. I will try my 64-bit machine later.

    2. In reading over the DFI article, what "hash type" is being used to decode the domain hash? Maybe I missed that piece of information somewhere.

    ReplyDelete
  2. Thanks for the input! I did not have a 32 bit system handy to test it on. I'll see if I can fix that.

    As for the Hash, I didn't see it either...I'll have to look into that.

    ReplyDelete
  3. Hi Mari,
    Thanks for such a great tool!
    Exporting from SQLite manager plugin in Firefox and parsing the file with GA Cookie Cruncher gives me the error after I click the process button:

    Unable to process file. Please verify file format: Conversion from string "1367551553637:1" to type 'Integer' is not valid.

    What can cause this issue? Some special settings in the export dialog of SQLite manager plugin ? I tried different combinations of check-boxes but still no luck. Please help.
    Thank you!

    ReplyDelete
  4. Alex,

    Thanks for the feedback.

    I have that error as a catch all if the program is not able to parse the file due to multiple reasons. That number looks like an epoch time stamp that contains milliseconds.

    Can you open the CSV file and search for that row and send it to me, or tell me what column contains that data? column 7 should be have 10 digits, and column 8 should have 16.

    In the meantime, if that is the only value that contains the timestamp in that format, you can remove that row and it should process ok.

    My email is arizona4n6 at gmail.com

    ReplyDelete
  5. The SANS webcast link is dead, but I found the preso on youtube for anyone interested.

    Not So Private Browsing
    https://www.youtube.com/watch?v=SKNzxAiNQrA

    Thanks for your posts on GA cookies, and blog in general.

    ReplyDelete