How to Convert Chrome Browser History Sqlite Timestamps with Osquery

Question

As I understand, the Chrome browser uses the WebKit time format for timestamps within the browser history database. WebKit time is expressed as milliseconds since January, 1601.

I've found numerous articles that seemingly have the answer to my question, but none have worked so far. The common answer is to use the formula below to convert from WebKit to a human-readable, localtime:

SELECT datetime((time/1000000)-11644473600, 'unixepoch', 'localtime') AS time FROM table;

Sources: https://linuxsleuthing.blogspot.com/2011/06/decoding-google-chrome-timestamps-in.html What is the format of Chrome's timestamps?

I'm trying to convert the timestamps while gathering the data through Osquery, using the configuration below.

"chrome_browser_history" : {
        "query" : "SELECT urls.id id, urls.url url, urls.title title, urls.visit_count visit_count, urls.typed_count typed_count, urls.last_visit_time last_visit_time, urls.hidden hidden, visits.visit_time visit_time, visits.from_visit from_visit, visits.visit_duration visit_duration, visits.transition transition, visit_source.source source FROM urls JOIN visits ON urls.id = visits.url LEFT JOIN visit_source ON visits.id = visit_source.id",
        "path" : "/Users/%/Library/Application Support/Google/Chrome/%/History",
        "columns" : ["path", "id", "url", "title", "visit_count", "typed_count", "last_visit_time", "hidden", "visit_time", "visit_duration", "source"],
        "platform" : "darwin"
    }

"schedule": {
    "chrome_history": {
    "query": "select distinct url,datetime((last_visit_time/1000000)-11644473600, 'unixepoch', 'localtime') AS time from chrome_browser_history where url like '%nhl.com%';",
    "interval": 10
    }
}

The resulting events have timestamps from the year 1600:

"time":"1600-12-31 18:46:16"

If I change the config to pull the raw timestamp with no conversion, I get stamps such as the following:

"last_visit_time":"1793021894"

From what I've read about WebKit time, it is expressed in 17-digit numbers, which clearly is not what I'm seeing. So I'm not sure if this is an Osquery, Chrome, or query issue at this point. All help and insight appreciated!

Answer 1

Solved. The datetime conversion needs to take place within the table definition query. I.e. the query defined underneath "chrome_browser_history".

"chrome_browser_history" : {
        "query" : "SELECT urls.id id, urls.url url, urls.title title, urls.visit_count visit_count, urls.typed_count typed_count, datetime(urls.last_visit_time/1000000-11644473600, 'unixepoch') last_visit_time, urls.hidden hidden, visits.visit_time visit_time, visits.from_visit from_visit, visits.visit_duration visit_duration, visits.transition transition, visit_source.source source FROM urls JOIN visits ON urls.id = visits.url LEFT JOIN visit_source ON visits.id = visit_source.id",
        "path" : "/Users/%/Library/Application Support/Google/Chrome/%/History",
        "columns" : ["path", "id", "url", "title", "visit_count", "typed_count", "last_visit_time", "hidden", "visit_time", "visit_duration", "source"],
        "platform" : "darwin"
    }

"schedule": {
    "chrome_history": {
    "query": "select distinct url,last_visit_time from chrome_browser_history where url like '%nhl.com%';",
    "interval": 10
    }
}

Trying to make the conversion within the osquery scheduled query (as I was trying before) will not work. i.e:

"schedule": {
"chrome_history": {
"query": "select distinct url,datetime((last_visit_time/1000000)-11644473600, 'unixepoch', 'localtime') AS time from chrome_browser_history where url like '%nhl.com%';",
"interval": 10
}

}