Logstash - MSSQL Events and alerts

This is probably a basic question, but I cannot find a good example of where to start. I am currently ingesting MSSQL's ERRORLOG using Filebeat -> LogStash -> Elasticsearch. In Elasticsearch, I would like to set an alert on certain errors in the Error Log like failed logins as an example. How would I go about something like this? Below is a common log file entry that would be coming into Elasticsearch from Logstash:

2021-11-10 15:34:12.67 Logon       Error: 18456, Severity: 14, State: 8.
2021-11-10 15:34:12.67 Logon       Login failed for user 'sqluser'. Reason: Password did not match that for the login provided. [CLIENT:]

I'm hoping this knowledge can be used for other logs and alerting.

I missed a key step when I setup the pipeline for this module. I didn't. To resolve, I stopped the filebeat service on the SQL host. Modified the filebeat.yml file. Commented out the Logstash configuration. Uncommented and filled in the Elasticearch information.

# ---------------------------- Elasticsearch Output ----------------------------
  # # # Array of hosts to connect to.
  hosts: ["https://elasticstack.mydomain.com:9200"]

  # # # Protocol - either `http` (default) or `https`.
  protocol: "https"

  # # # Authentication credentials - either API key or username/password.
  # # #api_key: "id:api_key"
  username: "elastic"
  password: "<fake password>"

# ------------------------------ Logstash Output -------------------------------
  # The Logstash hosts
  #hosts: ["<fake ip>:5044"]

  # Optional SSL. By default is off.
  # List of root certificates for HTTPS server verifications

Opened a PowerShell windows. Navigated to the Filebeat install directory. Executed the following command: ./filebeat --pipelines --modules mssql
This corrected the manual pipeline I had created in Elasticsearch. I then flipped the configuration in my YML file to go back to Logstash from Elasticsearch. Restarted the Filebeat service.
I ran a failed login test to populate a new log entry. I was then able to create an alert based on the message.keyword which was the MS Error code.

WITH event.dataset
IS mssql.log
AND message.keyword
IS Error: 18456, Severity: 14, State: 8.
IS more than or equals 1
FOR THE LAST 5 minutes
GROUP BY host.hostname

Properly creating the pipeline for each module is the key step that I missed during the module setup.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.