MSSQL metric in windows

I have installed metricbeat MSSQL module in windows, but when I enable MSSQL module metricbeat service auto stop. Metricbeat service worksonly if MSSQL module is disabled.

Please help

Hi!

Do you have any logs to share?

Hi!,
Please see below logs

'2021-01-05T14:29:47.141+0530 INFO instance/beat.go:645 Home path: [C:\Program Files\Elastic\Beats\7.10.1\metricbeat] Config path: [C:\ProgramData\Elastic\Beats\metricbeat] Data path: [C:\ProgramData\Elastic\Beats\metricbeat\data] Logs path: [C:\ProgramData\Elastic\Beats\metricbeat\logs]
2021-01-05T14:29:47.152+0530 INFO instance/beat.go:653 Beat ID: fcc91b37-3e88-4d08-9212-9201fe48c454
2021-01-05T14:29:47.174+0530 INFO [beat] instance/beat.go:981 Beat info {"system_info": {"beat": {"path": {"config": "C:\ProgramData\Elastic\Beats\metricbeat", "data": "C:\ProgramData\Elastic\Beats\metricbeat\data", "home": "C:\Program Files\Elastic\Beats\7.10.1\metricbeat", "logs": "C:\ProgramData\Elastic\Beats\metricbeat\logs"}, "type": "metricbeat", "uuid": "fcc91b37-3e88-4d08-9212-9201fe48c454"}}}
2021-01-05T14:29:47.174+0530 INFO [beat] instance/beat.go:990 Build info {"system_info": {"build": {"commit": "1da173a9e716715a7a54bb3ff4db05b5c24fc8ce", "libbeat": "7.10.1", "time": "2020-12-04T23:38:30.000Z", "version": "7.10.1"}}}
2021-01-05T14:29:47.174+0530 INFO [beat] instance/beat.go:993 Go runtime info {"system_info": {"go": {"os":"windows","arch":"amd64","max_procs":4,"version":"go1.14.12"}}}
2021-01-05T14:29:47.210+0530 INFO [beat] instance/beat.go:997 Host info {"system_info": {"host": {"architecture":"x86_64","boot_time":"2020-10-10T18:05:33+05:30","name":"M0946","ip":["fe80::87f:3a23:aa1d:5583/64","10.51.4.201/24","::1/128","127.0.0.1/8","fe80::5efe:a33:4c9/128"],"kernel_version":"6.1.7601.24520 (win7sp1_ldr_escrow.190828-1732)","mac":["20:25:64:08:d8:29","00:00:00:00:00:00:00:e0"],"os":{"family":"windows","platform":"windows","name":"Windows Server 2008 R2 Enterprise","version":"6.1","major":1,"minor":0,"patch":0,"build":"7601.24525"},"timezone":"IST","timezone_offset_sec":19800,"id":"d6bd2cfe-8e28-4eb2-864c-31ee93c0919e"}}}
2021-01-05T14:29:47.211+0530 INFO [beat] instance/beat.go:1026 Process info {"system_info": {"process": {"cwd": "C:\Windows\system32", "exe": "C:\Program Files\Elastic\Beats\7.10.1\metricbeat\metricbeat.exe", "name": "metricbeat.exe", "pid": 1620, "ppid": 560, "start_time": "2021-01-05T14:29:46.373+0530"}}}
2021-01-05T14:29:47.211+0530 INFO instance/beat.go:299 Setup Beat: metricbeat; Version: 7.10.1
2021-01-05T14:29:47.211+0530 INFO [index-management] idxmgmt/std.go:184 Set output.elasticsearch.index to 'metricbeat-7.10.1' as ILM is enabled.
2021-01-05T14:29:47.211+0530 INFO eslegclient/connection.go:99 elasticsearch url: https://kibana url:9200
2021-01-05T14:29:47.212+0530 INFO [publisher] pipeline/module.go:113 Beat name: MXXX
2021-01-05T14:29:47.326+0530 INFO [monitoring] log/log.go:118 Starting metrics logging every 30s
2021-01-05T14:29:47.326+0530 INFO instance/beat.go:455 metricbeat start running.
2021-01-05T14:29:47.349+0530 INFO helper/privileges_windows.go:79 Metricbeat process and system info: {"OSVersion":{"Major":6,"Minor":1,"Build":7601},"Arch":"amd64","NumCPU":4,"User":{"SID":"S-1-5-18","Account":"SYSTEM","Domain":"NT AUTHORITY","Type":1},"ProcessPrivs":{"SeAssignPrimaryTokenPrivilege":{"enabled":false},"SeAuditPrivilege":{"enabled_by_default":true,"enabled":true},"SeBackupPrivilege":{"enabled":false},"SeChangeNotifyPrivilege":{"enabled_by_default":true,"enabled":true},"SeCreateGlobalPrivilege":{"enabled_by_default":true,"enabled":true},"SeCreatePagefilePrivilege":{"enabled_by_default":true,"enabled":true},"SeCreatePermanentPrivilege":{"enabled_by_default":true,"enabled":true},"SeCreateSymbolicLinkPrivilege":{"enabled_by_default":true,"enabled":true},"SeDebugPrivilege":{"enabled_by_default":true,"enabled":true},"SeImpersonatePrivilege":{"enabled_by_default":true,"enabled":true},"SeIncreaseBasePriorityPrivilege":{"enabled_by_default":true,"enabled":true},"SeIncreaseQuotaPrivilege":{"enabled":false},"SeIncreaseWorkingSetPrivilege":{"enabled_by_default":true,"enabled":true},"SeLoadDriverPrivilege":{"enabled":false},"SeLockMemoryPrivilege":{"enabled_by_default":true,"enabled":true},"SeManageVolumePrivilege":{"enabled":false},"SeProfileSingleProcessPrivilege":{"enabled_by_default":true,"enabled":true},"SeRestorePrivilege":{"enabled":false},"SeSecurityPrivilege":{"enabled":false},"SeShutdownPrivilege":{"enabled":false},"SeSystemEnvironmentPrivilege":{"enabled":false},"SeSystemProfilePrivilege":{"enabled_by_default":true,"enabled":true},"SeSystemtimePrivilege":{"enabled":false},"SeTakeOwnershipPrivilege":{"enabled":false},"SeTcbPrivilege":{"enabled_by_default":true,"enabled":true},"SeTimeZonePrivilege":{"enabled_by_default":true,"enabled":true},"SeUndockPrivilege":{"enabled":false}}}
2021-01-05T14:29:47.349+0530 INFO helper/privileges_windows.go:87 SeDebugPrivilege is enabled. SeDebugPrivilege=(Default, Enabled)
2021-01-05T14:29:49.874+0530 WARN [cfgwarn] application_pool/application_pool.go:43 BETA: The iis application_pool metricset is beta.
2021-01-05T14:29:49.968+0530 INFO [monitoring] log/log.go:153 Total non-zero metrics {"monitoring": {"metrics": {"beat":{"cpu":{"system":{"ticks":2340,"time":{"ms":2340}},"total":{"ticks":3042,"time":{"ms":3042},"value":3042},"user":{"ticks":702,"time":{"ms":702}}},"handles":{"open":392},"info":{"ephemeral_id":"ef6a8a18-6246-4bb8-9847-71361b4f7a34","uptime":{"ms":3487}},"memstats":{"gc_next":15771024,"memory_alloc":10931968,"memory_total":31180888,"rss":69111808},"runtime":{"goroutines":38}},"libbeat":{"config":{"module":{"running":0}},"output":{"type":"elasticsearch"},"pipeline":{"clients":0,"events":{"active":0}}},"system":{"cpu":{"cores":4}}}}}
2021-01-05T14:29:49.968+0530 INFO [monitoring] log/log.go:154 Uptime: 3.4881433s
2021-01-05T14:29:49.968+0530 INFO [monitoring] log/log.go:131 Stopping metrics logging.
2021-01-05T14:29:49.968+0530 INFO instance/beat.go:461 metricbeat stopped.
2021-01-05T14:29:49.990+0530 ERROR instance/beat.go:956 Exiting: 2 errors: could not create connection to db: error doing ping to db: Login error: read tcp [::1]:65459->[::1]:1433: wsarecv: An existing connection was forcibly closed by the remote host.; could not create connection to db: error doing ping to db: Login error: read tcp [::1]:65461->[::1]:1433: wsarecv: An existing connection was forcibly closed by the remote host.'

Hi!

The last log indicates that Metricbeat is not able to connect to DB. Can you check your configuration again and see what could be the reason?

C.

Hi!!!

I am able to connect Database server from other machines through SQL server Management Studio but not through mssql.yml

Below is configuration in mssql.yml

"# Module: mssql

Docs: https://www.elastic.co/guide/en/beats/metricbeat/7.10/metricbeat-module-mssql.html

  • module: mssql
    metricsets:
    • "transaction_log"
    • "performance"
      hosts: ["sqlserver://sqlserveraddress"]
      username: domain\username (mentioned correct user name)
      password: verysecurepassword (mentioned correct password)
      period: 10s
      "

please let me know if this is correct way to define MSSQL DB server parameters in yml file.

hi @rohan0018, are you using Active Directory domain credentials for the login? Else you have to specify native user credentials as part of the host string with the following format:
Ex
hosts: ["sqlserver://sa@localhost"]]

Hi Mariana,

As per your suggestion I have tried below format

"hosts: ["sqlserver://sa:password@sqlserveraddress"]"

but no luck, I got below logs when tried with above format.

"Exiting: 2 errors: host parsing failed for mssql-transaction_log: error parsing URL: parse "sqlserver://sa:"sa user password": invalid port ":"sa user password"" after host; host parsing failed for mssql-performance: error parsing URL: parse "sqlserver://sa:sa user password": invalid port ":"sa user password"" after host"

any help on this issue.