Important Queries

1. Extract Cookies with Expiry Date

SELECT host_key, name, value, expires_utc FROM cookies;

• Purpose: Retrieves cookies stored by the browser, including their host_key, name, value, and the expiration date (expires_utc).

• Use case: This query can be used to extract user-specific cookies for a given website, useful in tracking user sessions or identifying any stored authentication cookies.

---

2. Extract Auto-fill Data for Fields

SELECT name, value FROM autofill WHERE field_type = 'field';

• Purpose: Extracts auto-filled data stored by the browser for form fields. The data includes name and value.

• Use case: Useful for obtaining auto-fill details for various form fields such as name, email, phone number, and other form entries.

---

3. Extract Bookmarked URLs with Tags

SELECT url, title, GROUP_CONCAT(tags) AS bookmark_tags FROM bookmarks GROUP BY url, title;

• Purpose: Retrieves bookmarked URLs along with their titles and tags.

• Use case: Helps in identifying important websites bookmarked by the user. Tags can categorize these bookmarks, making it easier to analyze them.

---

4. Extract Downloaded Files with Source and Target

SELECT url, target_path, start_time, end_time FROM downloads;

• Purpose: Extracts details of downloaded files, including the source URL, file path, and the download start and end times.

• Use case: Useful for understanding user behavior by tracking the files they've downloaded.

---

5. Extract Form Input Data with Origin

SELECT origin, field_name, value FROM forms;

• Purpose: Extracts form input data, including the origin (the website or page), field_name (the specific field in the form), and value (the input entered by the user).

• Use case: Helps to recover sensitive data entered in forms, such as login credentials, user preferences, etc.

---

6. Extract Visited URLs with Timestamp and Referrer

SELECT url, visit_time, referring_visit_id FROM visits;

• Purpose: Retrieves URLs visited by the user along with the visit time and the referring visit's ID.

• Use case: Used to track the browsing history of the user and understand their navigation flow between websites.

---

7. Extract User Annotations on Bookmarks with Dates

SELECT url, annotation, created, modified FROM annotations;

• Purpose: Extracts annotations made by the user on their bookmarks, along with the date they were created or modified.

• Use case: Useful for recovering additional context or notes added to bookmarked pages.

---

8. Extract Credit Card Expiration Years and Months

SELECT name_on_card, card_number, expiration_month, expiration_year FROM credit_cards;

• Purpose: Retrieves saved credit card details, including the cardholder's name, card number, and expiration date.

• Use case: Used for extracting sensitive financial information from the browser's stored credit card data.

---

9. Extract Synced Data with Device Information

SELECT id, name, device_type, last_modified FROM sync;

• Purpose: Retrieves information about synced data from various devices, including the device ID, name, type, and the last modified date.

• Use case: Helps track devices that the user has synchronized with the browser.

---

10. Extract URL Chains for Downloaded Files

SELECT url_chain FROM downloads_url_chains;

• Purpose: Retrieves chains of URLs associated with downloaded files.

• Use case: Useful in tracking the sequence of URLs leading to a specific download, which can help in identifying the source of the download.

---

11. Extract Suspicious URLs with Frequent Keyword Searches

SELECT url, title, COUNT(*) AS search_count FROM history WHERE title LIKE '%search%' GROUP BY url, title ORDER BY search_count DESC LIMIT 10;

• Purpose: Extracts the most frequently searched URLs, filtered by titles that contain the keyword "search".

• Use case: Useful for identifying URLs that have been visited multiple times for search-related activities.

---

12. Extract Suspicious USB Device Connections

SELECT guid, manufacturer, product FROM usb_devices WHERE manufacturer LIKE '%unknown%' ORDER BY connection_timestamp DESC LIMIT 5;

• Purpose: Extracts information about USB devices connected to the system, specifically looking for devices with unknown manufacturers.

• Use case: Helps in detecting suspicious USB devices that may have been used to exfiltrate data.

---

13. Extract Suspicious Media Playback Sessions

SELECT media_unique_id, playback_start_time_usec, duration_usec FROM media_session WHERE duration_usec > 3600000;

• Purpose: Extracts information about media playback sessions that have a duration greater than 1 hour.

• Use case: Useful for identifying prolonged media sessions that could be suspicious in nature, such as video or audio sessions used for illicit purposes.

---

14. Extract Suspicious Form Input History

SELECT form_field, user_input, input_timestamp FROM input_history WHERE user_input LIKE '%password%' OR user_input LIKE '%credit card%';

• Purpose: Extracts input history from form fields where the user input contains keywords like "password" or "credit card".

• Use case: Helps identify sensitive data that has been entered into forms, such as login credentials or payment details.

---

15. Extract Synced Tabs with Last Update Timestamp

SELECT url, title, last_updated FROM synced_tabs;

• Purpose: Extracts details about tabs that have been synced across devices, including the URL, title, and last updated timestamp.

• Use case: Useful for recovering tabs that the user has accessed on different devices.

---