Hi all!
I’m pretty new with CheckMK. Is there any article with an example of a custom (local) check with custom SQL-query for MSSQL 2016?
The SQL query itself works fine and returns several columns, one of which should be checked by CheckMK. This exact column returns Float.
Are there any simple examples of a local check script to be monitored and added to CheckMK?
We did that for our staff internal. Following some powershell code for a singe query:
############################################################################
# SET LOCAL PARAMS
############################################################################
set-executionpolicy RemoteSigned
# SET VARIABLES
# You have TO SET your SQL Server name NEXT :
#if you USE a particular instance, the variable should be "$sqlserver = SERVERNAME\INSTANCE"
##################################################################
#DONT ADD SPACE IN the STRING IF you want TO CHANGE Service_name
$service_name = "MSSQL_CONNECTIONS"
##################################################################
$sqlServer = "localhost" # Example server variable
$database = "master" # Example OF DB Uses
$query = $("SELECT TOP 10 [connections] FROM [master].[dbo].[spt_monitor]") #Query That need TO be replaced
$auth = "BDDUSER" #The other mode IS "IS", IF you want TO USE Windows integrated credentials CHANGE "BDDUSER" BY "IS"
$uid = "User" # Edit TO your specific USER
$pwd = "Password" #Edit TO your specific
############################################################################
# NO TOUCHING
############################################################################
#Connection Method Test
IF ($auth -eq "BDDUSER") {
$connString = "Server=$sqlServer;Database=$database;User Id=$uid;Password=$pwd;"
}
ELSE{
$connString = "Server= $sqlServer;Database=$database;Integrated Security=True;"
}
#Connect
Try{
$sqlConn = New-Object System.Data.SQLClient.SQLConnection
$sqlConn.ConnectionString = $connString
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConn
$sqlConn.Open()
}
Catch{
Write-Host "3 $service_name - "$_.Exception.Message
exit
}
#Query
Try{
#Command One
$sqlCmd.CommandText = $query
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd
#Fill SQL adapter WITH the dataset AND affect it TO a sqlFill Variable
$dataSet = New-Object System.Data.DataSet
$sqlFill = $sqlAdapter.Fill($dataSet)
$result = $dataSet.Tables[0].Rows.connections
#Liberation OF the LAST Query
$sqlCmd.Dispose()
}
Catch{
Write-Host "3 $service_name - "$_.Exception.Message
}
#In any CASE, close the connection
Finally{
$sqlconn.Close()
}
############################################################################
# EDIT
############################################################################
#We exit ALL TIME WITH this STATUS. here,
#if connections are less than 15 its ok
#Between 15 AND 50 its Warning
#More than 50 its critical
# 0 = OK
# 1 = WARNING
# 2 = CRITICAL
# 3 = UNKNOWN
[INT]$crit_threshold = 50
[INT]$warn_threshold = 2
Try{
#Do anything WITH your RESULT
#In this CASE, We CHECK the total connection NUMBER (that thresholds are just examples)
IF($result -gt $crit_threshold)
{Write-Host "2 $service_name - $result connections (CRIT above "$crit_threshold")"}
elseif($result -gt $warn_threshold)
{Write-Host "1 $service_name - $result connections (WARN between "$warn_threshold" and "$crit_threshold")"}
ELSE
{Write-Host "0 $service_name - $result connections (OK above 0 and below "$warn_threshold")"}
#Maybe ADD SOME FUNCTION TO make Specifics checks AND validate the query
}
Catch{
#We exit WITH UNKNOWN alert IN CASE something goes wrong WITH thresholding
Write-Host "3 $service_name - "$_.Exception.Message
}
############################################################################
# DEBUG
############################################################################
#If you want TO know what results you GET FROM your raw SQL query, uncomment the following block
<#Try{
#$dataSet.Tables[0].Rows | Select-Object * -ExcludeProperty ItemArray, TABLE, RowError, RowState, HasErrors | ConvertTo-Json
#$dataSet2.Tables[0].Rows | Select-Object * -ExcludeProperty ItemArray, TABLE, RowError, RowState, HasErrors | ConvertTo-Json
}
#Catch{
#Write-Host "3 ARRAY Problem(s) : "$_.Exception.Message
#exit
}#>
And here some powershell code for multiple queries:
############################################################################
# SET LOCAL PARAMS
############################################################################
# SET VARIABLES
# You have TO SET your SQL Server name NEXT :
#if you USE a particular instance, the variable should be "$sqlserver = SERVERNAME\INSTANCE"
$sqlServer = "localhost" # Example server variable
$database = "IJCore" # Example OF DB Uses
$query = $("SELECT TOP 5 [dataCalloffID] FROM [IJCore].[dbo].[archivedCalloff]") #Query That need TO be replaced
$query2 = $("SELECT TOP 2 [dataCalloffAdditionalInteger1] FROM [IJCore].[dbo].[archivedCalloff]") #Query That need TO be replaced
$auth = "IS" #The other mode IS "IS", IF you want TO USE Windows integrated credentials CHANGE "BDDUSER" BY "IS"
$uid = "User" # Edit TO your specific USER
$pwd = "Password" #Edit TO your specific
############################################################################
# NO TOUCHING
############################################################################
FUNCTION printout_errors($error_code, [string]$message){
#Error Message Printing
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
Write-Host "$error_code $message $ErrorMessage, $FailedItem"
}
#Connection Method Test
IF ($auth -eq "BDDUSER") {
$connString = "Server=$sqlServer;Database=$database;User Id=$uid;Password=$pwd;"
}
ELSE{
$connString = "Server= $sqlServer;Database=$database;Integrated Security=True;"
}
#Connect
Try{
$sqlConn = New-Object System.Data.SQLClient.SQLConnection
$sqlConn.ConnectionString = $connString
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConn
$sqlConn.Open()
}
Catch{
printout_errors(3,"Connection Problems")
exit
}
#Query
Try{
#Command One
$sqlCmd.CommandText = $query
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd
$dataSet = New-Object System.Data.DataSet
$sqlFill = $sqlAdapter.Fill($dataSet)
#Liberation OF the LAST Query
$sqlCmd.Dispose()
}
Catch{
printout_errors(2,"MSSQL query failed for the following reason : ")
}
Try {
#Second Query
$sqlCmd.CommandText = $query2
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd
$dataSet2 = New-Object System.Data.DataSet
$sqlFill2 =$sqlAdapter.Fill($dataSet2)
}
Catch{
printout_errors(2,"MSSQL query_2 failed for the following reason : ")
}
#In any CASE, close the connection
Finally{
$sqlconn.Close()
}
############################################################################
# DEBUG
############################################################################
#If you want TO know what results you GET FROM your raw SQL query, uncomment the following block
<#Try{
#$dataSet.Tables[0].Rows | Select-Object * -ExcludeProperty ItemArray, TABLE, RowError, RowState, HasErrors | ConvertTo-Json
#$dataSet2.Tables[0].Rows | Select-Object * -ExcludeProperty ItemArray, TABLE, RowError, RowState, HasErrors | ConvertTo-Json
}
Catch{
printout_errors(3,"ARRAY Problem(s)")
exit
}#>
############################################################################
# EDIT
############################################################################
Try{
#Do anything WITH your RESULT
#In this CASE, $sqlFill IS a COUNT ON the rownumber you selected ON request
#Example which COUNT ROWS AND define Statement ON this NUMBER
IF($sqlFill -gt 1) {
$result = $dataSet.Tables[0].Rows.Count
Write-Host "0 MSSQL Query OK - $result Rows is OK"
}
elseif($sqlFill -eq 1){
$result = $dataSet.Tables[0].Rows.Count
Write-Host "2 MSSQL Query Warning - $result Rows is Warning"
}
ELSE{
$result = $dataSet.Tables[0].Rows.Count
Write-Host "2 MSSQL Query Critical - $result Rows is Crticial"
}
#Maybe ADD SOME FUNCTION TO make Specifics checks AND validate the query
}
Catch{
printout_errors(2,"Custom handling of SQL result failed for the following reason : ")
}
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed. Contact an admin if you think this should be re-opened.