metricbeat mssql module: performance counter don't work with named instances

[cwuethrich]

For confirmed bugs, please report:

• Version: 7.11.0 (but all others as well)
• Operating System: all
• Discuss Forum URL: https://discuss.elastic.co/t/some-metricbeat-mssql-performance-metrics-not-returning-values/218493
• Discuss Forum URL: https://discuss.elastic.co/t/metricbeat-mssql-performance-module-does-not-record-data-from-named-instance/240536
• Discuss Forum URL: https://discuss.elastic.co/t/how-to-configure-multiple-sql-instance-in-windows-metricbeat-yml/161978
• Steps to Reproduce: Installation of MS SQL Server with named instances (not possible with docker)

The object_name of a performance counter doesn't start with SQLServer: if there is a named instance. It starts with MSSQL$MYINSTANCENAME.
You can read about this in following link: https://www.sentryone.com/blog/allenwhite/sql-server-performance-counters-to-monitor

So in this case we have to check if there is a named instance. We can find the name of the instance with following statement: SELECT SERVERPROPERTY('InstanceName'). This statement returns null if there is only the default instance -> SQLServer:.
https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver15

[elasticmachine]

Pinging @elastic/integrations (Team:Integrations)

[botelastic]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[cwuethrich]

Can anyone help to fix this? It's sad if we can't monitor metrics of a MS SQL Server with multiple instances.

[cwuethrich]

Is it possible to fix this issue? We would really like to use metricbeat for named mssql instances. I opened a pull request with a simple solution to fix it.