如何使用Osquery转换Chrome浏览器历史记录Sqlite时间戳

debugcn 发表于 Dev

迪伦·布勒

据我了解，Chrome浏览器将WebKit时间格式用于浏览器历史记录数据库中的时间戳。WebKit时间表示为自1601年1月以来的毫秒数。

我找到了许多看似可以回答我问题的文章，但到目前为止，都没有任何一篇。常见的答案是使用以下公式将WebKit转换为人类可读的本地时间：

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

来源：https : //linuxsleuthing.blogspot.com/2011/06/decoding-google-chrome-timestamps-in.html Chrome时间戳的格式是什么？

我正在尝试使用以下配置在通过Osquery收集数据时转换时间戳。

"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
    }
}

结果事件的时间戳记始于1600年：

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

如果我更改配置以不进行转换就提取原始时间戳，则会得到如下图章：

"last_visit_time":"1793021894"

根据我对WebKit时间的了解，它用17位数字表示，这显然不是我所看到的。因此，我目前不确定这是Osquery，Chrome还是查询问题。所有帮助和见识表示赞赏！

迪伦·布勒

解决了。日期时间转换需要在表定义查询中进行。即在“ 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
    }
}

尝试在osquery计划的查询中进行转换（如我之前尝试的那样）将不起作用。即：

"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
}

}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。