Mac OS X Live Forensics 109: OSQueryi

Hey all, I've recently started using OSQuery a lot more on OS X and I wanted to jot down some of my notes (Speaking of notes, it's been awhile since I've done an OS X Live Forensics piece, so I want to note this updated artifacts list). Ever since it's inception, osquery has been an instrumental tool in security monitoring on OS X systems at scale. It's really simple to install or view the source, and it's being used by a lot of large companies for monitoring now. While there is some extra awesome stuff people have been adding recently, today I'm gonna go over the basics. The wiki itself is extensive, but the osqueryi shell docs leave a bit to be desired. It's really all about the tables though, so I went through them, grabbed some commands from the IR pack, and tailored some commands myself to hobble together the following osqueryi cheatsheet. You can also enable a sweet easteregg by setting the following in your command line first: export ENHANCE=1. Then we will enter our osqueryi shell by issuing this command in the terminal osqueryi. And here is an osqueryi live response cheat sheet (all of these are commands for within the osqueryi shell):

Get the system information:
.all system_info;

Get the platform information:
.all platform_info;

Get the OS version:
.all os_version;

Check full disk encryption status:
.all disk_encryption;

Get installed applications:
select path, development_region, display_name, info_string, copyright from apps;

Get installed homebrew packages:
.all homebrew_packages;

Get all stored PKG information:
.all package_receipts;

List all unauthenticated sparkle feeds:
select feeds.*, p2.value as sparkle_version from (select a.name as app_name, a.path as app_path, a.bundle_identifier as bundle_id, p.value as feed_url from (select name, path, bundle_identifier from apps) a, preferences p where p.path = a.path || '/Contents/Info.plist' and p.key = 'SUFeedURL' and feed_url like 'http://%') feeds left outer join preferences p2 on p2.path = app_path || '/Contents/Frameworks/Sparkle.framework/Resources/Info.plist' where (p2.key = 'CFBundleShortVersionString' OR coalesce(p2.key, '') = '');

Get application to protocol scheme mappings:
.all app_schemes;

Retrieve all remembered wireless networks:
select ssid, network_name, security_type, last_connected, captive_portal, possibly_hidden, roaming, roaming_profile from wifi_networks;

Get keychain information:
.all keychain_items;
.all keychain_acls;

Get active directory configuration:
.all ad_config;

Get all users with a shell:
select * from users where shell!="/usr/bin/false";

Get the known DNS hosts and known SSH hosts:
.all known_hosts;
.all etc_hosts;

Get System Integrity Protection configuration:
.all sip_config;

Get all kernel information and extensions:
.all kernel_info;
.all kernel_extensions;

Get launch daemons:
select path, program, program_arguments from launchd;
.all launchd_overrides;

Get user startup items:
.all startup_items;

Get crontab items:
.all crontab;

Get login window persistence items:
select key, subkey, value from preferences where path = '/Library/Preferences/com.apple.loginwindow.plist';
select key, subkey, value from preferences where path = '/Library/Preferences/loginwindow.plist';
select username, key, subkey, value from preferences p, (select * from users where directory like '/Users/%') u where p.path = u.directory || '/Library/Preferences/com.apple.loginwindow.plist';
select username, key, subkey, value from preferences p, (select * from users where directory like '/Users/%') u where p.path = u.directory || '/Library/Preferences/loginwindow.plist';

Get browser plugins:
select browser_plugins.* from users join browser_plugins using (uid);

Get Safari extensions:
select safari_extensions.* from users join safari_extensions using (uid);

Get Chrome extensions:
select chrome_extensions.* from users join chrome_extensions using (uid);

Get FireFox add-ons:
select firefox_addons.* from users join firefox_addons using (uid);

Get all SUID binaries:
.all suid_bin;

Get application firewall configuration:
.all alf;
.all alf_exceptions;
select * from alf_services where state != 0;
.all alf_explicit_auths;

Get any IP forwarding configuration:
select * from system_controls where oid = '4.30.41.1' union select * from system_controls where oid = '4.2.0.1';

Get current ARP cache:
.all arp_cache;

Get current interface and IP addresses:
.all interface_addresses;

Get currently open sockets:
select distinct pid, family, protocol, local_address, local_port, remote_address, remote_port, path from process_open_sockets where path <> '' or remote_address <> '';

Get currently listening network ports and processes:
select distinct process.name, listening.port, listening.address, process.pid from processes AS process join listening_ports as listening on process.pid = listening.pid;

Get currently open files:
select distinct pid, path from process_open_files where path not like '/private/var/folders%' and path not like '/System/Library/%' and path not in ('/dev/null', '/dev/urandom', '/dev/random');

Get brief current processes:
select pid, name, path from processes;

Get processes where the binary is no longer on disk:
select pid, name, path from processes where on_disk = 0;

Get all current USB devices:
.all usb_devices;

Get all current mounted drives:
.all mounts;

Get all current NFS shares:
.all nfs_shares;

Get all currently logged in users:
select liu.*, p.name, p.cmdline, p.cwd, p.root from logged_in_users liu, processes p where liu.pid = p.pid;

Get the last login information:
.all last;

Get command line history:
.all shell_history;

Get all XProtect malware reports:
.all xprotect_reports;