https://github.com/osquery/osquery logo
Join Slack
Powered by
We have some macOS software items listed as being ...
# fleet
s
We have some macOS software items listed as being last opened "over 46 years ago". This is different than "Never." Obviously neither the software nor the device existed in 1980, but curious where that date is coming from. The unix epoch is 1/1/1970, and the Mac release was 1/24/1984.
f
can you share a specific query you are running that returns these rows? maybe an sqlite rounding issue?
s
It's not a query, it's just the Fleet built in software per host view
f
ah, likely there is still a query backing that, very possible the datatype of which ever column is being inspected there has an issue.
if you look on the actual host and pull filesystem timestamps I'd be curious what they are as well.
s
Any query is abstracted to custom tables in the fleet db first, then that data is what's shown in the UI
looking
The Fleet DB has 1980-01-01 000000 UTC stored. Trying an osquery.
315532800.0
f
it looks like the host software report UI cases last_opened_time 
-1.0
to 
Never
app query: 
SELECT name, bundle_identifier, bundle_version, info_string, last_opened_time FROM apps;
hosts UI representation:
there may be a rounding bug, typically you can convert the epoch last_opened_time to a datetime/date in the sql so we can probably verify
in the hosts UI i also see 
Not supported
so not sure about that but likely unrelated to your issue here.
s
I thought maybe it was a binary overflow but no
f
I have a legitimate app that the hosts ui reports as 
over 5 years ago
and the apps query reports 
1606013809.0
so we can convert that in sql and see this.
so the sqlite DATETIME() method or one of the arguments to it may be causing an issue. We can see here that previously the 
-1.0
value is actually converted to 1969...
Copy code
SELECT name, bundle_identifier, bundle_version, info_string, last_opened_time, datetime(last_opened_time, 'unixepoch', 'localtime') FROM apps WHERE name LIKE "REI%";
1969 is over 46 years ago, but all of the other apps that report -1.0 are reported as 
Never
in the UI as you noted.
maybe localtime induces a sway because I am NYC/EDT and 
localtime
resolves, and actually goes "pre-epoch"
s
Epoch is UTC
f
correct
s
Screenshot 2026-04-14 at 8.55.04 AM.png
I've just never noticed the 1/1/1980 date
(we're UTC-6)
f
maybe different versions of touch or some other file creation default use 1980 vs 1970 when creating files and there is a valid discrepancy across the os? no idea
seems like fleet could smooth things out by saying "if last opened time > X return 'Never' "?
s
Seems to only be showing for a handful of software titles. Perhaps a build artifact.
g
Hey both, I've just been reading through your discussion. And this is definitely worth opening a feature request for.
seems like fleet could smooth things out by saying "if last opened time > X return 'Never' "?
Happy to do this for you as soon as I get some time! But we also welcome anyone to do it: https://github.com/fleetdm/fleet/issues
f
I can open an FR in git, ok to put slack thread links in git? rather not recreate this thread if possible.
👍 1
s
I usually summarize and link
z
slack threads are okay to post @FG thanks!
👍 1
f
I can edit this if the request is unclear @Gray Williams @Zay Hanlon https://github.com/fleetdm/fleet/issues/43622
🙌 1
z
looks great! thanks frank. i've let our product team know
😎 1
g
Amazing, thank you @FG!
👍 1
Hey @FG / @Shawn Maddock - Just wanted to share with you that a fix for this was shipped in 4.87 (github.com/fleetdm/fleet/issues/43622) Thank you for reporting!
😎 1
14 Views
Open in Slack
PreviousNext
Powered by