fleetdm / fleet

Open-source platform for IT, security, and infrastructure teams. (Linux, macOS, Chrome, Windows, cloud, data center)
https://fleetdm.com
Other
2.7k stars 386 forks source link

Add last_opened_at for Homebrew, MacOS, and Windows #16005

Open Patagonia121 opened 6 months ago

Patagonia121 commented 6 months ago

Customer's feedback: Software in-use information is useful for various reason. E.g. when we need to prioritize a vulnerability, it plays an important factor whether it is actively used or not. Similarly it can help us to make informed decision what software can be cleaned up potentially, because they are not actively used. We are enforcing brew upgrades at specified times, so if there is a last accessed outside of the upgrade interval, there is a good chance that it has been used by the user. It's still not 100% of course, but at least it would give an indication.

JoStableford commented 6 months ago

Related to a Slack conversation

dernorberto commented 2 months ago

apparently the data about the available homebrew apps is available from Spotlight (https://github.com/osquery/osquery/issues/4470).

nonpunctual commented 1 month ago

The following are examples of queries that use the mdls table. (The mdls data is the macOS "Spotlight" metadata.) They are not directly applicable to this issue. They are placed here as a jumping off point to discover queries specific to this issue.

# macOS application metadata
SELECT ap.path,
       md.*
FROM apps AS ap
JOIN mdls AS md ON md.path=ap.path;

# macOS application metadata for a single application
SELECT ap.path,
       md.*
FROM apps AS ap
JOIN mdls AS md ON md.path='/Applications/Safari.app';

# macOS application metadata for a single application showing number of times application was opened
SELECT ap.path,
       md.*
FROM apps AS ap
JOIN mdls AS md ON md.path='/Applications/Safari.app'
WHERE KEY='kMDItemUseCount' LIMIT 1;