Firefox History Statistics - Extracting from Places.sqlite

If you want to take a look at Firefox surfing activity, the about:me add-on is a good start. However, it presents only one view of data and is thus limited in its ability to present more detailed statistics.

We will view that data in a different program. So let's first extract it from the browsing history stored in the Places.sqlite file into a CSV file using a Firefox add-on.

Step 1 - Locate and copy Places.sqlite to a working location

On Windows machines, Places.sqlite is found in a directory similar to:

C:\Users\User1\AppData\Roaming\Mozilla\Firefox\Profiles\.default\places.sqlite

Copy the file to another location. The database will be locked while using Firefox, and the SQLite plugin we will use to open it.

Step 2 - Install SQLite Manager

1. Aim Firefox at: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
2. Click 'Add to Firefox'.
3. Restart Firefox.

Step 3 - Open the Database

1. From Firefox, open SQLite Manager.
2. In SQLite Manager, open the copy of Places.sqlite. If SQLite Manager does not prompt you to open it, use 'Database -> Connect'.

Step 4 - Execute SQL

Try this query in the 'Execute SQL' tab, changing the date to suit you.

SELECT url, host, rev_host, title, datetime(visit_date/1000000, 'unixepoch', 'localtime') as timestamp
from
moz_historyvisits v
join
moz_places p
on
v.place_id = p.id
join
moz_hosts h
on
instr(p.url, trim(h.host)) > 0
where
timestamp >= '2014-06-01'
order by timestamp desc

It may take a few minutes for the query to run. You can see that the columns we want are spread across a three tables. Plus, we need to pattern match host names for decent aggregation. When it's completed, click:

Actions -> Save Result (CSV) to file

Next: Importing and Analysing Firefox History Data in LibreOffice Calc

Alternative methods:

• Install the SQLite ODBC driver and use LibreOffice Base to open the places.sqlite file. However, as we would probably use Calc for reporting, it's slightly more unwieldy.
• Exporting entire tables to CSV to run LibreOffice Query Builder did not work as LibreOffice will not perform join queries on CSV tables.