Title
#general
puffycid

puffycid

09/16/2021, 9:07 PM
👋 hey quick question for those who have used ATC json config files has anyone used them to query browser history? many of browsers use sqlite, so im trying to use ATC to query them it works fine for safari
{
    "auto_table_construction": {
        "safari_history": {
            "query": "SELECT id, url, domain_expansion, visit_count, daily_visit_counts, weekly_visit_counts, autocomplete_triggers, should_recompute_derived_visit_counts, visit_count_score, status_code FROM history_items;",
            "path": "/Users/%/Library/Safari/History.db",
            "columns": [
                "id",
                "url",
                "domain_expansion",
                "visit_count",
                "daily_visit_counts",
                "weekly_visit_counts",
                "autocomplete_triggers",
                "should_recompute_derived_visit_counts",
                "visit_count_score",
                "status_code"
            ],
            "platform": "darwin"
        },
    "safari_visits": {
            "query": "SELECT id, history_item, visit_time, title, load_successful, http_non_get, synthesized, redirect_source, origin, generation, attributes, score FROM history_visits;",
            "path": "/Users/%/Library/Safari/History.db",
            "columns": [
                "id",
                "history_item",
                "visit_time",
                "title",
                "load_successful",
                "http_non_get",
                "synthesized",
                "redirect_source",
                "origin",
                "generation",
        "attributes",
        "score"
            ],
            "platform": "darwin"
        }
    }
osquery> select count(*) from safari_history;
count(*) = 8
but for chrome/chromium i get some issues
{
    "auto_table_construction": {
        "chromium_history": {
            "query": "SELECT id, url, title, visit_count, typed_count, last_visit_time, hidden from chromium_history;",
            "path": "/Users/%/Library/Application Support/Chromium/Default/History",
            "columns": [
                "id",
                "url",
                "title",
                "visit_count",
                "typed_count",
                "last_visit_time",
                "autocomplete_triggers",
                "hidden"
            ],
            "platform": "darwin"
        }
    }
}

osquery> select * from chromium_history;
W0916 16:46:23.718451 315801088 auto_constructed_tables.cpp:47] ATC Table: Error Code: 1 Could not generate data: Could not prepare database for path /Users/%/Library/Application Support/Chromium/Default/History
same error for the Firefox history.sqlite file too im not sure its a bug or user error? it looks similar to the issue at https://github.com/osquery/osquery/issues/5225 but im not 100% sure ? just curious has anyone been able to use ATC with browser history like chrome/chromium or firefox? thanks
9:24 PM
this was user error
from chromium_history
is suppose to be
from urls
Seth Hanford

Seth Hanford

09/21/2021, 8:14 PM
Yes, I’ve done this for eg. Chrome, Brave, Firefox, and Safari; I’ve not worked with Chromium specifically. One thing that may help you is to use sqlite3 to open the database at the path you’ve specified and see if your SELECT works. One thing I see to begin with is that you specify ‘autocomplete_triggers’ in your Columns list, but do not SELECT it. You might also need to look at which tables you’re selecting from. I would expect the table to be SELECT … FROM urls (and maybe join with visits and/or visit_source). You are selecting from the name of your ATC which is perhaps not a table in the History sqlite