Any IP which succeeds at logging in via SSH after a failed login attempt is worth a look--it could indicate that a brute-force attempt has succeeded. This query builds up a table of successful SSH logins from system logs, then checks all failed attempts to see if that IP has ever successfully logged in. It builds a table of the output.

The Query:

@successful{tag=syslog words Accepted
| syslog Appname==sshd Hostname Message
| regex -e Message "Accepted (?P<method>\S+) for (?P<user>\S+) from (?P<ip>\S+)"
| unique method user ip Hostname
| table user ip Hostname};
tag=syslog words authenticating user

| syslog Appname==sshd Hostname Message
| regex "(Disconnected from|Connection closed by) authenticating user (?P<failed_user>\S+) (?P<failed_ip>\S+)"
| alias Hostname failed_hostname
| lookup -s -r @successful failed_ip ip (Hostname as successful_hostname user as successful_user)
| table failed_ip failed_hostname failed_user successful_hostname successful_user

Visit gravwell.io/query to view an archive of our previous Query of the Week posts.