Split an LDAP Distinguished Name into organizational units
SPLIT on the comma, then MV_SLICE extracts the main OU from a DN, aggregating accounts by organizational branch without a dedicated field in the directory.
Prerequisites
Elasticsearch 8.14+, logs d'annuaire LDAP
SQL
FROM "logs-ldap-*"
| WHERE user.dn IS NOT NULL
| EVAL parties = SPLIT(user.dn, ",")
| EVAL ou_principale = MV_SLICE(
SPLIT(MV_SLICE(parties, 1), "="), 1)
| STATS
connexions = COUNT(*),
comptes = COUNT_DISTINCT(user.name),
echecs = COUNT(*) WHERE event.outcome == "failure"
BY ou_principale
| WHERE connexions > 50
| SORT connexions DESC
| LIMIT 20Result
ou_principale | connexions | comptes | echecs --------------+------------+---------+------- Finance | 48211 | 412 | 188 Engineering | 31877 | 887 | 84 Sales | 18402 | 310 | 412 IT-Admins | 8204 | 48 | 12 Externes | 2204 | 187 | 611
SPLITMV_SLICELDAPParsing