mantastumenas / zabbix-template-for-microsoft-sql-server Goto Github PK

Hi there,
Thank you for taking the time of this template, Was wondering two things

1. possible to add on the template the top 10 expensive queries? and the Data File I/O
   the picture is from what i see on SSMS on the activity monitor

2. would be it possible to replicate to monitor on ubuntu server running linux mssql 2019?

Thank you

It should be $fullInstanceName.

Error message is:

Error executing query on !

And should be:

Error executing query on <INSTANCENAME>!

Hi,
I was wondering if its possible to change the threshold on the backup its currently to check every 7 days for full
Thank you

In some scripts variable is instName and in some scripts it is instanceName

what is different between with and without SQL instance discovery

Create a template for monitoring the MS SQL security best practices (based on CIS Benchmarks from www.cisecurity.org )

Not working in Zabbix Version 5.4.3. No data caputred. We configured as per the attached PDF file. zabbix agent version is 5.4.4. Kindly suggest the same

Hi @MantasTumenas
Currently i have zabbix 6.2 and the template is working and showing the info, but it seems that its not triggering

the data shows but some odd reason its not triggering in this case should of triggered the diff backup and log backup

Thank you

Discovery is not working if MS SQL is x86 and Win OS is x64

Hi
Currently trying to install on server 2022 with mssql but the data is not coming in
on zabbix it shows unsupported data key
I run the powershell manually and it works with no issue

I placed the scripts in the the zabbix folder

  4976:20220512:060803.271 active check "tier3.instances.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:060803.272 active check "tier3.sysdatabases.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:060803.273 active check "tier3.userdatabases.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:100804.117 active check "tier1.databases.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:100804.117 active check "tier1.instances.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:100804.118 active check "tier2.databases.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:100804.119 active check "tier2.instances.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:100804.119 active check "tier3.instances.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:100804.120 active check "tier3.sysdatabases.mssql.discovery" is not supported: Unsupported item key.
  4976:20220512:100804.120 active check "tier3.userdatabases.mssql.discovery" is not supported: Unsupported item key.

Thank you

Review triggers prototypes values.

Trigger SQL Instance {#SQLINSTANCENAME}: Database Backup Errors does not catch backup error, if the backups are configured with Ola Hallengren scripts.

Hello,

I installed your template on several MSSQL servers. Warnings and erros seems to act fine (there are many problems coming from the agent concerning SQL so the template seems to work fine), but I can see that (some) job errors did not, and it is the main reason I use it.
Where should I look first, knowing that I an in no way a developper, and just started to work with zabbix?
Thank you very much for the work put on that project, and cheers from France.

Edit : looking more sharply, I can see that the triggers are not available. Maybe this server is too old (it is a MSSQL 2005) for the template to work fine. I'll read the documentation more and come back to you once I figured out the possible cause.

Hi,
Would it be possible that when it get blocked to show which query is doing the blocking and the user that is blocking it?

maybe something like this?

WITH [Blocking]
AS (SELECT w.[session_id]
   ,s.[original_login_name]
   ,s.[login_name]
   ,w.[wait_duration_ms]
   ,w.[wait_type]
   ,r.[status]
   ,r.[wait_resource]
   ,w.[resource_description]
   ,s.[program_name]
   ,w.[blocking_session_id]
   ,s.[host_name]
   ,r.[command]
   ,r.[percent_complete]
   ,r.[cpu_time]
   ,r.[total_elapsed_time]
   ,r.[reads]
   ,r.[writes]
   ,r.[logical_reads]
   ,r.[row_count]
   ,q.[text]
   ,q.[dbid]
   ,p.[query_plan]
   ,r.[plan_handle]
 FROM [sys].[dm_os_waiting_tasks] w
 INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
 INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
 CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
 CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
 WHERE w.[session_id] > 50
  AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
      ,'ASYNC_NETWORK_IO'))
SELECT b.[session_id] AS [WaitingSessionID]
      ,b.[blocking_session_id] AS [BlockingSessionID]
      ,b.[login_name] AS [WaitingUserSessionLogin]
      ,s1.[login_name] AS [BlockingUserSessionLogin]
      ,b.[original_login_name] AS [WaitingUserConnectionLogin] 
      ,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
      ,b.[wait_duration_ms] AS [WaitDuration]
      ,b.[wait_type] AS [WaitType]
      ,t.[request_mode] AS [WaitRequestMode]
      ,UPPER(b.[status]) AS [WaitingProcessStatus]
      ,UPPER(s1.[status]) AS [BlockingSessionStatus]
      ,b.[wait_resource] AS [WaitResource]
      ,t.[resource_type] AS [WaitResourceType]
      ,t.[resource_database_id] AS [WaitResourceDatabaseID]
      ,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
      ,b.[resource_description] AS [WaitResourceDescription]
      ,b.[program_name] AS [WaitingSessionProgramName]
      ,s1.[program_name] AS [BlockingSessionProgramName]
      ,b.[host_name] AS [WaitingHost]
      ,s1.[host_name] AS [BlockingHost]
      ,b.[command] AS [WaitingCommandType]
      ,b.[text] AS [WaitingCommandText]
      ,b.[row_count] AS [WaitingCommandRowCount]
      ,b.[percent_complete] AS [WaitingCommandPercentComplete]
      ,b.[cpu_time] AS [WaitingCommandCPUTime]
      ,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
      ,b.[reads] AS [WaitingCommandReads]
      ,b.[writes] AS [WaitingCommandWrites]
      ,b.[logical_reads] AS [WaitingCommandLogicalReads]
      ,b.[query_plan] AS [WaitingCommandQueryPlan]
      ,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO

Create the templates for monitoring the MS SQL High Availability (Always On, database mirroring etc.) performance.

Create a template for monitoring the MS SQL logs.

Incorrect trigger for database in a Log shipping. After restoring transaction log to database database status changes from restoring to recovery. it may activate trigger.

Hello!
How can I monitor the MS SQL filegroup and file free and used space?
I found Log percent used only.

Add item to show how long backup took. For a specific database and specific backup type

Hallo,

ich habe aktuell das Problem, dass der Server hinter dem Proxy diesen Fehler für alle SQL Test ausgibt.

23620:20211111:172307.861 active check "perf_counter["\SQLServer:Transactions\Longest Transaction Running Time"]" is not supported: Invalid performance counter format.

Da bitte ich einmal um Hilfe.

Mit freundlichen Grüßen
Eric

Hi

Thanks for template.

Template with discovery.
sql 2014

SQL Server Databases Discovery
database.mssql.status
Value "Error executing query on !" of type "string" is not suitable for value type "Numeric (unsigned)"
Can you help with this?

Thanks

{Template Microsoft SQL Server DE Tier 2:perf_counter["{#SQLINSTANCE}:General Statistics\Processes blocked"].last(300,0)}>1
TO
{Template Microsoft SQL Server DE Tier 2:perf_counter["{#SQLINSTANCE}:General Statistics\Processes blocked"].avg(300,0)}>1

AND

{Template Microsoft SQL Server DE Tier 2:perf_counter["{#SQLINSTANCE}:General Statistics\Processes blocked"].last(60,0)}>5
TO
{Template Microsoft SQL Server DE Tier 2:perf_counter["{#SQLINSTANCE}:General Statistics\Processes blocked"].avg(60,0)}>5

Update zabbix manual, with info about Performance counter average value and how it is collected.
Case and point
perf_counter["\SQLServer:Replication Dist.(repl_name)\Dist:Delivered Cmds/sec",60]

Review triggers for Event Viewer items. Consider changing from nodata to close manually function

Hi,
Currently we have MSSQL 2016 standard, installed the zabbix agent then the powershell scripts. After that i put the role sysadmin to NT i only get alert for the backup but i dont see the information of mssql nor the agents?

Thank you

Template deploying to a cluster. How to manage a node issue? Probably gonna need to change item type too.

Hi MantasTumenas,

I have imported the template i am able to see all the metrics of the SQL server but not the SQL database. What needs to be done to get the metrics of the databases in SQL server . Help will be much appreciated.

Hello,

The triggers not work in zabbix 5.4.3, for example:

Can you try help fix this?

Thank you.

Fix trigger SQL Instance {#SQLINSTANCENAME}: Database Engine Critical Errors. Instead of eventlog[Application,,"Critical","{#SQLINSTANCENAME}",,,skip].last(,0)}<>0 use trigger function log severity

"Value should be a JSON object" error in discovery tab. One of possible reasons - installed but stopped database engine instance. Also possible to get this error, then there is one installed and running and one installed but stopped database engine instance. Solutions - rewrite powershell script, but it will take time. Faster solutions - uninstall stopped database engine. Or comment out catch section in the beginning of the script:

    $connection.Open()
}
catch
{
    #Write-Host "255"
    $DataSet = $null
    $connection = $null
}
try

I am having trouble with your template, the template works fine when there is one instance (using the multi-instance template) but when there are more than one instances the discovery rules doesn't working at all. The scripts seems working fine and the "zabbix_get" on zabbix server receiving the data but the item prototypes never become items. What possible is going wrong?

Hello,

I posted here https://www.zabbix.com/forum/em-portugues-y-en-espanol/380401-lentitud-en-zabbix-agent-active-sql-server-y-powershell (sorry, in Spanish) a problem related with Zabbix or PowerShell configuration.
The problem resides in there are a lot of items queued in Zabbix Server, all of them related with Active items about this template. I checked on the server, only one PowerShell.exe process is running at a time. Each PowerShell query spends 5 seconds more or less but we have 4 instances in one server with 20 databases, and the information never is up to date.

In the graph of the post you can see how often I have data, in this example, status database.

I appreciate your comments.

Regards,

Database discovery is not working with databases with Lithuanian symbols. Powershell discovers them correctly, but zabbix does not accept.
Solution rewrite PS script, so it does convert ČĘĖĮŠŲŲŪ to Latin.

Discovery function is not working with Powershell 2.0 (default on Win 2008 R2).

Create a template for monitoring the MS SQL backups.

Hello,

I followed the instructions and added the baseline template to one host.
Now when the discovery runs, it returns me the following message: Value should be a JSON object.
This happens for the SQL Server Database Discovery and for the SQL Server Instances Discovery.
Is there anything I´m missing in my configuration ?

After import Template Template Microsoft SQL Server DE Baseline.xml (With SQL instance discovery\Templates), Zabbix have't items

Are there are any reasons you don’t just report number of minutes since last backup instead of the hardcoded values, then it would be possible in a graph to follow as the time increases and you would see how long time since it was the backup was taken not just that it past the trigger time?
Mathias Andersson

item return KB，but your unit is B

Powershell scripts don't work on 2008R2 (MSSQL 2014) with built-in powershell 2.0

3164:20191101:093524.568 EXECUTE_STR() command:'powershell.exe -NoProfile -ExecutionPolicy Bypass -File "C:\mydb\ZabbixScripts\Discovery.mssql.instanceagentname.ps1"' len:504 cmd_result:'The term 'ConvertTo-'
3164:20191101:093524.568 for key [agent.mssql.discovery] received value [The term 'ConvertTo-Json' is not recognized as the name of a cmdlet, function,

Tested after installing WMF 5.1 (PS 5.1) and works as expected. Maybe add to documentation?
Thanks for great template otherwise!

Hi,

Running Zabbix Server 5.2 with zabbix agent 2 [5.2.7] and followed your guide for installing "Microsoft SQL Server" since that one is newer than instance discovery vs without instance discovery.

Templates and value mapping are imported.

Installed zabbix agent 2 in C:\Program Files\Zabbix\ on the host with subfolder \bin to copy MSSQL folder to since bin folder doesn't exist.

Also added the conf files to main conf. No errors in the log after restarting the agent.

Added Template Microsoft SQL Server DE Tier 3 to the host as a test and can only see 3 discovery rules.

No items or triggers are added. Can't "execute now".

What am I missing here? Shouldn't items be populated automatically?

Fix URL’s in triggers prototypes. Microsoft updated their URLs and current links leads to a page 404.

I installed the zabbix windows agent on the secondary server of an always on cluster. Everything seems to be working except the database status zabbix items. Zabbix says they are not supported for some reason. All other triggers/items from the plugin are working properly. I don't see anything in the logs. I only have one instance with about 12 different databases. It seems like the discovery scripts account for servers with only one instance. I gave the SYSTEM user sysadmin access so that shouldn't be the problem. I'm running zabbix 4.2 Any thoughts? Maybe database status is only supported on primary server?

Editing to remove this because I created an issue before fully understanding an issue. Sorry to bug you.

hello, i have followed the instructions but it get the error below when i restart the zabbix agent

cannot load user parameters: user parameter "backup.sysdatabase.mssql.discovery,powershell.exe -NoProfile -ExecutionPolicy Bypass -File "C:\Program Files\Zabbix Agent\bin\MSSQL\DiscoveryDatabaseBackup\Discovery.mssql.sysdatabasename.ps1"": key "backup.sysdatabase.mssql.discovery" already exists