- 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
- 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?