Osquery ist ein super-intergalaktisch-geiles Open-Source (GPLv2) Security-Programm, welches eine plattformübergreifende Systemüberwachung basierend auf fesche SQL-Abfragen ermöglicht. Hierbei können z.B Abfragen zu gerade laufenden Prozessen, offenen Netzwerkverbindungen, aktiven Firewall-Regeln, installierter Software,  oder aber auch Infos zu dem System und den angelegten Benutzern in verschiedenen Formaten aufgetischt werden.

In diesem Blogeintrag gibt es eine Quick-n-Dirty-Anleitung, wie du Osquery auf einem Ubuntu-System installierst und mit ein paar Beispiel-Abfragen zu deiner Bitsch machst. Für weiteren geilen, sehr erweiterbaren Scheiß gibt es die sehr ergiebige Dokumentation

Osquery fix installieren

# Osquery installieren
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
add-apt-repository "deb [arch=amd64] https://osquery-packages.s3.amazonaws.com/xenial xenial main"
apt update
apt install osquery
cp /etc/osquery/osquery.conf /etc/osquery/osquery.conf.orig

Osquery konfigurieren

#/etc/osquery/osquery.conf
{
  "options": {
    "config_plugin": "filesystem",
    "logger_plugin": "filesystem",
    "logger_path": "/var/log/osquery",
    "disable_logging": "false",
    "schedule_splay_percent": "10",
    "pidfile": "/var/osquery/osquery.pidfile",
    "events_expiry": "3600",
    "database_path": "/var/osquery/osquery.db",
    "verbose": "false",
    "worker_threads": "2",
    "enable_monitor": "true",
    "disable_events": "false",
    "disable_audit": "false",
    "audit_allow_config": "true",
    "host_identifier": "hostname",
    "enable_syslog": "true",
    "audit_allow_sockets": "true",
    "schedule_default_interval": "3600"
  },
  "schedule": {
    "crontab": {
      "query": "SELECT * FROM crontab;",
      "interval": 300
    },
    "system_profile": {
      "query": "SELECT * FROM osquery_schedule;"
    },
    "system_info": {
      "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
      "interval": 3600
    }
  },
  "decorators": {
    "load": [
      "SELECT uuid AS host_uuid FROM system_info;",
      "SELECT user AS username FROM logged_in_users ORDER BY time DESC LIMIT 1;"
    ]
  },
  "packs": {
     "it-compliance": "/usr/share/osquery/packs/it-compliance.conf",
     "vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
     "osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf",
     "incident-response": "/usr/share/osquery/packs/incident-response.conf",
     "hardware-monitoring": "/usr/share/osquery/packs/hardware-monitoring.conf",
  }
}

Osquery Beispielabfragen

Die Abfragen können entweder direkt in der interaktiven Osquery-Shell „osqueryi“ eingegeben, oder wie hier mit dem Befehl osqueryi „$BEFEHL;“ fix rausgepeitsch werden.

# Ausgabe aller verfügbaren Osquery-Tabellen
osqueryi ".table"

# Ausgabe bestimmter Tabellen
osqueryi ".schema"
osqueryi ".schema users"
osqueryi ".schema processes"

# Ausgabemöglichkeiten OSquery
osqueryi ".mode csv"
osqueryi ".mode list"
osqueryi ".mode column"
osqueryi ".mode line"
osqueryi ".mode pretty"

# Ausgabe OSquery Infos
osqueryi ".show"

# Ausgabe aller verfügbaren OSquery-Plugins/Packs
osqueryi "SELECT name FROM osquery_schedule;"

# Systeminfos
osqueryi "SELECT * FROM uptime;"
osqueryi "SELECT * FROM system_info;"

# Benutzer
osqueryi "SELECT * FROM users;"
osqueryi "SELECT COUNT(*) FROM users;"
osqueryi "SELECT * FROM last;"
osqueryi "SELECT * FROM logged_in_users;"
osqueryi "SELECT * FROM users WHERE uid>=1000;"
osqueryi "SELECT username, time, host FROM last WHERE type=7;"
osqueryi "SELECT uid, gid, username, description, directory FROM users;"
osqueryi "SELECT uid, gid, username, description, directory FROM users WHERE uid > 500;"
osqueryi "SELECT username, time, host FROM last WHERE username NOT LIKE 'root%' ORDER BY username;"
osqueryi "SELECT u.username, g.gid, g.groupname FROM users u JOIN user_groups ug USING (uid) JOIN groups g ON ug.gid = g.gid WHERE uid > 500;"

# Prozesse
osqueryi "SELECT pid, name, path FROM processes LIMIT 10;"
osqueryi "SELECT pid, name, path, cmdline FROM processes;"
osqueryi "SELECT pid, name, path, cmdline FROM processes WHERE name LIKE 'apache%' ORDER BY name;"
osqueryi "SELECT p.pid, p.name, u.uid, u.username FROM processes AS p LEFT JOIN users AS u ON u.uid = p.uid;"
osqueryi "SELECT pid, username, name FROM processes p JOIN users u ON u.uid = p.uid ORDER BY start_time DESC LIMIT 10;"
osqueryi "SELECT pid, name, ROUND((total_size * '10e-7'), 2) AS used FROM processes ORDER BY total_size DESC LIMIT 10;"
osqueryi "SELECT pid, username, state, name, path, cwd, user_time, system_time FROM processes p JOIN users u ON u.uid = p.uid ORDER BY start_time DESC LIMIT 10;"
osqueryi "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;"

# Cron
osqueryi "SELECT command, path FROM crontab;"

# Setuid-Files
osqueryi "SELECT * FROM suid_bin;"
osqueryi "SELECT * FROM suid_bin WHERE username='root' AND groupname='nobody' order by path;"

# Firewall
osqueryi "SELECT * FROM iptables;"
osqueryi "SELECT chain, policy, src_ip, dst_ip FROM iptables;"
osqueryi "SELECT chain, policy, src_ip, dst_ip FROM iptables WHERE chain='POSTROUTING' order by src_ip;"

# Kernel
osqueryi "SELECT name, used_by, status FROM kernel_modules where status='Live';"

# Network
osqueryi "SELECT * FROM routes;"
osqueryi "SELECT * FROM etc_hosts;"
osqueryi "SELECT * FROM listening_ports;"
osqueryi "SELECT * FROM interface_details;"
osqueryi "SELECT * FROM interface_addresses;"
osqueryi "SELECT interface, mac, ipackets, opackets, ibytes, obytes FROM interface_details;"

# Prozessname, Port und PID für laufende Prozesse auf allen Interfaces
osqueryi "SELECT DISTINCT processes.name, listening_ports.port, processes.pid FROM listening_ports JOIN processes USING (pid) WHERE listening_ports.address = '0.0.0.0';"
osqueryi "SELECT DISTINCT process.name, listening.port, listening.address, process.pid FROM processes AS process JOIN listening_ports AS listening ON process.pid = listening.pid;"

# Pakete
osqueryi "SELECT * FROM apt_sources;"
osqueryi "SELECT * FROM deb_packages;"
osqueryi "SELECT name, version FROM deb_packages ORDER BY name;"
osqueryi "SELECT name, version FROM deb_packages WHERE name='vim';"
osqueryi "SELECT name, version FROM deb_packages WHERE name NOT LIKE 'apache%' ORDER BY name;"
osqueryi "SELECT name, base_uri, release, maintainer, components FROM apt_sources ORDER BY name;"

# Mounts
osqueryi "SELECT * FROM mounts;"
osqueryi "SELECT device, path, type, inodes_free, flags FROM mounts;"
osqueryi "SELECT device, path, type, inodes_free, flags FROM mounts WHERE type='ext4';"
osqueryi "SELECT device, path, type, inodes_free, flags FROM mounts WHERE type='tmpfs';"

# Misc
osqueryi "SELECT * FROM uptime;"
osqueryi "SELECT * FROM os_version;"
osqueryi "SELECT hostname, cpu_type, physical_memory, hardware_vendor, hardware_model FROM system_info;"