Update - can now recover deleted records.
The extension can be purchased from this link, 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 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 additonal 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:
SELECT Container_1.* FROM Container_1 UNION SELECT * FROM Container_26
Clearly doing this for 67 different tables each containing different parts of essentially the same dataset would be very cumbersome so the Forenisc Browser allows you to easily create a 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:
SELECT Container_1.* FROM Container_1 UNION SELECT * FROM Container_2 UNION SELECT * FROM Container_3 and so on
SELECT * FROM containers_view
(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 compund 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)
I have seen a few posts recently regarding the puroses 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 an find the relevant link.
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 time stamps (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 task 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):
A simple query on a few select columns from these tables that creates a usable report is as follows:
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