ESE/EDB/Jetblue – Edge, WebCache & Cortana

by | Sep 26, 2015 | ESE Extension

Along with SQLite, the Microsoft Extensible Storage Engine (ESE) is becoming increasingly common on Windows mobile phones and desktop operating systems. ESE, also known as Jetblue or EDB DBs, is the technology that underpins these databases such as Windows search and Cortana. In this article, I want to look briefly at the Microsoft ESE database used by Internet Explorer 10 and the new Edge browser and also a little at Cortana and highlight some of the features of The Forensic Browser for SQLite that make examining these databases easy and also highlight some interesting artifacts as we go.

Update – can now recover deleted records.

In order to use our ESE Extension, you will also need a current licence for the Forensic Toolkit for SQLite, also available at the same link.

If you are keeping up with developments with the Forensic Toolkit for SQLite you will be aware that I recently released a Forensic Browser extension that allows ESE databases to be examined with the Forensic Browser and therefore use all of the querying and reporting features available within the Browser as part of your investigation.

Running the extension once installed is simplicity, just create a case file, do not open an SQLite database when prompted and then run the extension from the extensions menu. The screenshot below shows me opening a database.

After the extension has been run you will see a host of tables in the browser, the screenshot below shows the tables from a WebCache01.dat Microsoft Edge database:

As you can see there is a good amount of duplication with the table names and investigation shows that for each table with a similar name has the same structure. So all tables named container_xxx have the same number of column definitions (names and types).

So the containers_1 table looks like this :

and container_26 looks like this:

As you can see the table structure looks essentially the same and investigation shows that the data in each table of a similar name is consistent although each table has a slightly different use (more later). 

Further investigation of an ‘inuse’ system shows that the number of tables seems to increase with use, so additional container_xxx tables are added. The same applies to the other ‘groups’ of tables, on my system, there are currently 67 Container tables, 9 AppCache tables, 7 AppCacheEntry tables, 4 DependencyEntry tables and 5 HstEntry tables. Some of these tables have no data (rows).

SQL and particularly SQLite allows us to use a query type known as a union to create an amalgamated view onto tables (or subsets of tables) where the column types are the same, this query is known as a UNION.

To create a union from the two tables above and select all of the tables the SQL is:

Code:
SELECT Container_1.* FROM Container_1 UNION SELECT * FROM Container_26
The resultant table looks like this:

Clearly doing this for 67 different tables each containing different parts of essentially the same dataset would be very cumbersome so the Forensic Browser allows you to easily create a view based on a SQL union that essentially creates a compound table amalgamating the contents of tables with the same structure. So instead of investigation 67 container tables, you can just investigate 1 compound virtual table. This simplifies the investigation immensely.

The SQL for this is just more of the UNIONs described as above:

Code:
SELECT Container_1.* FROM Container_1 UNION SELECT * FROM Container_2 UNION SELECT * FROM Container_3 and so on
While this is straight forward and queries based on the union (especially a union of 67 tables) would start to look a bit complex so we can use another SQL facility known as a VIEW. A view is a sort of virtual named query, i.e. we can call the query above something like “containers_view” and then use this name instead of the full query, e.g.: Code
SELECT * FROM containers_view
All of the above is really just background as I have added a new menu feature to the Browser whereby you select a table and the Browser will identify all tables with the same structure and automatically create a compound UNION on these tables and then create a VIEW to alias the UNION. In the example below you can see me creating just such a view (using containers_26 as a source table) with the resulting table containing nearly 30K entries:
(note that the view this function creates automatically adds a TableName column for clarity – i.e. to show which table each row comes from). I mentioned before that although these tables have the same format the content is subtly different. There is a further table within the database called helpfully containers, this table tells us what the purpose of each of the containers_xx table is. In the screenshot below we can see that containerId 1 is Cookies, ContainerId 2 is History etc,
Now that we have our VIEW created for us it is simple to then create a compound query/report showing the purpose of each row in the report. The following screenshot shows just such a join – created just using drag and drop – identifying just a small subset of the columns and all rows that contain the word ‘sanderson’, with the CreationTime column converted to human-readable format (from Microsoft filetime)

Dependency entries

I have seen a few posts recently regarding the purposes of the Dependency entry tablesr, I recently posted the results of a small piece of research into these on a forensic forum, for interest, I summarise this here. Dependencies are those sites that a web browser needs to load/visit in order to render a page. So for my site most pages have a link to google analytics so when the page is visited a DNS lookup, TCP connection and HTTP connection all need to be made to Google in order to display the page. If a site contains pictures held remotely (as below for my pic) then these are sites that need to be visited in order to display this post correctly. These are cached to speed up future visits. There are a number of DependencyEntry tables and I use a SQL Union to display them all in one grid in my Browser. You can see from the blob display that this site appears to use a font from Google and by looking at the HTML for digital-detective.net you can find the relevant link.

Cortana Reminders

When I look at the database (CortanaCore.ese) there are a number of tables within the database (I do not propose to go into them all here – this is not a Cortana forensics article) but the reminders table stood out as being possibly relevant. When I examined this table it contained four columns that held windows 64-bit timestamps (converted to human-readable in the display below) and a title that corresponded to the tasks I had just created. I also noticed that there was an extra row in this table that shows a task I had previously created that was to trigger on the next occasion I spoke to my friend Jackie. The three rows in the Reminders table look as follows:

We can see from the above that one of the tasks has a completion date and of course this is for the café that I visited yesterday.

But how do we link these triggers to the locations at which they should occur, further examination shows that we need to create joins on two additional tables to generate this report. The first table is in fact the ‘master’ table for want of a better phrase and is named triggers. This table has two fields that link to the Reminders table (ReminderId) and to the locationtriggers table (id):

Triggers

Location Triggers

A simple query on a few select columns from these tables that creates a usable report is as follows:

Code

SELECT Triggers.Kind, Triggers.CreationTime, Reminders.Status, Reminders.LastUpdateTime, Reminders.CompletionTime, Reminders.Title, Reminders."Text", LocationTriggers.Latitude, LocationTriggers.Longitude, LocationTriggers.Radius, LocationTriggers.Name, FROM Triggers LEFT JOIN Reminders ON Triggers.ReminderId = Reminders.Id LEFT JOIN LocationTriggers ON Triggers.Id = LocationTriggers.Id

However using the inbuilt mapping functionality for the Forensic Browser it is the straight forward to create a report (all using drag and drop) that shows a map of the Cortana georeferenced location: