osquery – Snacktakuläres Open-Source System- und Securitymonitoring

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 installieren
export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B$
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys $OSQUERY_KEY$
add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'$
apt update && apt install osquery

Unter „/etc/osquery/osquery.conf“ liegt die zentrale Osquery-Konfiguration, in der man das Verhalten von Osquery einstellen und z.B verschiedene Plugins (Packs) freischalten kann.

#===========================================================
# Zentrale Osquery-Datei
#===========================================================
{
  "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": {
     "ossec-rootkit": "/usr/share/osquery/packs/ossec-rootkit.conf",
     "it-compliance": "/usr/share/osquery/packs/it-compliance.conf",
     "vuln-management": "/usr/share/osquery/packs/vuln-management.conf",
     "incident-response": "/usr/share/osquery/packs/incident-response.conf",
     "osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf",
     "hardware-monitoring": "/usr/share/osquery/packs/hardware-monitoring.conf"
  }
}

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

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

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

# CLI Abfragen mit verschiedenen Ausgabeformaten
osqueryi --csv "SELECT * FROM system_info;"
osqueryi --list "SELECT * FROM system_info;"
osqueryi --json "SELECT * FROM system_info;"

#===========================================================
# Ausgabe Infos über OSquery selber
#===========================================================
osqueryi ".show"

#===========================================================
# Ausgabe bestimmter Abfrage-Tabellen
#===========================================================
osqueryi ".schema"
osqueryi ".schema users"
osqueryi ".schema processes"
osqueryi ".schema system_info"

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

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

#===========================================================
# Benutzer-Abfragen
#===========================================================
osqueryi ".schema users"
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;"

#===========================================================
# Prozess-Abfragen
#===========================================================
osqueryi ".schema processes"
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;"

#===========================================================
# Cronjob-Abfragen
#===========================================================
osqueryi ".schema crontab"
osqueryi "SELECT command, path FROM crontab;"
osqueryi "SELECT minute, hour, day_of_month, month, day_of_week, command, path FROM crontab;"

#===========================================================
# Firewall-Abfragen
#===========================================================
osqueryi ".schema iptables"
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;"

#===========================================================
# Mountpount-Abfragen
#===========================================================
osqueryi ".schema 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';"

#===========================================================
# Kernel-Abfragen
#===========================================================
osqueryi ".schema kernel_modules"
osqueryi "SELECT * FROM kernel_info;"
osqueryi "SELECT name, used_by, status FROM kernel_modules where status='Live';"

#===========================================================
# Netzwerk-Abfragen
#===========================================================
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;"

#===========================================================
# Paket-Abfragen
#===========================================================
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;"

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