Is it possible to monitor network errors in SQL that are returned when running a query such as below?
– Return count of number of network errors (error:40).
SELECT COUNT(*)
FROM (database table)
WHERE InsertDate > DATEADD(HOUR, -1, GETDATE())
AND Message LIKE ‘%A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)%’
Hello. There is no monitoring rule to cover this. Here you can find a good example how to build a customer check for your needs: Local check - custom SQL query for MSSQL
You can/need to edit this draft script (ps1) to your needs and (if you want a synchronized execution) add this to the Windows Task Scheduler for regular execution. You can “inject” the Output to the Spool Directory of the Agent (The spool directory - Adding file contents to agent outputs)
You also need to add the agent seperator to your local check. For example:
Write Output “<<>>” | Out-File -FilePath “Agent-Spool-Directory\mssqlstatus.log”
Write-Output “0 $service_name - $result connections (OK above 0 and below “$warn_threshold”).” | Out-File -FilePath “Agent-Spool-Directory\mssqlstatus.log” -Append