# Logstash - MSSQL Events and alerts

**URL:** <https://discuss.elastic.co/t/logstash-mssql-events-and-alerts/288931>\
**Category:** Logstash\
**Tags:** ingest-pipeline\
**Created:** [November 10, 2021, 9:59pm UTC](https://discuss.elastic.co/t/logstash-mssql-events-and-alerts/288931 "2021-11-10T21:59:55Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Trinity\_Rolling](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/trinity_rolling/32/96443_2.png) [@Trinity\_Rolling](https://discuss.elastic.co/u/Trinity_Rolling)\
**Post date:** [November 10, 2021, 9:59pm UTC](https://discuss.elastic.co/t/logstash-mssql-events-and-alerts/288931/1 "2021-11-10T21:59:55Z")

</div>

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:

```auto
15:34:21.063
mssql.log
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: 192.1.6.101]

```

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

---

<div class="post-metadata">

**Author:** ![Trinity\_Rolling](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/trinity_rolling/32/96443_2.png) [@Trinity\_Rolling](https://discuss.elastic.co/u/Trinity_Rolling)\
**Post date:** [November 10, 2021, 10:33pm UTC](https://discuss.elastic.co/t/logstash-mssql-events-and-alerts/288931/2 "2021-11-10T22:33:39Z")

</div>

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.

```auto
# ---------------------------- Elasticsearch Output ----------------------------
output.elasticsearch:
  # # # 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 -------------------------------
#output.logstash:
  # 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.

```auto
WHEN THE count OF LOG ENTRIES
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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 8, 2021, 10:33pm UTC](https://discuss.elastic.co/t/logstash-mssql-events-and-alerts/288931/3 "2021-12-08T22:33:49Z")

</div>

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