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!

score 4 · Accepted Answer · answered Apr 14 '20 at 19:34

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
}

}

I'm glad you found a workaround. This seems like it might be a bug. I've created https://github.com/kolide/k2/issues/2054 for it — seph, Apr 15 '20 at 02:41
Yeah, bug. I have a fix up for it. Should be release in 4.3.1 — seph, Apr 17 '20 at 02:31

score 2 · Answer 2 · answered Apr 14 '20 at 11:02

2

Try:

SELECT datetime(last_visit_time/1000000-11644473600, \"unixepoch\") as last_visited, url, title, visit_count FROM urls;

This is from something I wrote up a while ago - One-liner that runs osqueryi with ATC configuration to read in the chrome history file, export as json and curl the json to an API endpoint

https://gist.github.com/defensivedepth/6b79581a9739fa316b6f6d9f97baab1f

answered Apr 14 '20 at 11:02

Josh Brower

245
1
2
8

Thanks for the code, still not working for me unfortunately. "last_visited":"1601-01-01 00:28:32". It really seems like the timestamps osquery is pulling are wrong for me. They don't seem to be in WebKit format. – Dylan Buehler Apr 14 '20 at 13:28

score 0 · Answer 3 · answered Apr 14 '20 at 16:07

The things you're working with, are pretty straight sqlite. So I would start by debugging inside sqlit.

First, you should verify the data is what you expect. On my machine, I see:

$ cp   Library/Application\ Support/Google/Chrome/Profile\ 1/History /tmp/
$ sqlite3 /tmp/History "select last_visit_time from urls limit 2"
13231352154237916
13231352154237916

Second, I would verify the underlying math:

sqlite> select datetime(last_visit_time/1000000-11644473600, "unixepoch") from urls limit 2;
2020-04-14 15:35:54
2020-04-14 15:35:54

It would be easier to test your config snippet if you included it as text we can copy/paste.

Awesome, makes sense. Verified that Chrome is producing valid WebKit timestamps and storing them in sqlite. sqlite> select datetime(last_visit_time/1000000-11644473600, "unixepoch") from urls limit 2; 2020-04-14 13:21:38 2020-04-13 15:12:20 sqlite> select last_visit_time from urls limit 2; 13231344098641512 13231264340656178 — Dylan Buehler, Apr 14 '20 at 19:12

How to Convert Chrome Browser History Sqlite Timestamps with Osquery

3 Answers3