macos_osquery

macos_osquery

#all local user accounts that exist on a machine
SELECT * FROM users WHERE username NOT LIKE '\_%' ESCAPE '\';

# finds all users who have actual login shells 
SELECT * FROM users WHERE shell NOT LIKE '%false' AND shell NOT LIKE '%true';

#listening ports
SELECT p.name, address, port, family, protocol, 
  COALESCE(NULLIF(pos.path,''), p.path) AS path
FROM listening_ports AS pos
INNER JOIN processes AS p ON p.pid = pos.pid
WHERE address <> ""
  AND port != 0
  AND pos.pid > 0
LIMIT 5;

#information about the specified file on disk
SELECT file.path, users.username AS owner,
groups.groupname AS groups,
datetime(file.btime,'unixepoch') AS created,
datetime(file.mtime,'unixepoch') AS last_mod,
ROUND((file.size * 10e-7),4) AS size_mb
FROM file
JOIN users USING (uid)
JOIN groups USING (gid)
WHERE path LIKE '/Users/%/Downloads/%%'
ORDER BY last_mod DESC;

#shell_history,search for the executed commands on the system.
SELECT uid,
username,
shell,
command
FROM users
JOIN shell_history USING (uid);

#sudo rules present on a system.
SELECT * FROM sudoers;
SELECT * FROM sudoers WHERE rule_details LIKE '%ALL';

#List system information
select cpu_type, cpu_brand, hardware_vendor, hardware_model from system_info;

#Find remaining hard disk storage
select path, type, round((blocks_available * blocks_size *10e-10),2) as gigs_free from mounts where path='/';

#Find apps installed on machine
select display_name, bundle_short_version, bundle_version from apps limit 1;

#Find the process running on port 8080
select pid from listening_ports where port = 8080;
#join it with processes 
select p.pid, p.name, p.state,p.uid, lp.port from processes p join listening_ports lp on p.pid = lp.pid and lp.port=8080;

#Find top 5 most CPU intensive processes
SELECT pid, uid, name, ROUND((
  (user_time + system_time) / (cpu_time.tsb - cpu_time.itsb)
) * 100, 2) AS percentage
FROM processes, (
SELECT (
  SUM(user) + SUM(nice) + SUM(system) + SUM(idle) * 1.0) AS tsb,
  SUM(COALESCE(idle, 0)) + SUM(COALESCE(iowait, 0)) AS itsb
  FROM cpu_time
) AS cpu_time
ORDER BY user_time+system_time DESC
LIMIT 5;

#Find top 5 most memory intensive processes
SELECT pid, name, ROUND((total_size * '10e-7'), 2) AS used FROM processes ORDER BY total_size DESC LIMIT 5;

#who is logged into the system
select * from logged_in_users;

#List Python packages installed in default Python
select name, version from python_packages;

# Find the last docker image installed in my local registry
select id,tags from docker_images order by created desc limit 1;

#find processes that are running whose binary has been deleted from the disk
SELECT name, path, pid FROM processes WHERE on_disk = 0;

# audited socket events
sudo osqueryd -S --disable_events=false --audit_allow_sockets --disable_audit=false

#CTI, DFIR, OSX
Finding specific indicators of compromise (IOCs) in memory or on disk; Facebook has provided the queries which detect Hacking Team’s OSX backdoor by querying for specific persistent mechanisms and file system activity on OSX

select * from file where path = '/dev/ptmx0';
select * from apps where bundle_identifier = 'com.ht.RCSMac' or bundle_identifier like 'com.yourcompany.%' or bundle_package_type like 'OSAX';
select * from launchd where label = 'com.ht.RCSMac' or label like 'com.yourcompany.%' or name = 'com.apple.loginStoreagent.plist' or name = 'com.apple.mdworker.plist' or name = 'com.apple.UIServerLogin.plist';