Authentication Queries

Overview

Queries for monitoring authentication events across log sources.

Failed Logins by Source

SELECT
    sourceip AS "Source",
    username AS "Username",
    COUNT(*) AS "Failures"
FROM events
WHERE CATEGORYNAME(category) ILIKE '%Authentication%'
  AND QIDNAME(qid) ILIKE '%fail%'
GROUP BY sourceip, username
HAVING COUNT(*) > 10
ORDER BY "Failures" DESC
LIMIT 50
LAST 24 HOURS

Windows Failed Logins (Event 4625)

SELECT
    DATEFORMAT(starttime, 'yyyy-MM-dd HH:mm') AS "Time",
    sourceip AS "Source",
    username AS "Username",
    COUNT(*) AS "Failures"
FROM events
WHERE LOGSOURCETYPENAME(devicetype) ILIKE '%Microsoft Windows%'
  AND "EventID" = '{win-logon-fail}'
GROUP BY starttime, sourceip, username
HAVING COUNT(*) > 5
ORDER BY "Failures" DESC
LIMIT 50
LAST 1 DAYS

Related

Threat Detection