Top 3 CVSS scores per host with MV_SORT and MV_SLICE
MV_SORT orders the multi-value field in descending order, MV_SLICE keeps its top three values, and MV_FIRST extracts the worst — all without exploding the rows.
Prerequisites
Elasticsearch 8.12+, scans de vulnérabilités indexés
SQL
FROM "logs-scans-vuln-*"
| WHERE vulnerability.score IS NOT NULL
| EVAL scores_tries = MV_SORT(vulnerability.score, "DESC")
| EVAL nb_vulns = MV_COUNT(scores_tries),
score_pire = MV_FIRST(scores_tries),
top3_scores = MV_SLICE(scores_tries, 0, 2)
| WHERE score_pire >= 9.0
| KEEP host.name, nb_vulns, score_pire, top3_scores
| SORT score_pire DESC
| LIMIT 30Result
host.name | nb_vulns | score_pire | top3_scores -------------+----------+------------+----------------- srv-web-01 | 42 | 9.8 | [9.8, 9.6, 9.1] srv-legacy-7 | 118 | 9.8 | [9.8, 9.8, 9.4] gw-vpn-02 | 16 | 9.6 | [9.6, 8.9, 8.2] db-archive-1 | 54 | 9.1 | [9.1, 8.8, 8.8]
MV_SORTMV_SLICEVulnérabilitésMulti-valeur