Tutoriel QRadar AQL – Advanced Search 101

Bonjour à tous, aujourd’hui j’ai eu l’autorisation de vous repartager du boulot que j’ai réalisé en 2020 à mon taf. Du coup, on va se faire un Tutoriel QRadar AQL, logiciel d’IBM et son langage de recherche interne : cool non ? Je sais qu’ici je vous ai plutôt abonné chez la concurrence jusqu’ici mais ca ne fait jamais de mal de regarder l’herbe ailleurs, hein.

Et je l’indique ce post c’est du « vrai taf » ça, du coup je profite de cette introduction pour remercier mon équipe à EDF qui m’autorise à republier ce travail… On lâche un pouce bleu pour EDF c’est pas systématique les boites sympas qui autorisent ça 🙂 ! The SOC needs you, tout ça tout ça, hein… :-).

Bref c’est partie pour ce tutoriel QRadar AQL…

AQL : pour Acceptable Query Limit ?     

L’AQL, pour Ariel Query Langage, est le langage de requête dans QRadar. La syntaxe ressemble beaucoup à du SQL, et ce qui est demandé dans le clickodrome via l’interface web de QRadar qui construit en fait des requêtes AQL. Chez la concurrence (qui a dit Splunk ?), ça s’appelle le SPL pour Search Processing Language.

Il faut garder en tête que si le langage AQL ressemble fortement à du SQL, les données derrières ne sont pas des bases relationnelles traditionnelles, mais bien du « Big Data ». L’AQL n’offre donc pas exactement les mêmes capacités qu’un SQL « classique ».

Mais AQL a un gros avantage : c’est que si vous savez faire une requête en SQL, vous ne devriez pas être trop perdu (Et si vous avez fait beaucoup de Splunk avant : pleurez un coup, ça va passer… 😉 ) en AQL.

Quelques exemples de requêtes simples en AQL

Pour mieux comprendre à quoi ressemble une requête AQL, voici quelques exemples faciles. Pour les tester il vous suffit de sélectionner le mode Advanced Search de l’onglet Log Activity dans QRadar:

# tous les évènements relatifs à l’IP 51.15.9.16 sur la semaine 52 de 2019.

SELECT *
FROM events
WHERE destinationip = '51.15.9.16'
START '2019-12-23 8:00:00' STOP '2019-12-27 17:00:00'

# même recherche en excluant l’utilisateur jdupond la semaine d’avant :

SELECT *
FROM events
WHERE destinationip = '51.15.9.16'
AND username!= 'jdupond'
START '2019-12-16 8:00:00' STOP '2019-12-20 17:00:00'

Un petit rappel avant d’attaquer le dur la Documentation AQL officielle (Version PDF).

To ‘quote’ or not « two quote » ?

On va mettre de suite au clair un truc pas du tout, du tout, intuitif dans QRadar : «  » » et « ‘ » c’est pas du tout pareil en AQL (qui a pensé dit « comme en PowerShell ? » :@ ).

• « username » fait référence à la valeur du champ username (i.e. la colonne, dans l’interface).
• Alors que ‘username’ fait référence à la chaine de caractère ‘username’

Comme en PowerShell en fait, du coup si j’écris :

• WHERE "username"='jdupont': je teste que si l’utilisateur dans mon event est jdupont.
• WHERE 'username'='jdupont' : je teste que si le string  ‘username’ est égal à ‘A01234’ (ce qui est tout le temps faux d’après captain obvious)

Voilà, maintenant que ça s’est fait, on peut commencer.

Construire une requête AQL :

On l’a déjà dit, la requête AQL se construit de la même manière qu’une requête SQL, soit en gros :

[SELECT *, column_name, column_name]
[FROM table_name]
[WHERE search clauses]
[GROUP BY column_reference*]
[HAVING clause]
[ORDER BY column_reference*]
[LIMIT numeric_value]
[TIMEFRAME]

Et voici le flux d’analyse qui sera appliqué dans QRadar ensuite, sachant que tous les mots clés sont optionnels à partir de where.

La Clause WHERE et quelques opérateurs

La clause WHERE s’utilise comme en SQL, voici quelques filtres utiles (doc) :

N’oubliez pas de combiner tout ça avec les opérateurs AND et OR.

Time-selection

Voici quelques exemples permettant de fixer la période de recherche à la fin de la requête :

• START '2017 01 01 9:00:00'
• STOP '2017 01 01 10:20:00'
• LAST 24 HOURS

Limit

Pour limiter le nombre de résultats au 500 premiers résultats et arrêter la requête :

• limit 500

Le mot clé limit est très utile quand vous travaillez à la construction sur une « grosse requête » car elle permet d’éviter de tuer votre infra pendant que vous développez la requête. En limitant la recherche au « n » premiers résultats on évite de demander à l’infra une recherche dans de trop gros volume de donnée.

Attention néanmoins, si un « LIMIT » est combinée avec un « GROUP BY », ce dernier prendra la priorité, et la limite sera alors appliquée sur le résultat groupé. En conséquence, l’optimisation ne se fera pas comme prévu.

L’autre option consiste donc à réduire fortement la fenêtre de temps sur laquelle vous appliquez la recherche.

FROM ? Osef

Pour le FROM, vous avez 2 options derrière le from : events ou flows dans le cadre de cette série d’articles on ne s’intéressera qu’aux event donc vous ne vous posez pas trop de questions :-).

GROUP BY, les stats

Comme en SQL le group by vous permet de regrouper vos données en utilisant des fonctions d’agrégation, qui sont, dans 90% des cas, des stats. Comme un bon exemple vaut souvent mieux qu’un long discours : la liste des utilisateurs qui accède le plus à l’IP 51.15.9.16.

SELECT username,
COUNT(username) AS nb_acces
FROM events
WHERE destinationip = '51.15.9.16'
GROUP BY username
ORDER BY nb_acces DESC
LAST 24 HOURS

Voici les principales fonctions utilisables avec un GROUP BY :

• AVG
• COUNT
• MIN
• MAX
• SUM
• UNIQUECOUNT

HAVING

Quand vous avez besoin d’un second niveau de filtre, notamment sur des stats générées par un GROUP BY, il faut utiliser HAVING de notre exemple ci-dessus :

 SELECT username, 
 COUNT(username) AS nb_acces 
 FROM events 
 WHERE destinationip = '51.15.9.16' 
 GROUP BY username 
 HAVING nb_acces>10 
 ORDER BY nb_acces DESC 
 LAST 24 HOURS

Travailler les résultats

Les ID internes

Pour ceux qui sont encore là, et qui ont fait les tests, vous aurez vu dans vos tests que les recherches SELECT * retournent un ensemble de colonnes prédéfinies qui n’inclues pas la provenance du log… Et que ce n’est pas super pratique pour savoir de quoi on parle :

Heureusement, il existe un tas de fonction de conversion des ID internes en valeur lisible, comme LOGSOURCENAME qui permet de convertir le logsourceid en sa valeur textuelle. Voici un exemple de recherche utilisant ces fonctions pour obtenir un résultat un peu plus « human readable ».

SELECT starttime,sourceip,LOGSOURCENAME(logsourceid),eventcount,destinationip,destinationport,CATEGORYNAME(category),username 
FROM events  
WHERE destinationip = '51.15.9.16'  
AND username!= chadock'  
LAST 24 HOURS

Les SubQuery

Il nous reste un dernier gros problème, à ce stade nous sommes capable d’extraire des résultats de des logs mais pas nécessairement de rechercher une seconde fois dans QRadar sur ces résultats ensuite.

C’est là que les subquery interviennent, par exemple pour compter les utilisateurs uniques du site depuis la liste des utilisateurs.

Select UNIQUECOUNT(username_l) 
FROM (SELECT DATEFORMAT(starttime,'yyyy-MM-dd hh:mm:ss'), sourceip,LOGSOURCENAME(logsourceid),eventcount,destinationip,destinationport,category,LOWER(username) as username_l
FROM events
WHERE destinationip = '51.15.9.16'
AND username!= 'milou' 
LAST 24 HOURS)

Du coup à partir de là, il vous suffit d’encapsuler les requêtes jusqu’à obtenir les résultats que vous voulez.

Conclusion de ce 1er tutoriel QRadar AQL

C’est tout pour ce 1^er TP, il reste encore quelques trucs à voir pour être pleinement opérationnel. Par exemple, les « reference data » et la performance des requêtes qui seront abordés dans les prochains TP qui compléteront ce 1er tutoriel QRadar AQL.