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.

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)

package_install_history

reads one of the apple plists. As Teddy says, brew doesn’t use that.

@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.

/Library/Receipts/InstallHistory.plist

as per https://github.com/osquery/osquery/blob/08736648aacaefbdfc90bc2b87acc4414fd6c9ec/osquery/tables/system/darwin/packages.mm#L37-L38

@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.

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

I think it's hard to do in a query. I'd do this at the TLS server.

@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.

jamf pkg management is… not good but you probably already know that (no versions on pkg receipts, no install history)

@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.

we mash receipts and history with one query for 'everything' pkg-wise (which doesn't include brew)

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;

@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. 😃

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)

FWIW the existing homebrew table crawls the cellar to find packages. Not sure there’s a cleaner way