windows_osquery

windows_osquery

PS C:\Users\Administrator> osqueryi
Using a virtual database. Need help, type '.help'

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

#shows the top 10 memory hogs running on a system.
SELECT pid, name,
ROUND((total_size * '10e-7'), 2) AS memory_used
FROM processes
ORDER BY total_size DESC LIMIT 10;

#(process_open_sockets) - Searches for processes making network connections, other than web connections.
SELECT s.pid, p.name, local_address, remote_address, family,
protocol, local_port, remote_port
FROM process_open_sockets s
JOIN processes p ON s.pid = p.pid
WHERE remote_port NOT IN (80, 443)
AND local_port NOT IN (0)
AND family = 2;

#address families and protocols
SELECT DISTINCT family, protocol FROM process_open_sockets;

#breakdown of address families and protocols
SELECT (
  CASE family 
  WHEN 2 THEN 'IP4' 
  WHEN 10 THEN 'IP6' 
  ELSE family END
) AS family, (
  CASE protocol 
  WHEN 6 THEN 'TCP' 
  WHEN 17 THEN 'UDP' 
  ELSE protocol END
) AS protocol, local_address, local_port, 
  remote_address, remote_port 
FROM process_open_sockets 
WHERE family IN (2, 10) 
AND protocol IN (6, 17) 
LIMIT 4;

#all the open socket connections in use by processes on the system
# all the inbound and outbound connections to and from running processes
SELECT pos.pid, local_address, local_port, 
  remote_address, remote_port, family, protocol, 
  COALESCE(NULLIF(pos.path,''), p.path) AS path
FROM process_open_sockets AS pos
INNER JOIN processes AS p ON p.pid = pos.pid
WHERE remote_address <> ""
  AND remote_port != 0
  AND pos.pid > 0
LIMIT 5;

#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;

# Search for the browser extensions running Chrome.The following query eliminates duplicates, and shows all extensions for all users
SELECT DISTINCT c.name, u.username
FROM users u
JOIN chrome_extensions c USING (uid)
ORDER BY c.name;

#information about the specified file on disk
SELECT file.path, users.username AS owner,
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)
WHERE path LIKE 'c:\Users\%\Downloads\%%'
ORDER BY last_mod DESC;

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

#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;

#find backdoors on the server is to run a query that lists all the listening ports
select * from listening_ports ;

#Check the processes that have a deleted executable
SELECT * FROM processes WHERE on_disk = 0;

#Get the process name, port, and PID, for processes listening on all interfaces:
SELECT DISTINCT processes.name, listening_ports.port, processes.pid
  FROM listening_ports JOIN processes USING (pid)
  WHERE listening_ports.address = '0.0.0.0';

#Find every macOS LaunchDaemon that launches an executable and keeps it running:
SELECT name, program || program_arguments AS executable
  FROM launchd
  WHERE (run_at_load = 1 AND keep_alive = 1)
  AND (program != '' OR program_arguments != '');

#Check for ARP anomalies from the host's perspective:    
SELECT address, mac, COUNT(mac) AS mac_count
  FROM arp_cache GROUP BY mac
  HAVING count(mac) > 1;

#Alternatively, Check for ARP anomalies from the host's perspective:
  SELECT address, mac, mac_count
    FROM
      (SELECT address, mac, COUNT(mac) AS mac_count FROM arp_cache GROUP BY mac)
    WHERE mac_count > 1;

osquery> .all firefox_addons;
osquery> .all  chrome_extensions;