topic Re: MS SQL Server Perfmon multiple Install instances in Splunk Search

MS SQL Server Perfmon multiple Install instances

bmacias84 — Mon, 08 Jul 2013 18:37:54 GMT

I am trying to create generic MSSQL for data collection. While installing SQL you are able to use the DEFAULT_INSTANCE or create NAMED_INSTANCES. Creating NAMED_INSTANCES allows you to have multiple MSSQL installation running on different ports.

With a DEFAULT_INSTANCE the counters will look like:



[perfmon://SQLServer:GeneralStatistics]

counters = User Connections

disabled = 0

interval = 60

object = SQLServer:General Statistics

index = <some_index>

On a NAMED_INSTANCE the counters will look like:



[perfmon://<NAMED_INSTANCE>:GeneralStatistics]

counters = User Connections

disabled = 0

interval = 60

object = <NAMED_INSTANCE>:General Statistics

index = <some_index>

NAMED_INSTANCE could be foo,bar, MSQL$HELLO, or MSQL$THREEHUNDRED. I would like to wild card all objects SQL objects for General Statistics for example. I do know I can create a WMI query quite easily to complish this but I would prefer using perfmon for constancy. Also trying to avoid creating stanzas for each instance of SQL.

Cheers

Re: MS SQL Server Perfmon multiple Install instances

ahall_splunk — Thu, 10 Jul 2014 14:56:17 GMT

You can do this by wildcards in the object. This is a regular expression, so something like the following in Splunk Universal Forwarder 6.0 and beyond:

[perfmon://GeneralStats]
counters = User Connections
interval = 60
object = [^:]+:General Statistics
index = perfmon
disabled = 0

Re: MS SQL Server Perfmon multiple Install instances

bmacias84 — Mon, 04 Aug 2014 16:55:41 GMT

Thats good to know, but does that work for virtual (shared) node in an active active SQL 2012 env. The perfmon counters for the virtual and physical node are different if you collect through the virtual node vs the physical node.