8 SQL snippets to make you an osquery datetime expert
If someone told you they wanted to meet up for drinks at 1634656080,you might question your choice of friends. Yet, this is precisely the type of response you would get if you asked osquery when a file was created, when a registry key was modified, or when a user logged into a device.
Understanding how to interpret, compare and manipulate these values is crucial to writing robust osquery SQL, and this article will help you learn all of the tips and tricks necessary.
—
This article is an excerpt from my in-progress osquery SQL handbook.
If you want to jump around to a specific section, feel free to use the Table of Contents below to skip ahead.
Table of Contents:
i. What is osquery?
ii. How does osquery report time-series data?
iii. Date/Time operators in osquery (SQLite)
strftimedatetimejulianday- Using modifiers to offset timestamps
- Working with raw unix time
- Finding out what time it is ‘now’
- Calculating how much time has elapsed
- Common epoch offsets for timestamp conversion
A. Extra Fun (CTE Lookup Tables with Dates)
B. Wrapping things up
C. Further Reading
A brief background on osquery
Osquery is an endpoint visibility tool that translates native API calls into SQLite virtual tables, allowing a Mac, Linux, or Windows computer to be queried in real-time as if it were a relational database:
osquery> SELECT name, pid, parent FROM processes LIMIT 5;
+----------------+-----+--------+
| name | pid | parent |
+----------------+-----+--------+
| kernel_task | 0 | 0 |
| launchd | 1 | 0 |
| syslogd | 67 | 1 |
| UserEventAgent | 68 | 1 |
| uninstalld | 71 | 1 |
+----------------+-----+--------+
Created at and later open-sourced by Facebook, osquery is now under the ownership and governance of The Osquery Foundation, which is under The Linux Foundation. Osquery is the underlying agent for dozens of security solutions and runs on millions of devices across the globe.
If you aren’t familiar with osquery, you can get started exploring it in less than 5 minutes by installing the agent on your device:
osquery / Downloads / Official
For those of you who are already familiar with osquery, or using another osquery endpoint manager like Kolide, feel free to dive in and start refining your date and time SQL interactions!
How does osquery report time-series data?
The broad majority of date and time-based columns in osquery’s output are returned in unix-epoch (or unix-time) format.
In computing, an epoch is a date and time from which a computer measures system time. Most computer systems determine time as a number representing the seconds removed from particular arbitrary date and time.
Unix time is the number of seconds since ‘the unix epoch’ which began at 00:00:00 AM, Thursday, January 1, 1970, UTC.
UTC here is important to note; it stands for Coordinated Universal Time (a successor to Greenwich Mean Time GMT). UTC allows us to precisely define a timepoint without worrying about time zones and/or daylight savings time.
Why should osquery use epoch at all?
With so many disparate data sources populating the output of osquery, we must strive for uniformity among common data types whenever possible.
The usage of unix epoch for date and time values is multi-purpose:
1. It reduces performance overhead to store timestamps in a integer format. Integers are comparatively less expensive to store and query than string values.
2. It ensures a degree of uniformity and predictability in the output. When storing timestamps as strings, there are innumerable patterns that one can use (e.g., October 16, 2021, 10/16/2021, 16/10/2021, 2021–10–16), different separators, different ordering conventions; it’s frankly a mess. Using one standardized format reduces ambiguity and ensures snippets of your queries can be easily reused.
3. It’s a format that does not require knowledge of political time boundaries. E.g., How we define time zones and Daylight Savings Time changes but tracking time via an epoch means variations in those political meanings only matter at display time.
4. It’s a format that does not require understanding or accounting for the nuisance of leap seconds. (unless you prefer dealing with raw second values directly)
Ultimately, while they are somewhat ugly to look at, unix epoch timestamps are malleable and allow us a great deal of freedom when performing manipulations or comparisons.
Let’s explore how we can interact with these raw epoch values using SQLite date and time functions.
Date and time functions available in osquery
A frequent situation you may encounter when querying data containing time-series columns is to scope results to a specific time window. Knowing how many seconds have elapsed since January of 1970 is pretty unreasonable; thankfully, we can interact with dates and times using more standard conventions with the help of several functions.
Each function can be used to compare relative (e.g., three days ago) and absolute (e.g., October 31, 2021) values and each has its respective syntax and modifiers and preferred use-case.
datetime(alternativelydateandtime)juliandaystrftime
1. strftime
The strftime (string-from-time) operator is the basis for all other date and time functions in SQLite, which are provided simply as shortcuts to accomplish specific common use-cases.
For example, the output of datetime can be rewritten in strftime as in the example below:
SELECT
strftime('%Y-%m-%d %H:%M:%S','now'),
datetime('now');+-------------------------------------+---------------------+
| strftime('%Y-%m-%d %H:%M:%S','now') | datetime('now') |
+-------------------------------------+---------------------+
| 2021-11-30 20:06:47 | 2021-11-30 20:06:47 |
+-------------------------------------+---------------------+
strftime is used with the following syntax:
strftime('format_string...format_string', 'time_string', 'modifier_string')Each format string corresponds to a given format or segment of a timestamp.
+--------+--------------------------------+
| Format | Description |
+--------+--------------------------------+
| %d | day of the month: 01-31 |
| %f | fractional seconds: SS.SSS |
| %H | hour: 00-24 |
| %j | day of the year: 001-366 |
| %J | Julian day number |
| %m | month: 01-12 |
| %M | minute: 00-59 |
| %s | seconds since 1970-01-01 |
| %S | seconds: 00-59 |
| %w | day of week 0-6 with Sunday==0 |
| %W | week of the year: 00-53 |
| %Y | year: 0000-9999 |
| %% | % |
+--------+--------------------------------+The terrific flexibility strftime lets us tease apart any component of a date or time-based value based on its constituent pieces.
For example, the month format string: '%m' returns just the numeric representation (1–12) of the month component of a provided timestamp:
SELECT strftime('%m','2021-11-30');+-----------------------------+
| strftime('%m','2021-11-30') |
+-----------------------------+
| 11 |
+-----------------------------+
Example strftime Queries:
Absolute date comparisons
(e.g., before October 10, 2021)
SELECT
filename,
strftime('%Y-%m-%d %H:%M:%S',btime,'unixepoch') AS file_created
FROM file
WHERE path LIKE '/Users/%/Downloads/%'
AND file_created >= strftime('%Y-%m-%d %H:%M:%S','now','-14 days')
ORDER BY file_created DESC;Relative date comparisons
(eg. before x days ago)
SELECT
filename,
strftime('%Y-%m-%d %H:%M:%S',btime,'unixepoch') AS file_created
FROM file
WHERE path LIKE '/Users/%/Downloads/%'
AND file_created >= '2021-10-20 00:00:00'
ORDER BY file_created DESC;2. datetime
SELECT datetime(1634656080,'unixepoch');+----------------------------------+
| datetime(1634656080,'unixepoch') |
+----------------------------------+
| 2021-10-19 15:08:00 |
+----------------------------------+
The most commonly used SQLite date and time function is the aptly named: datetime. It returns timestamps in the standard ISO8601 format. Using datetimewe can supply any epoch-based value and convert or evaluate it.
The datetime function can also be used as solely date or time
SELECT
date(1634656080,'unixepoch') AS date_string,
time(1634656080,'unixepoch') AS time_string;
+-------------+-------------+
| date_string | time_string |
+-------------+-------------+
| 2021-10-19 | 15:08:00 |
+-------------+-------------+One beneficial aspect of the ISO8601 timestamps returned by the datetime function is that the SQLite engine is familiar with their format and will order/sort them appropriately when asked to do so.
WITH
example_dates (date_string) AS (
VALUES
('2021-12-09'),
('2020-12-02'),
('1985-07-13'),
('2021-12-24'),
('2023-01-08')
)
SELECT * FROM example_dates
ORDER BY date_string DESC;+-------------+
| date_string |
+-------------+
| 2023-01-08 |
| 2021-12-24 |
| 2021-12-09 |
| 2020-12-02 |
| 1985-07-13 |
+-------------+
Example datetime Queries:
Absolute date comparisons
(e.g., before October 10, 2021)
- Finding apps opened between two given dates using the
BETWEENfunction:
SELECT
name,
datetime(last_opened_time,'unixepoch') AS last_opened_at
FROM apps
WHERE last_opened_at
BETWEEN '2021-10-01 23:59:59' AND '2021-10-20 00:00:00';+--------------------------------------+---------------------+
| name | last_opened_at |
+--------------------------------------+---------------------+
| 1Password 7.app | 2021-10-15 16:28:09 |
| Adobe InDesign 2021.app | 2021-10-13 15:49:35 |
| Adobe Crash Reporter.app | 2021-10-07 13:10:15 |
| AdobeCRDaemon.app | 2021-10-07 13:10:15 |
| LogTransport.app | 2021-10-07 13:10:15 |
Relative date comparisons
(e.g., before x days ago)
- Finding files created in the past 14 days:
SELECT
filename,
datetime(btime,'unixepoch') AS file_created
FROM file
WHERE path LIKE '/Users/%/Downloads/%'
AND file_created >= datetime('now','-14 days')
ORDER BY file_created DESC;+--------------------------+---------------------+
| filename | file_created |
+--------------------------+---------------------+
| CascadiaCode-2108.26.zip | 2021-10-19 12:53:11 |
| cascadia-code-main.zip | 2021-10-19 12:51:10 |
| macos12.jpg | 2021-10-19 12:21:01 |
| macos12j.jpg | 2021-10-19 12:21:01 |
| what-year.gif | 2021-10-18 19:13:29 |
| jumanji.gif | 2021-10-18 19:08:19 |
3. julianday
Julianday is a date and time function which refers to the continuous count of days since the beginning of the Julian period (November 24, 4714 BC, in the proleptic Gregorian calendar). It’s worth noting, in osquery’s SQLite, julianday is the only date time function that relies on a different epoch than the standard unix epoch of January 1, 1970.
The julianday function is most often used for calculating the passage of time between two events. For example: ‘How many days remain between when a password was created and when it will expire?’.
Understanding how julianday calculates time
Let’s take a look at the output of julianday to see what we’re dealing with and understand how it can be used:
SELECT julianday('2021-10-19 12:21:01');
+----------------------------------+
| julianday('2021-10-19 12:21:01') |
+----------------------------------+
| 2459507.01459491 |
+----------------------------------+As we can see, the result is fractional to accommodate greater precision than a simple integer would allow. This way, we can still calculate the time of day if needed.
Let’s quickly orient ourselves to the julianday epoch by getting our bearings compared to other epochs. We can start by seeing what the julianday representation is of our standard unixepoch (00:00:00 AM, Thursday, January 1, 1970):
SELECT julianday(datetime(0,'unixepoch'));+------------------------------------+
| julianday(datetime(0,'unixepoch')) |
+------------------------------------+
| 2440587.5 |
+------------------------------------+
According to this, at the start of the 01/01/1970 unix epoch, 2440587.5 Julian days have already elapsed. Let’s verify our julianday BC epoch by using this calculated value and counting backward 2440587.5 days from the start of the unixepoch:
SELECT datetime(2440587.5*-86400.0,'unixepoch');
+------------------------------------------+
| datetime(2440587.5*-86400.0,'unixepoch') |
+------------------------------------------+
| -4713-11-24 12:00:00 |
+------------------------------------------+Precisely as we described above, the beginning of the julianday epoch is noon, November 24, 4713BC.
Thankfully, we don’t often need to be dealing with prehistoric dates, so we can put this demonstration aside to see some valuable examples. Let’s put julianday to work for a more practical purpose.
Example julianday queries
- Finding certificates that will expire in the next 14 days:
SELECT
common_name,
julianday(not_valid_after,'unixepoch') - julianday('now') AS expires_in_days
FROM certificates
WHERE expires_in_days BETWEEN 0 AND 14;+------------------+------------------+
| common_name | expires_in_days |
+------------------+------------------+
| GlobalSign | 5.71893464121968 |
| Belgium Root CA2 | 5.71893464121968 |
| GlobalSign | 5.71893464121968 |
+------------------+------------------+
- Determining session age of logged-in users:
SELECT
type,
user,
tty,-- Convert time from unixepoch to datetime format
datetime(time,'unixepoch') AS login_time,-- Calculate difference in time between login_time and now in hours
ROUND(
((JULIANDAY('now') - JULIANDAY(time,'unixepoch')) * 60),1)
AS session_age_hoursFROM logged_in_users-- Scope to only sessions which are older than 8 hours
WHERE datetime(time,'unixepoch') <= datetime('now','-8 hours') -- Order by oldest sessions
ORDER BY login_time ASC;+------+-------+---------+---------------------+-------------------+
| type | user | tty | login_time | session_age_hours |
+------+-------+---------+---------------------+-------------------+
| user | fritz | console | 2021-12-08 18:33:21 | 52.3 |
| user | fritz | ttys000 | 2021-12-08 18:39:56 | 52.0 |
| user | fritz | ttys001 | 2021-12-08 18:39:56 | 52.0 |
| user | fritz | ttys002 | 2021-12-08 18:40:00 | 52.0 |
| user | fritz | ttys003 | 2021-12-09 02:13:44 | 33.1 |
+------+-------+---------+---------------------+-------------------+
4. Using modifiers to offset date
Each of the date and time functions described above can be offset using a modifier. This means we can add or subtract a given interval of time by declaring it inside the parenthesis of our date time function like so:
SELECT datetime('2021-10-19 12:21:01','-14 days') AS two_weeks_earlier;+---------------------+
| two_weeks_earlier |
+---------------------+
| 2021-10-05 12:21:01 |
+---------------------+
Modifiers are expressed using common intervals of time (e.g., months, years, seconds, etc.).
WITH
modifiers_example (modifier,value) AS (
VALUES
('none', '2021-10-19 12:00:00'),
('-90 seconds',datetime('2021-10-19 12:00:00','-90 seconds')),
('-30 minutes',datetime('2021-10-19 12:00:00','-30 minutes')),
('-5 hours', datetime('2021-10-19 12:00:00','-5 hours')),
('-14 days', datetime('2021-10-19 12:00:00','-14 days')),
('-2 months', datetime('2021-10-19 12:00:00','-2 months')),
('-6 years', datetime('2021-10-19 12:00:00','-6 years'))
)
SELECT * FROM modifiers_example;+-------------+---------------------+
| modifier | value |
+-------------+---------------------+
| none | 2021-10-19 12:00:00 |
| -90 seconds | 2021-10-19 11:58:30 |
| -30 minutes | 2021-10-19 11:30:00 |
| -5 hours | 2021-10-19 07:00:00 |
| -14 days | 2021-10-05 12:00:00 |
| -2 months | 2021-08-19 12:00:00 |
| -6 years | 2015-10-19 12:00:00 |
+-------------+---------------------+
Multiple modifiers can be stacked. Let’s say you wanted to specify an interval of time that was three days and 12 hours in the past:
SELECT datetime('2021-10-19 12:00:00', '-3 days', '-12 hours') AS stacked_modifier;+---------------------+
| stacked_modifier |
+---------------------+
| 2021-10-16 00:00:00 |
+---------------------+
Positive offset modifiers can be used to look forward. Perhaps you want to find certificates that are due to expire soon:
SELECT
common_name,
datetime(not_valid_after,'unixepoch') AS expires
FROM certificates
WHERE expires BETWEEN
datetime('now') AND datetime('now','+14 days');+------------------+---------------------+
| common_name | expires |
+------------------+---------------------+
| GlobalSign | 2021-10-28 08:00:00 |
| Belgium Root CA2 | 2021-10-28 08:00:00 |
| GlobalSign | 2021-10-28 08:00:00 |
+------------------+---------------------+
Using relative modifiers
Not all modifiers are limited to specific intervals of time; for example, let’s say you wanted to calculate the timepoint for Thanksgiving for the next few years. Why would you want to do this? I really can’t say, but SQLite allows you to calculate these sorts of tasks with ease:
SELECT
date('2021-11-01','weekday 4','+21 days') AS thanksgiving_2021,
date('2022-11-01','weekday 4','+21 days') AS thanksgiving_2022,
date('2023-11-01','weekday 4','+21 days') AS thanksgiving_2023,
date('2024-11-01','weekday 4','+21 days') AS thanksgiving_2024,
date('2025-11-01','weekday 4','+21 days') AS thanksgiving_2025,
date('2026-11-01','weekday 4','+21 days') AS thanksgiving_2026;thanksgiving_2021 = 2021-11-25
thanksgiving_2022 = 2022-11-24
thanksgiving_2023 = 2023-11-23
thanksgiving_2024 = 2024-11-28
thanksgiving_2025 = 2025-11-27
thanksgiving_2026 = 2026-11-26
The SQLite engine evaluates the modifiers in the order they are presented; in this case, it starts from a known point (e.g., 2021–11–01) it proceeds to the 4th weekday (the first Thursday encountered). Then it is adding 21 days (3 weeks) to arrive on the 4th Thursday of November.
5. Working with raw unix time
You may prefer writing less verbose queries. You can always skip converting timestamps into human-readable formats and instead compose your queries with their raw seconds-based values in mind.
Because unix time is simply the number of seconds since the unix epoch, you can utilize seconds conversions for common intervals of time:
+----------------------------+-----------+
| human-readable time | seconds |
+----------------------------+-----------+
| 1 minute | 60 |
| 5 minutes | 300 |
| 10 minutes | 600 |
| 30 minutes | 1800 |
| 1 hour | 3600 |
| 2 hours | 7200 |
| 4 hours | 14400 |
| 6 hours | 21600 |
| 8 hours | 28800 |
| 12 hours | 43200 |
| 1 day | 86400 |
| 2 days | 172800 |
| 3 days | 259200 |
| 4 days | 345600 |
| 5 days | 432000 |
| 6 days | 518400 |
| 1 week | 604800 |
| 2 weeks | 1209600 |
| 4 weeks | 2419200 |
| 1 month (30 days) | 2592000 |
| 1 month (avg. 30.44 days) | 2629743 |
| 1 month (31 days) | 2678400 |
| 1 year (365 days) | 31536000 |
| 1 year (avg. 365.24 days) | 31556926 |
| leap year (366 days) | 31622400 |
+----------------------------+-----------+Using these values, you could achieve the same use-cases as described in the previous section by merely adding or subtracting the desired interval, as we can see below:
SELECT
filename,
btime,
(unix_time - 1209600) AS two_weeks_ago
FROM file, time
WHERE path LIKE '/Users/%/Downloads/%'
-- Look for files created in last two weeks (1209600 seconds)
AND btime >= two_weeks_ago
ORDER BY btime DESC;+--------------------------+------------+
| filename | btime |
+--------------------------+------------+
| CascadiaCode-2108.26.zip | 1634647991 |
| cascadia-code-main.zip | 1634647870 |
| macos12.jpg | 1634646061 |
| macos12j.jpg | 1634646061 |
| what-year.gif | 1634584409 |
| jumanji.gif | 1634584099 |
What if you don’t have your chart of raw values handy? Well, you can always use the strftime function starting at 0, (the beginning of epoch) to find a given interval like so:
SELECT
strftime('%s', '0', 'unixepoch', '1 day') AS day_seconds,
strftime('%s', '0', 'unixepoch', '7 days') AS week_seconds,
strftime('%s', '0', 'unixepoch', '1 month') AS month_seconds,
strftime('%s', '0', 'unixepoch', '1 year') AS year_seconds;+-------------+--------------+---------------+--------------+
| day_seconds | week_seconds | month_seconds | year_seconds |
+-------------+--------------+---------------+--------------+
| 86400 | 604800 | 2678400 | 31536000 |
+-------------+--------------+---------------+--------------+
6. What time is it right now?
It’s often helpful to know what time it is ‘now’ to create comparative logic or to calculate how much time has elapsed since a past time point. Using the 'now' modifier with any of our date time functions will allow us to return the current system time in each function’s respective output.
Likewise, there is a dedicated osquery table called time which can be queried for current system time as well:
+-------------------------------------------+----------------------+
| query | output |
+-------------------------------------------+----------------------+
| SELECT unix_time FROM time | 1634693973 |
| SELECT strftime('%s', 'now') | 1634693973 |
| SELECT (julianday('now')-2440587.5)*86400 | 1634693973.36498 |
| SELECT datetime FROM time | 2021-10-20T01:39:33Z |
| SELECT strftime('%Y-%m-%d %H:%M:%S','now')| 2021-10-20 01:39:33 |
| SELECT datetime('now') | 2021-10-20 01:39:33 |
| SELECT julianday('now') | 2459507.56913617 |
+-------------------------------------------+----------------------+Dealing with UTC vs ‘local time’
But wait, you might try to run one of these queries and think to yourself. This time is incorrect; it’s hours away from the current time! This is because, as we mentioned earlier, the unix epoch is based on UTC and does not account for your device’s location and corresponding time zone.
While it may seem counterintuitive at first, this is a good thing because it means when you query devices in osquery, timestamps are directly comparable and do not require any form of time-zone adjustment.
If you wanted to interact with date and time data that was more ‘human’ in nature (e.g., let’s find files created after 1 AM in the system’s local time), we could evaluate local-time using the 'localtime' modifier like so:
SELECT
datetime('now') AS utc_time,
datetime('now','localtime') AS local_time;+---------------------+---------------------+
| utc_time | local_time |
+---------------------+---------------------+
| 2021-12-10 18:45:04 | 2021-12-10 13:45:04 |
+---------------------+---------------------+
7. How much time has elapsed?
Let’s say you wanted to calculate the difference in time between two known points. For example, how long ago was a user account created? Using julianday or strftime and simple arithmetic, we can calculate these differences.
For example, on macOS, account creation time is retrieved from the account_policy_data table as shown below:
SELECT username, uid, creation_time
FROM account_policy_data
JOIN users USING(uid);
+------------------+------------+------------------+
| username | uid | creation_time |
+------------------+------------+------------------+
| fritz-imac | 502 | 1520000969.91084 |
| kolide-imac-pro | 501 | 1514913227.50548 |
| root | 0 | 1537895434.27449 |
+------------------+------------+------------------+Using julianday to find elapsed time
As we mentioned earlier, the julianday function is particularly well-suited for calculating the difference between two time points in numbers of elapsed days:
SELECT
username,
uid,ROUND(
(julianday('now') - julianday(date(creation_time,'unixepoch')))
-- Divide by 365 to estimate duration in years
/ 365,2) AS account_age_years
FROM account_policy_data
JOIN users USING(uid);+------------------+------------+-------------------+
| username | uid | account_age_years |
+------------------+------------+-------------------+
| fritz-imac | 502 | 3.64 |
| kolide-imac-pro | 501 | 3.8 |
| root | 0 | 3.07 |
+------------------+------------+-------------------+
Using strftime and basic arithmetic to find elapsed time
Since we know, the typical (non-leap) year is 31536000 seconds, we can arrive at the same output by using the strftime function:
SELECT
username,
uid,
ROUND((strftime('%s','now') - creation_time) / 31536000,2) AS account_age_years
FROM account_policy_data
JOIN users USING(uid);+------------------+------------+-------------------+
| username | uid | account_age_years |
+------------------+------------+-------------------+
| fritz-imac | 502 | 3.64 |
| kolide-imac-pro | 501 | 3.8 |
| root | 0 | 3.07 |
+------------------+------------+-------------------+
8. Common epoch offsets for timestamp conversion
Some software utilizes non-standard or proprietary epochs to define datetime values. Typically, these occur in an epoch with a known offset from the standard unix epoch.
When writing queries, if your timestamps are converting to an unexpected value (e.g., four thousand years in the future), you may be dealing with an unexpected epoch impacting your output.
Two helpful examples are shown below:
Core Data (Cocoa Time)
Part of the Cocoa API, and sometimes referred to as ‘Mac absolute time.’ This timestamp is represented as the seconds since midnight, January 1, 2001, GMT.
Identifying Core Data at a glance:
These values will usually be nine digits long (as opposed to unixepoch’s ten numbers) and be the wrong date from what you expect by about minus 30 years.
The offset for Core Data timestamps is: +978307200 seconds.
Where will I see Core Data timestamps?
These timestamps are often encountered in plists buried deep in macOS system internals.
An example of interacting with a Core Data timestamp is shown below when interacting with timestamp values used by the macOS locationd framework.
Raw Core Data values:
SELECT subkey, value
FROM plist
WHERE path = '/var/db/locationd/clients.plist'
AND key = 'com.apple.VoiceMemos'
AND subkey LIKE '%Time%';+-----------------------------------------+-------------------+
| subkey | value |
+-----------------------------------------+-------------------+
| ReceivingLocationInformationTimeStopped | 610636507.824172 |
| LocationTimeStopped | 610636511.044382 |
| SignificantTimeStopped | 610636510.9094909 |
+-----------------------------------------+-------------------+
Offset to standard unixepoch:
SELECT subkey,
datetime(CAST(value as integer)+978307200,'unixepoch') AS timestamp
FROM plist
WHERE path = '/var/db/locationd/clients.plist'
AND key = 'com.apple.VoiceMemos'
AND subkey LIKE '%Time%';
+-----------------------------------------+---------------------+
| subkey | timestamp |
+-----------------------------------------+---------------------+
| ReceivingLocationInformationTimeStopped | 2020-05-08 13:15:07 |
| LocationTimeStopped | 2020-05-08 13:15:11 |
| SignificantTimeStopped | 2020-05-08 13:15:10 |
+-----------------------------------------+---------------------+WebKit / Google Chrome Timestamps
Sometimes referred to as ANSI time, this timestamp is the number of seconds since midnight, January 1, 1601, GMT. This date was the start of the Gregorian calendar’s previous 400 year leap year cycle (during which Windows NT was developed and the epoch start was chosen).
The offset for Webkit timestamps is: -11644473600 seconds. However, these values are often stored in a format that includes nanoseconds and needs to be divided by 1000000 to arrive at the proper value.
Identifying WebKit timestamp at a glance:
These timestamps will usually be 17+ characters long vs. the ten-character length of the standard unix epoch timestamp. If you attempt to convert them without dividing by 1000000, no value will be returned as it will exceed the max unix time of 2038.
Where will I see WebKit timestamps?
When dealing with internal data for Google Chrome or Safari, or other applications which use WebKit.
An example of interacting with a WebKit timestamp is shown below when interacting with datetime values used by Chrome’s Local State file.
Raw WebKit values:
SELECT
key,
value
FROM kolide_json
WHERE path LIKE '/Users/%/Library/Application Support/Google/Chrome/Local State'
AND key = 'last_statistics_update';+------------------------+-------------------+
| key | value |
+------------------------+-------------------+
| last_statistics_update | 13278962099984926 |
+------------------------+-------------------+
WebKit offset to standard unixepoch:
SELECT
key,
datetime((CAST(value AS int)/1000000)-11644473600, 'unixepoch') AS chrome_restarted
FROM kolide_json
WHERE path LIKE '/Users/%/Library/Application Support/Google/Chrome/Local State'
AND key = 'last_statistics_update';+------------------------+---------------------+
| key | chrome_restarted |
+------------------------+---------------------+
| last_statistics_update | 2021-10-17 16:34:59 |
+------------------------+---------------------+
Calculating novel epoch offsets without going to Google:
If we know when a given epoch began, we can quickly determine the seconds offset using strftime like so:
SELECT strftime('%s','1601-01-01');
+-----------------------------+
| strftime('%s','1601-01-01') |
+-----------------------------+
| -11644473600 |
+-----------------------------+We could then dynamically calculate our timestamp using this generated offset as shown below:
SELECT
key,
datetime(
-- Divide WebKit timestamp by 1000000 to round down nanoseconds
(value/1000000)
-- Add the WebKit timestamp to the seconds value of 1601-01-01
+ (strftime('%s','1601-01-01')
), 'unixepoch') AS chrome_restarted FROM kolide_json
WHERE path LIKE '/Users/%/Library/Application Support/Google/Chrome/Local State'
AND key = 'last_statistics_update';+------------------------+---------------------+
| key | chrome_restarted |
+------------------------+---------------------+
| last_statistics_update | 2021-10-17 16:34:59 |
+------------------------+---------------------+
Converting Microsoft YMD LDAP dates
In rare instances, digging around the registry, you may come across Microsoft LDAP dates stored in the YMD format. These will typically look like:
20210325133550.000000-240Using some basic string manipulation and clever case statements, we can disassemble and reassemble these strings to produce valid unix timestamps:
WITH
sample AS (
SELECT '20210325133550.000000-240' AS last_logon
),split_time AS (
SELECT
last_logon,
CONCAT(
SUBSTR(last_logon,1,4),
'-',
SUBSTR(last_logon,5,2),
'-',
SUBSTR(last_logon,7,2),
' ',
SUBSTR(last_logon,9,2),
':',
SUBSTR(last_logon,11,2),
':',
SUBSTR(last_logon,13,2)
) AS timestamp_no_tz,
CAST(SUBSTR(last_logon,1,4) AS int) AS year,
CAST(SUBSTR(last_logon,5,2) AS int) AS month,
CAST(SUBSTR(last_logon,7,2) AS int) AS day,
CAST(SUBSTR(last_logon,9,2) AS int) AS hour,
CAST(SUBSTR(last_logon,11,2) AS int) AS minute,
CAST(SUBSTR(last_logon,13,2) AS int) AS seconds,
CASE WHEN last_logon LIKE '%-%' THEN '-'
WHEN last_logon LIKE '%+%' THEN '+'
END AS offset_type,
CASE WHEN last_logon LIKE '%-%'
THEN (CAST(SPLIT(last_logon,'-',1) AS float)/60)
WHEN last_logon LIKE '%+%'
THEN (CAST(SPLIT(last_logon,'+',1) AS float)/60)
END AS offset_amount
FROM sample
),offset_calculations AS (
SELECT *,
CAST(SPLIT(offset_amount, '.', 0) AS int) AS hour_offset,
CAST(SPLIT(offset_amount, '.', 1) AS int) minute_offset
FROM split_time)SELECT *,
CONCAT(
timestamp_no_tz,
offset_type,
substr(CONCAT('0',hour_offset), -2, 2),
':',
substr(CONCAT('0',minute_offset), -2, 2)
) AS timestamp
FROM offset_calculations; last_logon = 20210325133550.000000-240
timestamp_no_tz = 2021-03-25 13:35:50
timestamp = 2021-03-25 13:35:50-04:00
year = 2021
month = 3
day = 25
hour = 13
minute = 35
seconds = 50
offset_type = -
offset_amount = 4.0
hour_offset = 4
minute_offset = 0
9?! An extra little nugget for the folks that like to do silly things
Sadly, the osquery strftime function does not include all of the format strings available in other languages that utilize strftime. This means you cannot pretty print dates with things like:
Wednesday, December 8th, 2021 at 11:39PMUnless…
Using CTE Lookup Tables to format dates in arbitrary ways:
WITH
sample_date AS (
SELECT strftime('%s','now') AS sample_timestamp
),date_split AS (
SELECT
strftime('%Y',sample_timestamp,'unixepoch') AS year,
strftime('%m',sample_timestamp,'unixepoch') AS month_int,
strftime('%d',sample_timestamp,'unixepoch') AS day_int,
strftime('%H',sample_timestamp,'unixepoch') AS twelve_hour_int,
strftime('%M',sample_timestamp,'unixepoch') AS minute,
strftime('%w',sample_timestamp,'unixepoch') AS day_of_week_int
FROM sample_date
),dates_pretty_month (month_int,month) AS (VALUES
('01','January'),('02','February'),('03','March'),('04','April'),('05','May'),('06','June'),('07','July'),('08','August'),('09','September'),('10','October'),('11','November'),('12','December')
),dates_pretty_day (day_int,day) AS (VALUES
('01','1st'),('02','2nd'),('03','3rd'),('04','4th'),('05','5th'),('06','6th'),('07','7th'),('08','8th'),('09','9th'),('10','10th'),('11','11th'),('12','12th'),('13','13th'),('14','14th'),('15','15th'),('16','16th'),('17','17th'),('18','18th'),('19','19th'),('20','20th'),('21','21st'),('22','22nd'),('23','23rd'),('24','24th'),('25','25th'),('26','26th'),('27','27th'),('28','28th'),('29','29th'),('30','30th'),('31','31st')
),dates_pretty_day_of_week (day_of_week_int,day_of_week) AS (VALUES
('0','Sunday'),('1','Monday'),('2','Tuesday'),('3','Wednesday'),('4','Thursday'),('5','Friday'),('6','Saturday')
),dates_pretty_twelve_hour (twelve_hour_int,twelve_hour,am_pm) AS (VALUES
('00','12','AM'),('01','01','AM'),('02','02','AM'),('03','03','AM'),('04','04','AM'),('05','05','AM'),('06','06','AM'),('07','07','AM'),('08','08','AM'),('09','09','AM'),('10','10','AM'),('11','11','AM'),('12','12','PM'),('13','01','PM'),('14','02','PM'),('15','03','PM'),('16','04','PM'),('17','05','PM'),('18','06','PM'),('19','07','PM'),('20','08','PM'),('21','09','PM'),('22','10','PM'),('23','11','PM')
)SELECT
CONCAT(day_of_week, ', ', month, ' ', day, ', ', year, ' at ', twelve_hour, ':', minute, am_pm) AS written_date
FROM date_split
LEFT JOIN dates_pretty_month USING (month_int)
LEFT JOIN dates_pretty_day USING (day_int)
LEFT JOIN dates_pretty_day_of_week USING (day_of_week_int)
LEFT JOIN dates_pretty_twelve_hour USING (twelve_hour_int);+------------------------------------------+
| written_date |
+------------------------------------------+
| Wednesday, December 8th, 2021 at 11:39PM |
+------------------------------------------+
And there, we have a completely unnecessary demonstration of replacing missing format strings using Lookup Tables. I cannot imagine where you might need such functionality, but you have it at your disposal now!
Wrapping things up
Thanks for joining me on this deep-dive of SQLite and osquery interactions with date and time-based values. I hope that whether you were a total novice or a seasoned veteran of osquery, you were able to pick up some reusable tips and tricks.
Did you see something that got left out that will help others down the road? Hit me up in the comments below; I am always eager to expand my familiarity with SQL.
Further Reading
Interested in more how-to guides on Osquery? I recommend reading some of my other posts:
- Pivoting registry and plist data from EAV rows into columns using SQLite
- Running osquery as sudo(root) vs. user
- Spotlight search across every Mac in your fleet using osquery
Kolide is hiring!
Want to write cool queries like this and get paid? Kolide just raised 17MM in Series B funding, and we’re hiring! HMU in the comments or visit our recruiting link below:
About the Author:
Fritz Ifert-Miller is the UX designer for Kolide, an endpoint visibility platform that prioritizes the tenets of Honest Security. Fritz is responsible for Kolide’s design, user experience, and researching innovative ways to collect better ground truth from devices using osquery.
