Metricbeat for SQL Server

tgspacelabs · June 11, 2018, 9:27pm

I am trying to create perfmon counters for SQL Server in metricbeat. I added the following to my metricbeat.yml file but the service will not start properly. The service starts fine when the SQL Server perfmon counter is commented out.

I can't find the error messages as to why it failed. How do I find and correct the problem?

- instance_label: "sqlserver.statistics.batchrequests.name"
  measurement_label: "sqlserver.statistics.batchrequests.sec"
  query: '\SQLServer:SQL Statistics\Batch Requests/sec'
  format: "long"

kvch · June 12, 2018, 8:07am

For debug purposes you could start Metricbeat in the foreground with debug logs enabled. You can do it by running: ./metricbeat -e -d "*".

tgspacelabs · June 12, 2018, 2:46pm

OK, now I see the error messages but I still can't figure out the correct format for the SQL Server perfmon counter. I can use the following format in PowerShell but it doen't work with metricbeat.

Get-Counter -Counter '\*:SQL Statistics\Batch Requests/sec'

Any working ideas would be greatly appreciated.

kvch · June 12, 2018, 2:47pm

What's the error you are seeing?

tgspacelabs · June 12, 2018, 2:48pm

PS C:\Program Files\metricbeat> ./metricbeat -e -d "*"
2018-06-12T07:48:01.006-0700    INFO    instance/beat.go:468    Home path: [C:\Program Files\metricbeat] Config path: [C:\Program Files\metricbeat] Data path: [C:\Program Files\metricbeat\data] Logs path: [C:\Program Files\metricbeat\logs]
2018-06-12T07:48:01.006-0700    DEBUG   [beat]  instance/beat.go:495    Beat metadata path: C:\Program Files\metricbeat\data\meta.json
2018-06-12T07:48:01.007-0700    INFO    instance/beat.go:475    Beat UUID: 4969762b-680a-4b26-8c28-9a45c42800d8
2018-06-12T07:48:01.007-0700    INFO    instance/beat.go:213    Setup Beat: metricbeat; Version: 6.2.4
2018-06-12T07:48:01.007-0700    DEBUG   [beat]  instance/beat.go:230    Initializing output plugins
2018-06-12T07:48:01.007-0700    DEBUG   [processors]    processors/processor.go:49      Processors:
2018-06-12T07:48:01.007-0700    INFO    pipeline/module.go:76   Beat name: PER730XD
2018-06-12T07:48:01.008-0700    DEBUG   [modules]       beater/metricbeat.go:80 Register [ModuleFactory:[docker, mongodb, mysql, postgresql, system, uwsgi, windows], MetricSetFactory:[aerospike/namespace, apache/status, ceph/cluster_disk, ceph/cluster_hea
lth, ceph/cluster_status, ceph/monitor_health, ceph/osd_df, ceph/osd_tree, ceph/pool_disk, couchbase/bucket, couchbase/cluster, couchbase/node, docker/container, docker/cpu, docker/diskio, docker/healthcheck, docker/image, docker/info, docker/memory, dock
er/network, dropwizard/collector, elasticsearch/node, elasticsearch/node_stats, etcd/leader, etcd/self, etcd/store, golang/expvar, golang/heap, graphite/server, haproxy/info, haproxy/stat, http/json, http/server, jolokia/jmx, kafka/consumergroup, kafka/pa
rtition, kibana/status, kubernetes/container, kubernetes/event, kubernetes/node, kubernetes/pod, kubernetes/state_container, kubernetes/state_deployment, kubernetes/state_node, kubernetes/state_pod, kubernetes/state_replicaset, kubernetes/system, kubernet
es/volume, logstash/node, logstash/node_stats, memcached/stats, mongodb/collstats, mongodb/dbstats, mongodb/status, mysql/status, nginx/stubstatus, php_fpm/pool, postgresql/activity, postgresql/bgwriter, postgresql/database, prometheus/collector, promethe
us/stats, rabbitmq/node, rabbitmq/queue, redis/info, redis/keyspace, system/core, system/cpu, system/diskio, system/filesystem, system/fsstat, system/memory, system/network, system/process, system/process_summary, system/raid, system/uptime, uwsgi/status,
 vsphere/datastore, vsphere/host, vsphere/virtualmachine, windows/perfmon, windows/service, zookeeper/mntr]]
2018-06-12T07:48:01.008-0700    DEBUG   [processors]    processors/processor.go:49      Processors:
2018-06-12T07:48:01.010-0700    INFO    helper/privileges_windows.go:62 Metricbeat process and system info: {"OSVersion":{"Major":6,"Minor":2,"Build":9200},"Arch":"amd64","NumCPU":32,"User":{"SID":"S-1-5-21-787380144-986785343-375376054-58496","Account":"
greentx","Domain":"NA","Type":1},"ProcessPrivs":{"SeBackupPrivilege":{"enabled":false},"SeChangeNotifyPrivilege":{"enabled_by_default":true,"enabled":true},"SeCreateGlobalPrivilege":{"enabled_by_default":true,"enabled":true},"SeCreatePagefilePrivilege":{"
enabled":false},"SeCreateSymbolicLinkPrivilege":{"enabled":false},"SeDebugPrivilege":{"enabled":true},"SeImpersonatePrivilege":{"enabled_by_default":true,"enabled":true},"SeIncreaseBasePriorityPrivilege":{"enabled":false},"SeIncreaseQuotaPrivilege":{"enab
led":false},"SeIncreaseWorkingSetPrivilege":{"enabled":false},"SeLoadDriverPrivilege":{"enabled":false},"SeManageVolumePrivilege":{"enabled":false},"SeProfileSingleProcessPrivilege":{"enabled":false},"SeRemoteShutdownPrivilege":{"enabled":false},"SeRestor
ePrivilege":{"enabled":false},"SeSecurityPrivilege":{"enabled":false},"SeShutdownPrivilege":{"enabled":false},"SeSystemEnvironmentPrivilege":{"enabled":false},"SeSystemProfilePrivilege":{"enabled":false},"SeSystemtimePrivilege":{"enabled":false},"SeTakeOw
nershipPrivilege":{"enabled":false},"SeTimeZonePrivilege":{"enabled":false},"SeUndockPrivilege":{"enabled":false}}}
2018-06-12T07:48:01.011-0700    INFO    helper/privileges_windows.go:70 SeDebugPrivilege is enabled. SeDebugPrivilege=(Enabled)
2018-06-12T07:48:01.011-0700    WARN    [cfgwarn]       service/service.go:32   BETA: The windows service metricset is beta
2018-06-12T07:48:01.011-0700    DEBUG   [processors]    processors/processor.go:49      Processors:
2018-06-12T07:48:01.011-0700    WARN    [cfgwarn]       perfmon/perfmon.go:37   BETA: The perfmon metricset is beta
2018-06-12T07:48:01.229-0700    ERROR   instance/beat.go:667    Exiting: 1 error: 1 error: initialization failed: failed to add counter (path="\*:sql statistics\batch requests/sec"): The specified counter could not be found.
Exiting: 1 error: 1 error: initialization failed: failed to add counter (path="\*:sql statistics\batch requests/sec"): The specified counter could not be found.
PS C:\Program Files\metricbeat>

tgspacelabs · June 12, 2018, 10:11pm

I did finally manage to get a query string which worked. However, it seems odd that I have to specify the machine and SQL Server instance name in order to make it work.

#SQL Statistics\Batch Requests/sec
- instance_label: "sqlserver.statistics.batchrequests.name"
  instance_name: "Batch Requests per Second"
  measurement_label: "sqlserver.statistics.batchrequests.sec"
  **query: '\\machinename\\MSSQL$SS17:SQL Statistics\Batch Requests/sec'**
  format: "float"

It would be much nicer if it worked like the PowerShell:
Get-Counter -Counter '\*:SQL Statistics\Batch Requests/sec'

tgspacelabs · June 13, 2018, 2:42pm

After hours of trying different query strings I did manage to get a slightly smaller version which still requires the SQL Server instance name:

#SQL Statistics\Batch Requests/sec
- instance_label: "sqlserver.statistics.batchrequests.name"
  instance_name: "Batch Requests per Second"
  measurement_label: "sqlserver.statistics.batchrequests.sec"
  query: '\MSSQL$SS17:SQL Statistics\Batch Requests/sec'
  format: "float"

Does anyone have any other ideas?

system · July 11, 2018, 2:42pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.