SQL Search Templates

A place for general discussion of sleuthkit.org projects or other open source forensics software.

Moderator: carrier

SQL Search Templates

Postby Hoyt » Thu Oct 19, 2017 5:46 pm

I thought it might be useful to gather some commonly used SQL search command sequences in one place. These can be copy/pasted into various SQL tools out there to sort, filter, and transform data. For example, DB Browser is a very nice, open source tool for SQLite databases as are used in Chrome, Firefox, etc. These commands can also be saved to a text file with either a ".txt" or a ".sql" extension and imported. In any case, here's a couple to start:

These are useful for Firefox investigations and pull from the moz_places and moz_historyvisits tables in places.sqlite:

To obtain a complete URL history list with date/time stamps converted from PRTime:

Code: Select all
select moz_places.url,
datetime((moz_historyvisits.visit_date/1000000),
'unixepoch', 'localtime') from moz_places,
moz_historyvisits where
moz_historyvisits.place_id = moz_places.id order
by moz_historyvisits.visit_date desc;


To obtain Yahoo searches and terms with converted PRTime:

Code: Select all
select moz_places.url,
datetime((moz_places.last_visit_date/1000000),
'unixepoch','localtime') from moz_places,
moz_historyvisits where
moz_places.id = moz_historyvisits.place_id and
moz_places.url like '%search.yahoo.com%/search?p=%'
order by moz_places.last_visit_date desc;


To obtain typed URLs:

Code: Select all
select moz_places.url,
datetime((moz_historyvisits.visit_date/1000000),
'unixepoch', 'localtime') from
moz_places,moz_historyvisits where
moz_places.id = moz_historyvisits.place_id and
moz_historyvisits.visit_type = 2 order by
moz_historyvisits.visit_date desc;


Feel free to add more.

Hoyt
Hoyt
 
Posts: 61
Joined: Thu Dec 11, 2014 4:02 am
Location: Little Rock, AR

Return to General

Who is online

Users browsing this forum: No registered users and 1 guest

cron