Update
I spent some time researching and trying different combinations. At the end it looks like the sql query is finally being executed without errors, as I don't see any in Metricbeat logs. The issue now is that not all the fields contained in the query are included in the index, when I check from Discovery. I was expecting at least a - when a field does not have a value to show.
sql.yml
- module: sql
metricsets:
- query
period: 15s
#hosts: ["user=myuser password=mypassword dbname=mydb sslmode=disable"]
hosts: ["sqlserver://sa:123456aA@localhost"]
dbname: "BikeStores"
sslmode: "disable"
driver: "mssql"
sql_query: "
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Transaction Name],
[CONTEXT],
[AllocUnitName],
[Page ID],
[Slot ID],
[Begin Time],
[End Time],
[Number of Locks],
[Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN
('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')"
sql_response_format: table
Discovery (Kibana)
Original query in MSSQL server
