System.Run[] Item

[Caphoras]

Hello Guys,
I am stuck in a little Problem i can't solve.

My task is "simple" but i am a total noob when it comes to zabbix.
I have an MSSQL-Database, and i have to Monitor certain values in it.

I've wrote a python script wich is working perfectly fine and does what its supposed. It connects to the database and check the values and it prints 0, 1 or 2 depending on the current value.
So my idea was creating a Item on the zabbix server (where i created that python script, and let it run).
I can execute it on the commandshell on the zabbix server, but it wont work within zabbix

the location of the python file is /home/User/odbc-test3
the Item configuration is:

Name - get odbc-test
Type - Zabbix agent
Key - system.run[python /home/User/odbc-test3]
Interface - 127.0.01
Intervall 5s

Ive configured the /etc/zabbix/zabbix_agend.conf with

AllowKey:system.run[*]

altough it says

#       If no AllowKey or DenyKey rules defined, all keys are allowed.

but i still get the information unsupported item Key.

[Oz_Joris]

hello,

make sure you have an '=' and not a ':' in your configuration file (AllowKey=system.run[*]) and everything in your system.run should be enclosed in quotation marks (system.run["python /home/User/odbc-test3"]) with a zabbix or zabbix active agent

I hope I helped you !

[Caphoras]

yes my bad, its AllowKey=system.run[*]! ive used that video for trainingspurpose: https://www.youtube.com/watch?v=Oha53b00vR0

[Caphoras]

Found the answer:

I had to add EnableRemoteCommands=1 even it is depricated.
wow i didn't expect this to be the reason it was not working...

[Oz_Joris]

Which version of Zabbix are you using? On 5.0+ it seems that EnableRemoteCommands=1 is replaced by AllowKey=system.run[*] right

Well, at least it works,

have a nice day, Oz_Joris

[Caphoras]

I am using Zabbix 5.4 i just updated it from 3.0 to 5.4.
Now i i have an additional issue: i copied the file and renamed it so i can use it.
I also changed my sql statement a bit (like

select data where "%xyz% to select data wehere description like %xyz%123%

).
it works in the command shell still without an issue. But now i'll get a not supported error and "Timeout while executing a shell script."

[Caphoras]

it is definitley that additional sql statement. if i remove it it works. I came to the conclusion its about the search time during the database. I can't tell it may take like 3 seconds to find the needed information.
I increased the TimeOut to 30 in the server conf. but that didn't solve my problem is there another file i need to edit to prevent the timeout to happen?

[Oz_Joris]

Hello Caphoras,

It is amazing to me that this simple order can take so much time.

Indeed if you increase the timeout on the server, there may also be a timeout change on the agent in question in its configuration file.

Hoping to help you, Oz_Joris

[Caphoras]

Oh, that means i also have to change that time on the sql server agent. Okay that explains it.
It is actually just an statement that should check if the last incoming information on the database exceeds more than 5 minutes. If it does, it should trigger an alarm. The alarm Trigger works just fine (Via OpsGenie media Hook). But since i am not really good with sql statements and database i struggle here a lot. First the ODBC tools installing and so on.

At first place i wanted do a discovery rule wich checks the database and put the statement in there but well lets say that didn't work and comsumed so much time. So i decided to make sure there is no firewall problem or anything, thats why i wrote the python script. Wich is working more or less fine....
The SQL Statement i received from a co worker wich is not available for the next two weeks. Well it didn't work as planned so i am trying to fix everything with my like 3 Months of IT-Experience.

I thought i could do those settings on serverside and then just use the local agent wich has all the networkconfiguration working.

and btw. you are an huge help i am so gratefull for your efforts!!

[Caphoras]

#!/usr/bin/python -u
# The -u ensures we have unbuffered output
# small script for odbc tests
# requires MSODBC, see
# https://docs.microsoft.com/de-de/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#debian17

import sys,os
import pyodbc
import time
from datetime import datetime

connection_string = "Driver={ODBC Driver 17 for SQL Server}; " + \
"Server=x.x.x.x,1453; " + \
"Database=PNC; " + \
"uid=zbx_monitor; " + \
"pwd=supersecretpassword; "

#print "connection_string= " + connection_string

try:
connection = pyodbc.connect(connection_string, readonly=True )
except pyodbc.Error as ex:
print "NOT WORKING"

cursor = connection.cursor()
# Get Date in the right format (YYYY-MM-DD)

cursor.execute("select GETDATE()")
row = cursor.fetchone()
string= (row)
string = str(string[0])
string = string[0:10]

#print "select GETDATE() delivers:"
#print (string)


# Start the Cursorexecute with the needed sql statement
# print "starting cursor execution"
cursor.execute("select datediff(ss, max(I.ReceivedOn), getdate()), max(I.ReceivedOn) from [dbo].[MessageRawIn] I where I.MessageDetail like '%PE1&LD20200713%' and ReceivedOn>DATEADD(DD, -7, " + string + ")" )
row = cursor.fetchone()

# Output of the received information
#print "Output of the cursor execution is:"
#print (row)


#print (row[0])

#Alarm Check 0 = No Alarm 1 = Warning 2 = Alarm


#print (row[0])

#Alarm Check
#print "check if Alarm is needed"
if row[0]<1200:
print "0"
elif row[0]<1400:
print "1"
else:
print "2"


#print "closing connection"
cursor.close()
connection.close()

Thats my script btw.

[Oz_Joris]

I admit I can't help you much with the script, I don't know enough about SQL ^^

If you have changed the timeout on the agent side, does it work now?

Sincerely, Oz_Joris

[Caphoras]

yea after some sessions with the dba admin i was able to fix most of the problems.
It was mostly the sql code wich was not working proberly. Now everything works at this point. thank you very much!