Hi,
Note:
- Using ELK 7.9.2 in Debian 10
- MS SQL Server 2016 SP2 Standard in Windows Server 2012
I am trying to monitor my MS SQL Server with Elasticsearch and visualize the data in Kibana. After some research I found and tried Metricbeat, enabling the mssql
module. The pre-loaded dashboards looks great. The issue with this is that only offers metrics/rates data, but I am looking for a more detailed monitoring like queries, users, time, logs, etc. I need to be able to check the transaction logs containing INSERT, UPDATE, DELETE, DROP
rather than just the rate.
By enabling the mssql
module I will get just rates. Then I found out that I can query the MS SQL server using the sql
module. Enabled the module and after spending some time struggling with the config parameters, I was able to resolve the errors preventing Metricbeat process from start, but I am still unable to query my DB. Instead it looks like Metricbeat will try to query always the same DB mssqlsystemresource
, ignoring the DB I am trying to set in the config file.
Below is the sql
module config file. I uploaded a sample DB and for testing, this is just a very simple query to test connection and DB access
sql.yml
- module: sql
metricsets:
- query
period: 10s
#hosts: ["user=myuser password=mypassword dbname=mydb sslmode=disable"]
hosts: [sqlserver://localhost, user=server_name\User1', password='SECRET', dbname='BikeStores', sslmode='disable']
driver: "mssql"
sql_query: "SELECT COUNT(*) FROM fn_dblog(null,null)"
sql_response_format: table
What could I be doing wrong?
Please help!
Thank you