f10e33d387
* add deploy category, change product to guides * update links to deployment guides * Update deploy-fleet-on-hetzner-cloud.md * Update enrolling-a-digital-ocean-droplet-on-a-fleet-instance.md * Update how-to-install-osquery-and-enroll-linux-devices-into-fleet.md * Update delivering-data-to-snowflake-from-fleet-and-osquery.md * Update how-to-install-osquery-and-enroll-windows-devices-into-fleet.md * Update how-to-install-osquery-and-enroll-macos-devices-into-fleet.md * Update deploying-fleet-on-aws-with-terraform.md * Update deploying-fleet-on-render.md * Update how-to-uninstall-osquery.md * Update osquery-a-tool-to-easily-ask-questions-about-operating-systems.md * Update get-and-stay-compliant-across-your-devices-with-fleet.md * Update work-may-be-watching-but-it-might-not-be-as-bad-as-you-think.md * Update converting-unix-timestamps-with-osquery.md * Update correlate-network-connections-with-community-id-in-osquery.md * Update using-elasticsearch-and-kibana-to-visualize-osquery-performance.md * Update fleet-quick-tips-querying-procdump-eula-has-been-accepted.md * Update locate-assets-with-osquery.md * Update osquery-consider-joining-against-the-users-table.md * Update import-and-export-queries-and-packs-in-fleet.md * Update ebpf-the-future-of-osquery-on-linux.md * Update generate-process-trees-with-osquery.md * Update get-and-stay-compliant-across-your-devices-with-fleet.md * Update work-may-be-watching-but-it-might-not-be-as-bad-as-you-think.md * Update ebpf-the-future-of-osquery-on-linux.md * Change category meta value back to guides Co-authored-by: Desmi-Dizney <99777687+Desmi-Dizney@users.noreply.github.com> * change article category * update latest article category * add redirects for articles not handled by cloudflare rules * Update to main nav I did a little more housekeeping on the main nav for when we do the switch on these categories. - Added link to /deploy under Guides on the Documentation dropdown. - Added link to /guides under Guides on the Documentation dropdown - Removed the now redundant "See all" from under Guides on the Documentation dropdown - Removed the now redundant "See all" from under Articles on the Community dropdown (There's no need to point users to All categories any more, since all the category land are now linked to independently from the main nav. * Update article category name Co-authored-by: Desmi-Dizney <99777687+Desmi-Dizney@users.noreply.github.com> Co-authored-by: Mike Thomas <mthomas@fleetdm.com>
3.1 KiB
Converting unix timestamps with osquery
Human readable timestamps
Unix timestamps can be confusing for even the smartest Time Lord.
If you are anything like me, and unix timestamps leave you thinking about the mysterious numbers in Lost, you’re going to want to convert them into something more human friendly. Running your timestamp through any number of online converters is one way to go, but it’s a clunky process.
Hmm… 10800? That’s Thursday, January 1, 1970 3:00:00 AM, if I’m not mistaken.
Thankfully, we can easily convert unix timestamps directly in osquery:
SELECT
unixtime,
datetime(unixtime, 'unixepoch') AS timestamp
FROM
(SELECT 1623366772 AS unixtime);
unixtime = 1623366772
timestamp = 2021-06-10 23:12:52
The above query returns the time in UTC, but what if we want to get the local timestamp for the system being queried?
SELECT
datetime(unixtime, 'unixepoch') AS timestamp,
datetime(unixtime, 'unixepoch', 'localtime') AS local_timestamp FROM
(SELECT 1623366772 AS unixtime);
timestamp = 2021-06-10 23:12:52
local_timestamp = 2021-06-11 8:12:52
We can take this further by baking this idea into any of our queries. Let’s run a simple query to get all running processes on our host.
SELECT
name, cmdline, start_time
FROM
processes
As you can see, we have start_time listed in unix time again.
So let’s augment our query with the datetime line from before to give us a more human friendly output for start_time.
SELECT
name, cmdline,
datetime(start_time, 'unixepoch') AS start_time
FROM
processes
And finally, as before, we can of course output that data as the local time for our host by you guessed it, adding localtime to our query.
SELECT
name, cmdline,
datetime(start_time, 'unixepoch', 'localtime') AS start_time
FROM
processes
So there we go. Simple, human readable timestamps with osquery.
Could this post be more helpful?
Let us know if you can think of any other example scenarios you’d like us to cover.