HowTo #4 – Maîtriser les subqueries AQL (FR)

1. Introduction
2. HowTo
3. Conclusion
4. Bibliographie

1. Introduction

Bonjour à tous, nous allons découvrir aujourd’hui un fonctionnement plus avancé des requêtes AQL, j’ai nommé les “subqueries” ou “sous-requêtes”.

Elle permet de faire des actions impossibles à faire avec de simples requêtes mais également de combiner plus requêtes et donc potentiellement plusieurs tâches en une seule fois.

Il existe deux types de sous-requêtes :

1. celles qui permettent de définir les données qui vont être utilisées par la requête principale
2. celles qui permettent de filtrer une propriété en fonction d’une ou plusieurs valeurs issues des résultats d’une autre requête

2. HowTo

D’un point de vue plus technique voici leur structure :

Type 1 :

select /*columns*/
from (/*subquery*/)
where
/*filters*/
/*aggregation*/
/*time filters*/

Type 2 :

select /*columns*/
from events /*or flows*/
where
myproperty in (/*subquery*/)
/*other filters*/
/*aggregation*/
/*time filters*/

Dans les deux cas la structure de la sous-requête est identique à la structure d’une requête AQL normale à savoir :

select /*columns*/
from events /*or flows*/
where
/*filters*/
/*aggregation*/
/*time filters*/

Maintenant que nous avons vu les bases, un peu de pratique avec des exemples concrets et surtout des cas ou ce type de requêtes est nécessaire.

Cas n°1 :

Je souhaite avoir le nom d’utilisateur qui apparaissent le plus parmi l’ensemble de mes log sources. Je ne veux pas les noms d’utilisateur qui ne compte pas au moins 100 évènements sur la période définie.

Pour ce cas, la requête aurait été simple s’il n’y avait pas eu la deuxième partie, en effet nous aurions eu :

select logsourcename(logsourceid) as 'LogSource', username as 'Username', sum(eventcount) as 'TotalEvents', count(*) as 'CoalescedEvents'
from events
where
logsourceid > 69 /*exclusion of internal log sources*/
and Username <> null /*exclusion of logs without username*/
group by Username, LogSource
order by TotalEvents desc
last 1 days

Désormais si nous souhaitons appliquer un filtre sur le nombre total d’évènements nous pouvons penser que faire la requête suivante sera suffisant :

select logsourcename(logsourceid) as 'LogSource', username as 'Username', sum(eventcount) as 'TotalEvents', count(*) as 'CoalescedEvents'
from events
where
logsourceid > 69 /*exclusion of internal log sources*/
and Username <> null /*exclusion of logs without username*/
and TotalEvents > 100
group by Username, LogSource
order by TotalEvents desc
last 1 days

Malheureusement ce n’est pas possible et vous obtiendrez l’erreur suivante :

En effet, la propriété sur laquelle nous essayons de filtrer est calculée par agrégation et il n’est pas possible de faire du filtrage sur ce type de propriété. Nous allons donc utiliser les sous-requêtes de type 1 pour transformer cette propriété agrégée en propriété normale, et donc filtrable.

On voit dans la requête que la sous-requête (en bleu) correspond à la requête sans le filtrage sur le nombre d’évènements et qu’ensuite dans la requête principale le filtrage est fait et possible sur la colonne ‘TotalEvents’ qui est considérée comme calculée et figée.

Remarque : Il faut bien penser à mettre le filtrage temporaire en “doublon” puisque dans la requête principale, si vous ne mettez pas le filtrage, par défaut QRadar va mettre comme filtrage “last 5 minutes”, ce qui peut conduire à des résultats nuls ou non souhaités.

Cas n°2 :

Un utilisateur m’a remonté qu’il s’était fait pirater son compte et je souhaite investiguer tout les actions de déplacement latéral que l’attaquant aurait pu effectuer

Pour ce cas, nous souhaitons effectuer un filtrage sur l’IP source mais nous ne savons pas exactement avec quelle(s) IP(s) nous souhaitons la comparer. Par contre d’un autre côté nous savons que le point de pivot est le nom d’utilisateur, nous pouvons donc déjà construire une premier requête qui va permettre de mettre en exergue l’ensemble des IPs sources qui sont impliquées.

select sourceip
from events
where
username = 'root'
group by sourceip
last 1 days

Maintenant, je créé ma requête principal autour de cette sous-requête comme ci-dessous.

Cela va permettre de récupérer les types évènements qui sont le plus apparus avec une IP source correspondant à notre filtrage sur le nom d’utilisateur.

3. Conclusion

Nous voici à la conclusion de cet article qui, je l’espère, vous aura permis de découvrir l’étendu des possibilités avec les subqueries dans QRadar. Nous n’avons qu’effleuré les capacités de cet outil, nous verrons dans de prochains articles d’autres usages qui vous permettrons de comprendre encore mieux tout ce qu’il est possible de faire.

En attendant, je vous laisse découvrir par vous même tous les cas d’usages qui peuvent être résolus. N’hésitez pas à les partager ou à poser vos questions si vous rencontrer des difficultés.

4. Bibliographie

• Documentation IBM : https://www.ibm.com/docs/en/qsip/7.4?topic=language-aql-subquery