Explorer Son Système avec Osquery : L’Analyse Système Distillée par le SQL

Dans le domaine de l’administration système et de la sécurité, une visibilité complète sur l’infrastructure est cruciale. Qu’il s’agisse de gérer les configurations, de détecter des anomalies ou de réaliser des audits de sécurité, disposer d’informations détaillées et en temps réel sur les systèmes est indispensable.

Les ingénieurs systèmes s’appuient quotidiennement sur des commandes POSIX incontournables comme top, ps, lsof, netstat ou encore dmesg. Pour analyser les résultats, des outils tels que grep ou awk sont souvent utilisés. Ces commandes sont ensuite enchaînées via des pipes et intégrées dans de vastes scripts shell pour automatiser les tâches.

Bien que ces scripts aient longtemps rendu service, ils deviennent souvent complexes à maintenir, refactoriser ou partager. La compréhension de leur logique peut s’avérer ardue, surtout lorsque des enchaînements complexes de commandes sont dissimulés dans des structures peu lisibles.

Exemple :Une commande qui affiche uniquement les processus bash temporairement suspendus ou arrêtés, puis filtre les résultats pour ne montrer que l’UID, la commande en cours d’exécution et l’état.

Ici, bien sûr, il n’y a rien de compliqué, mais peut-être que cela manque de détails. Imaginez qu’avec une requête SQL comme celle-ci :

l’on puisse obtenir le même résultat et ainsi l’on puisse requêter différentes parties de nos systèmes comme s’il s’agissait de tables dans une BDD ?

Ceci est possible ! Grâce à OSQuery, vous pourrez désormais interroger votre système en SQL !

Qu’est-ce qu’Osquery ?

Osquery est un outil open-source (sous licences GPL-2 et Apache 2) qui permet d’interroger des systèmes informatiques — serveurs, postes de travail, etc. — via des requêtes SQL. Initialement développé par Meta (anciennement Facebook) en 2014, puis soutenu par la Linux Foundation depuis 2019, Osquery offre la possibilité de collecter des informations système telles que les processus en cours, les connexions réseau, les utilisateurs connectés, l’état des disques et bien plus encore. Chaque type d’information est présenté sous forme de « table », à la manière d’une base de données relationnelle.

Grâce à sa syntaxe SQL intuitive, Osquery se révèle puissant et accessible, ce qui en fait un outil de choix pour les administrateurs systèmes, ingénieurs DevOps et professionnels de la sécurité.

Parmi les entreprises qui l’utilisent dans leurs infrastructures, on retrouve notamment Meta, Uber, Dropbox, Cloudflare, Okta, Airbnb, Netflix, et bien d’autres (1).

Comment installer OSQuery ?

Osquery est compatible avec Windows, Linux et macOS. Bien qu’une installation soit possible sur des systèmes BSD-like (comme FreeBSD et OpenBSD), elle n’est pas directement proposée via les ports BSD et demandera de l’adaptation. À noter que macOS, bien que basé sur BSD, est déjà pris en charge nativement.

Cet article se concentre sur l’installation d’Osquery sur AlmaLinux, une distribution basée sur RHEL. La même procédure s’applique également à Fedora, RockyLinux et RHEL.

Pour installer Osquery sous Windows ou macOS, consultez la section Pour aller plus loin.

Étapes d’installation :

Rendez-vous sur la page de téléchargement d’OSQuery : https://osquery.io/downloads/official/.
Sous la rubrique Alternative Install Options, sélectionnez RPM Linux.
Suivez les instructions pour récupérer le certificat officiel et ajouter le dépôt YUM

Une fois cela fait, OSQuery sera installé sur votre système !

Maintenant nous allons étudier les deux façons d’utiliser Osquery, à savoir le mode interactif et le mode Daemon.

Le mode intEractif

Le mode interactif d’Osquery permet d’exécuter des requêtes SQL en temps réel sur votre système, via une interface similaire à celle des SGBD comme $psql ou $mysql. Ce mode est idéal pour :

Tester des requêtes rapidement.
Déboguer des problèmes.
Explorer les données système sans écrire de scripts complexes.

Pour accéder au mode interactif, exécutez simplement la commande suivante :

Ce mode vous permet de :

Tester vos requêtes en temps réel.
Déboguer des problèmes.
Explorer votre système, tout comme vous le feriez avec d’autres outils interactifs comme Python, Ruby (irb), etc.

Commandes utiles en mode interactif

Avant de lancer des requêtes SQL, voici quelques commandes essentielles à connaître :

Afficher les tables disponibles

Pour lister toutes les tables disponibles dans Osquery, vous pouvez utiliser la commande :

.tables

Cela affichera toutes les tables disponibles, actuellement au nombre de 154 sur Linux, avec certaines tables compatibles sur plusieurs plateformes. Toutefois, certaines tables peuvent ne pas fonctionner selon le système. Par exemple, la table apt_sources est présente dans Osquery, mais si vous l’exécutez sur une machine utilisant une distribution RHEL, vous obtiendrez une erreur indiquant qu’Osquery ne parvient pas à lire le fichier /etc/apt/sources.list.

Afficher les colonnes d’une table

Pour voir les colonnes d’une table spécifique, vous pouvez utiliser la commande suivante :

.schema nom_de_la_table

Cela affichera les colonnes de la table avec leurs types, l’équivalent de la commande SQL DESCRIBE. Pour plus de détails, vous pouvez consulter la rubrique Schéma2 sur le site officiel d’Osquery, en filtrant selon votre OS afin d’obtenir des explications adaptées et une meilleure compréhension des tables disponibles.

Exemple : la table arp_cache est disponible sur macOS, Linux et Windows (tables multi-plateformes).

Changer le format d’affichage des résultats

Par défaut, lorsqu’une requête est exécutée, le résultat est affiché dans un format « pretty », ce qui est lisible mais peut devenir difficile à interpréter pour des ensembles de données plus volumineux.

C’est pourquoi Osquery propose plusieurs formats d’affichage. Voici les options disponibles :

1. .mode csv

Affiche les résultats au format CSV. Idéal pour l’exportation de données. Une fois cette commande exécutée, toutes les requêtes de la session afficheront les résultats dans ce format.

2. .mode list

Affiche les noms des colonnes sur la première ligne, puis chaque donnée est séparée par un tiret (-) pour indiquer à quelle colonne elle appartient.

3. .mode line

Un approfondissement du mode list, où chaque ligne affiche le nom de la colonne suivi de la donnée (format clé-valeur).

4. .mode column

Semblable au mode pretty, mais sans les bordures en ASCII.

5. .mode pretty

Le mode par défaut, avec un affichage clair et structuré.

Commandes supplémentaires

.help : Affiche la liste des commandes disponibles.

.show : Affiche les configurations actuelles, comme le mode et l’état de la base de données.

.exit ou Ctrl+C : Permet de quitter le mode interactif.

Quelques requêtes SQL impliquant une ou plusieurs tables :

Ici, l’objectif est de vous montrer la simplicité et la puissance d’Osquery. Les commandes que je vais vous présenter vont peut-être paraître rudimentaires, voire même simples pour prétendre surveiller ou auditer un système complexe.

Les requêtes présentées ici ont uniquement pour but de vous montrer la puissance d’Osquery. Pour les requêtes utiles, je vous laisse construire les vôtres à votre guise ou suivre une de nos nombreuses formations concernant les tricks à avoir pour les OPS3

Requête avec jointure :

Ces exemples ne sont que des démonstrations de ce que vous pouvez faire avec Osquery. Pour des requêtes plus avancées et adaptées à vos besoins, je vous encourage à expérimenter par vous-même ou à suivre l’une de nos formations spécialisées, où nous couvrons en profondeur des astuces et bonnes pratiques pour les ops3.

Cette requête SQL pour Osquery est conçue pour récupérer les processus exécutés par un utilisateur spécifique sur un système, dans notre cas, l’utilisateur nommé « vagrant » et en changeant le nom des colonnes, en voici le résultat (en mode pretty) :

Ainsi, vous remarquerez que nous avons utilisé la table users et la table processes, puis créé une correspondance entre elles pour réaliser la jointure. Il est important de noter que vous pouvez utiliser autant de jointures que nécessaire pour complexifier vos requêtes et extraire des informations plus détaillées.

Utilisation d’une fonction mise à disposition par SQL :

Vous avez vu que les jointures sont possibles avec Osquery, mais ce n’est pas tout. Il est également possible d’utiliser des fonctions SQL intégrées.

Dans cet exemple, nous allons reprendre la requête précédente, mais en y ajoutant la fonction strftime(), qui permet de convertir le temps de démarrage des processus (processes.start_time) en un format lisible de type YYYY-MM-DD HH:MM:SS. La colonne résultante sera renommée heure_de_demarrage.

Une fois exécutée, vous pourrez constater ce résultat-ci :

Pour découvrir les fonctions SQL prises en charge par Osquery, je vous invite à consulter la documentation à ce sujet (4). Vous y trouverez toutes les fonctions disponibles, telles que des fonctions mathématiques, de hachage, ainsi que des fonctions liées au réseau.

De plus, vous pouvez filtrer les résultats en utilisant toutes les commandes SQL standards, comme GROUP BY pour grouper les résultats, COUNT(*), et bien d’autres.

Pour conclure avec le mode interactif :

Comme vous pouvez l’imaginer, Il est aussi possible d’exécuter vos requêtes directement à partir du shell et d’utiliser les différents flags de $osqueryi –help pour ajouter des options ou même lui demander de nous faire des sorties en JSON ou en CSV, comme ceci :

Faire nos scripts

Comme vous l’avez vu, avec le mode interactif d’Osqueryi ou via la commande shell, vous pouvez facilement créer vos propres scripts bash, comme celui-ci :

Ici, vous avez toute liberté pour créer vos scripts comme bon vous semble, je vous laisse explorer à votre rythme ! Cependant, attendez d’avoir découvert le mode Daemon avant de vous lancer à fond dans vos scripts, car il pourrait bien vous simplifier la vie.

Le mode Daemon

Comme mentionné plus haut, le mode interactif (osqueryi) permet de prendre en main l’outil, mais aussi de tester et de déboguer les requêtes dans un environnement convivial.

Il permet également d’exécuter des requêtes répétées dans des scripts shell, avec des sorties en JSON ou CSV. Là, toutes les possibilités s’offrent à vous. Mais avant de repartir sur des scriptings exécutant plein de commandes $osqueryi à la chaîne, avec des séries de pipelines (|) en utilisant jq pour analyser le JSON et obtenir les résultats souhaités. Et peut-être ajouter le script dans un scheduler (cron, etc.) pour le lancer et alimenter vos fichiers etc …

Un peu de théorie :

Le mode Daemon d’Osquery permet d’exploiter pleinement la puissance de l’outil en fonctionnant en arrière-plan. Avant d’aborder son utilisation, il est important de comprendre l’architecture interne d’Osquery que voici de façon distillée :

À gauche, on retrouve le Daemon, et à droite, le mode interactif, qui offrent tous deux des fonctionnalités distinctes pour une gestion optimale de l’outil.

Comme vous pouvez le constater, ce schéma très simplifié montre que tant $osqueryi que $osqueryd possèdent chacun leur propre moteur. Curieusement, osqueryi n’est pas un client qui pourrait appeler $osqueryd, comme on pourrait le supposer, mais bien une instance presque complète d’Osquery. Lorsqu’il est exécuté en mode interactif, il crée une socket située dans le répertoire $HOME/.osquery/shell.em.

Cependant, plusieurs différences existent, et ici, nous allons nous concentrer sur les avantages d’utiliser le daemon $osqueryd.

Le mode interactif ne fait rien persister par défaut, sauf si la sortie est redirigée lorsqu’il est utilisé depuis le bash, ou si elle est configurée en amont (voir la commande .show, qui permet de passer de la base de données en mémoire virtuelle à la base de données persistante située dans $HOME/.osquery/shell.db). En revanche, le daemon osqueryd permet de faire persister des données dans sa base de données RocksDB, créant ainsi son répertoire de base de données par défaut dans le répertoire /var/osquery/osquery.db.

Osquery en tant que service permet de l’utiliser comme un outil de surveillance de votre système, en configurant dans un fichier de configuration les requêtes et leurs intervalles. Ces requêtes seront automatiquement enregistrées dans un fichier log.

Osquery utilise Apache Thrift pour permettre la communication entre différentes parties de l’application via des sockets (*.em dans notre situation).

Thrift facilite les appels de procédure distante (RPC) en sérialisant et désérialisant les données, assurant ainsi une communication rapide et efficace entre les composants de l’architecture Osquery. De plus, Thrift permet l’ajout d’extensions à osquery, offrant ainsi une grande flexibilité et la possibilité d’étendre les fonctionnalités de l’outil selon les besoins spécifiques des utilisateurs.

Comment démarrer le daemon

Avant de démarrer le daemon, il est nécessaire de procéder à quelques configurations préalables, comme la création d’un fichier de configuration osquery.conf au format JSON, situé dans le répertoire /etc/osquery/.

Ce fichier permet, entre autres, de configurer l’objet scheduler, qui vous permet de spécifier les commandes que vous souhaitez planifier, ainsi que les intervalles en secondes, comme ceci :

Voire même paramétrer les différentes configuration tel que par exemple l’emplacement des logs, etc. qui sans cela se trouveront dans /var/log/osquery/osquery.results.log par défaut.

Sachant que vous pouvez aussi utiliser des outils tiers pour la récupération des logs tel que la stack ELK (logstach), grafana, ou autres cela grâce aux extensions5, sinon voir en globalité les différentes configurations (6).

Une fois votre fichier osquery.conf créé et alimenté, vous pouvez maintenant démarrer le daemon $osqueryd.

Par défaut, il est recommandé d’utiliser le service systemd déjà préconfiguré. Sinon, vous devrez utiliser les différents flags et paramètres pour le lancer.

Sur Linux, la plupart des distributions, notamment les distributions de type RHEL, utilisent systemd comme implémentation du processus init ce qui facilite l’implémentation des services par rapport à son prédécesseur historique le SystemVinit utilisant des scripts bash dans init.d.

Si vous installez osquery à partir des packages, le service est déjà implémenté, mais pas activé. Il se trouve dans le répertoire /usr/lib/systemd/system/osqueryd.service

Pour activer le service, le démarrer et vérifier son état, vous devez exécuter les commande suivante :

Cela fait, vous devriez voir le statut en actif :

Sinon, si vous ne voulez pas utiliser de service systemd, vous pouvez exécuter la commande $Osqueryctl start et $Osqueryctl status qui permet aussi à démarrer le daemon et voir son statut.

Créer un script en python

Comme mentionné plus haut dans l’article, osqueryd expose une socket unix, ce qui va nous permettre notamment avec des script python ou golang par exemple, d’ajouter le client osquery et de lui spécifier la socket d’exploiter notre daemon osquery en profondeur avec des langages plus avancés.

Il est conseillé d’utiliser le client officiel d’osquery pour python que vous pourrez installer avec $pip install osquery. Cela fait, nous pouvons créer notre script python qui utilisera le client. Bien-sur il ne faut pas oublier de lui donner le répertoire de la socket osquery.

Pour rappel par défaut, la socket de notre daemon osquery pour Linux et Mac OS sera ici :

Ce script python permet ainsi d’exploiter la socket osquery et dans cet exemple de requêter et de stocker le résultat dans la variable proc.

Créer vos tables Osquery

Une autre grande force d’Osquery réside dans sa capacité à être étendu, notamment pour ajouter des extensions, des logs, et dans notre cas, créer nos propres tables.

Comme vous pouvez l’imaginer, la création de tables dans Osquery ne se fait pas comme en SQL avec une simple commande CREATE TABLE. Il faut un peu plus de travail, qui implique des interactions avec le système. Mais avant de plonger dans les détails, sachez que, par défaut, les tables proposées par Osquery sont développées en C++ (le langage principal utilisé pour Osquery, avec des bibliothèques comme Boost ou Apache Thrift). Cependant, grâce aux SDK officiels, il est également possible de créer des extensions et des tables en Golang et en Python.

Dans cet exemple, nous allons créer une table en Python qui récupère le contenu de notre fichier /etc/fstab sur Linux, un fichier utilisé par init pour monter automatiquement les systèmes de fichiers au démarrage.

Si vous avez déjà suivi le chapitre précédent sur la création de scripts Python pour interroger le daemon Osquery, vous avez déjà installé la librairie osquery. Sinon, veuillez exécuter la commande suivante pour récupérer le SDK Osquery complet.

Je vais d’abord vous montrer l’implémentation, puis je vous expliquerai les grands axes :

Étant donné que nous souhaitons lire le contenu de /etc/fstab, nous allons bien sûr utiliser le module os de Python.

Pour créer une table Osquery en Python, il est nécessaire de définir un objet qui hérite de osquery.TablePlugin et de le décorer avec @osquery.register_plugin . Cela permet d’enregistrer notre classe en tant que plugin dans Osquery.

Ensuite, notre classe doit impérativement contenir trois méthodes :

La méthode name : Elle retourne un nom sous forme de chaîne de caractères pour notre table personnalisée. Ce nom sera utilisé dans les requêtes SQL Osquery pour interroger la table et il doit être unique.
La méthode columns : Elle définit les colonnes de la table. Chaque colonne doit avoir un nom et un type de données associé. Cela permet à Osquery de savoir quelles colonnes existent et quel type de données chaque colonne contient pour exécuter la requête et retourner les résultats correctement.
La méthode generate : Cette méthode est appelée par Osquery lors de l’exécution d’une requête pour récupérer les données de la table. C’est ici que notre code lit le fichier /etc/fstab et place son contenu dans une liste query_data. Cette liste contient des lignes valides du fichier /etc/fstab, sous forme de dictionnaires. Chaque dictionnaire représente une ligne et contient les colonnes définies dans la méthode columns.

Enfin, la commande osquery.start_extension(name= »fstab_extension », version= »0.0.1″) permet de démarrer l’extension Osquery.

Cela fait, il faut rendre notre code python éxecutable avec la commande

$chmod +x fstab_table.py

Cela fait, pour le tester en mode interactif, vous éxecutez :

$Osqueryi –extension /root/fstab_table.py

Ainsi vous pourrez vérifier si votre table existe avec .table puis tester notre table d’extension avec la requête :

select * from fstab_table where mount_point != “none”

Permettant d’afficher que les périphériques avec un mount_point (le swap ne s’affiche pas avec ce filtre).

Voici le résultat :

Le mode interactif nous a permis de tester notre table customisé, maintenant pour la mettre à disposition de façon permanente dans le daemon, il vous faudra stopper le service osqueryd, modifier le fichier /etc/osquery/osquery.flags en ajoutant la ligne suivante :

–extension /root/fstab_table.py

Cela fait, veuillez démarrer le service osqueryd en vérifiant dans le statuts que tout s’est bien passé, puis nous allons tester avec un script python. Ici je triche en utilisant le mode interactif de python afin d’interagir avec le daemon, nous allons requêter le fstab avec select.

* from fstab_table et vous verrez le résultat ci :

Notre table est mise à disposition dans notre daemon, comme dit plus haut. L’avantage d’utiliser le fichier osquery.flags vous permettra de faire persister votre table et maintenant vous pourrez requêter votre table dans le scheduler situé dans osquery.conf.

Pour le faire persister dans le mode interactif vous pouvez utiliser le fichier flags que vous ajouterai en argument de la commande $osqueryi.

Bien sûr, pour que votre table fonctionne sur d’autre machines, il faut que vos machines aient Python ainsi que la librairie osquery vu que c’est un langage interprété, sinon veuillez implémenter votre table en golang ou en c++ afin d’avoir un binaire sachant que l’avantage de développer en golang, il vous donnera un seul binaire contenant à l’intérieur les différentes briques (librairies, GC, etc.).

Pour plus d’informations, veuillez lire la documentation officielle concernant la création de tables et d’extensions (7).

Conclusion :

Vous êtes maintenant arrivé à la fin de cet article qui vous a permis de découvrir les bases d’ Osquery. En soi, Osquery est un outil relativement simple à comprendre et à utiliser. C’est une solution puissante, particulièrement utile dans divers contextes, notamment en sécurité où des entreprises comme FleetDM l’utilisent. Cependant, le vrai défi réside dans la capacité de l’utilisateur à formuler les bonnes requêtes.

Pour partager mon expérience personnelle avec cet outil que j’utilise depuis plusieurs années, je trouve qu’il s’inscrit dans une logique similaire à celle de Git, qui, bien que destiné aux développeurs, est aujourd’hui utilisé dans de nombreux autres domaines. Osquery incarne bien cette idée, où des outils ou des langages conçus pour un usage particulier peuvent être détournés à d’autres fins, selon les besoins.

Osquery peut devenir un véritable allié pour surveiller votre système à l’aide de requêtes SQL. Il trouve sa place dans des scripts, des tâches programmées, voire même des plugins Nagios, comme je l’ai déjà implémenté en Python. J’ai également utilisé Osquery pour créer des inventaires dynamiques avec Ansible, en l’intégrant dans des playbooks afin de collecter des données en temps réel sur les systèmes et de les utiliser pour peupler un inventaire dynamique. Cela permet de gérer des configurations systèmes de manière flexible et d’obtenir une vue d’ensemble des ressources sur l’ensemble de l’infrastructure.

Cependant, bien que cet outil facilite certaines tâches, il peut parfois rendre des opérations simples plus complexes par rapport aux utilitaires classiques du standard POSIX. Il est donc important de ne pas en abuser et de ne pas tomber dans le piège de ceux qui, en apprenant les design patterns, se demandent quel est le « meilleur » pattern pour une simple application Hello World. À vous de juger si cela vous semble absurde !

Dylan SMILA

Consultant Ingénieur DevOps, Système & Infrastructure chez Objectware.

Coordinateur de la Squad Recherche & Innovation chez Objectware

Liens :

Le site officiel d’Osquery: https://osquery.io

La page d’installation pour les différentes OS: https://osquery.io/downloads/official.

Le repo github du projet: https://github.com/osquery/osquery

Le code des différents exemples : https://github.com/objectware/article-osquery