https://github.com/osquery/osquery logo
#macos
Title
# macos
s

Steve Poe

08/06/2021, 6:06 PM
Under what conditions is the osquery table
package_install_history
updated? I installed two apps yesterday (Evernote and Spotify via brew) and I'd expect them to show up in the package history? I am using osquery 4.9 on macOS 10.15.7. Current uptime: 3 days.
t

theopolis

08/07/2021, 2:31 AM
I don't think brew installs via
pkgutil
(it might in some cases) but that table is reporting Installer packages https://en.wikipedia.org/wiki/Installer_(macOS)
does
select * from homebrew_packages
work?
s

seph

08/07/2021, 3:39 PM
package_install_history
reads one of the apple plists. As Teddy says, brew doesn’t use that.
s

Steve Poe

08/08/2021, 2:23 AM
@theopolis @seph thanks for your responses. I presumed that even a package installed by brew would still update the plist from the package maintainer such as Spotify or Evernote which I just installed as tests. I guess I need to rethink my approach for install history.
s

Steve Poe

08/10/2021, 7:15 PM
@allister thank you. 🤔 Updating the /Library/Receipts/InstallHistory.plist is optional then for a vendor/package maintainer. Context: I am working with our Security team to generate queries through Carbon Black AV of what our installed weekly on company laptops.
a

allister

08/10/2021, 11:54 PM
I think you'd have to cross-reference the homebrew table with the file table to get dates, I'm still thinking about how I'd best want to audit this but brew isn't something I've studied enough to understand its operation
s

seph

08/10/2021, 11:55 PM
I think it's hard to do in a query. I'd do this at the TLS server.
s

Steve Poe

08/11/2021, 12:38 AM
@allister we do use brew, and our developers are familiar with brew, but package deployment happens via Jamf. I appreciate your feedback that I should consider brew output as well.
a

allister

08/11/2021, 12:40 AM
jamf pkg management is… not good but you probably already know that (no versions on pkg receipts, no install history)
s

Steve Poe

08/11/2021, 12:42 AM
@allister 😾 yes, I know. But it is what we have. I am not dependent on the audit logs for it. This is why I like Carbon Black for built-in osquery.
a

allister

08/11/2021, 12:42 AM
we mash receipts and history with one query for 'everything' pkg-wise (which doesn't include brew)
Copy code
SELECT pih.name,
       pih.package_id,
       receipts.version AS receipt_version,
       pih.version,
       receipts.location,
       receipts.installer_name,
       datetime(max(pih.time), 'unixepoch') AS datetime_utc
FROM package_install_history AS pih
JOIN package_receipts AS receipts ON pih.package_id = receipts.package_id
GROUP BY pih.name;
s

Steve Poe

08/11/2021, 1:06 AM
@allister thanks for sharing. The one I did, much longer than yours, excludes the base package id and trying to only report for the last 7 days (for Security team). This looks much better than depending on the package history table. 😃
y

yossarian

08/11/2021, 9:32 PM
putting on my homebrew maintainer hat: AFAIK we don’t have any centralized journal or manifest for package install/uninstall events, but it’s good to know that this is a place it could be useful (we’ve debated it before)
s

seph

08/11/2021, 10:15 PM
FWIW the existing homebrew table crawls the cellar to find packages. Not sure there’s a cleaner way