Make a notification from a sql request

[schranz]

Hello,

I comming for helping with a notification configuration.

I am looking to create a notification when a mac and ip address does not match a dhcp server

I have a host group so the IP address is assigned by a dhcp server. I install arpwatch (linux utility) for watch the équipement in this group. This utility can record all new équipement in a text file with the ip and mac address. I have make the same record with the dhcp.

When i have good understand i must create a item in the host and i must create a used parameter . The user parameter that i would make is :

user parameter = mysql  -u user -p SELECT * FROM fichierdat WHERE fichierdat.adresse_ip NOT IN (SELECT dhcp.adresse_ip FROM dhcp)

But i’m loose can someone help me to explain to make the mail notification with this query ?

Thank you

[filipp.sudanov]

Zabbix basics are the folliwing:

1) Item. That's a value - number, text. It's obtained once per n seconds and stored in zabbix database (For log items - every time a new line appears. For trapper items - each time zabbix_send sends the value).

2) Trigger. It watches at values of one or several items, checks if they correspond to some criteria. If criteria is met, trigger switches to problem, otherwise - to ok.

3) Action. When a trigger changes it's state, an action can happen, e.g. send smth to email.

And there's some hirarchy - items belong to hosts (computers or some equipment units). Hosts belong to host groups.

A user parameter is actually an item. What should be the values of your items, that are stored in the db? An IP address, or a MAC address, or both? So it's probably a string. When should be a new value stored - once when a new address is detected? If so, I would probably make a trapper item and send a string to it when a new address is detected. Then create a trigger, that triggers every time when item gets updated. And an action to send email when that trigger gets triggered.

[schranz]

Thank you to be interested in my question

For the zabbix basic, it’ok for the explain. but for the next i have not all understand.

A user parameter is actually an item

A user parameter is actually an item. for the moment i have only enable discovery of equipement. Agent is not yet installed.

What should be the values of your items, that are stored in the db? An IP address, or a MAC address, or both? So it's probably a string

to answer an this question the ideal would be to have the actual IP and mac address of the new connected equipement. And exactly that a string value.

When should be a new value stored - once when a new address is detected?

For this question , the goal of my script is effectly when the utility arpwatch detected a new ip and mac adresse that is recorded in the db. or I am currently lost it to the SQL query that compares two tables and the result sent by the value of the new equipment. that this maybe what I want to do is not possible.

if so, I would probably make a trapper item and send a string to it when a new address is detected. Then create a trigger, that triggers every time when item gets updated. And an action to send email when that trigger gets triggered

have you example of a trap item based on what I want to do please ?

[filipp.sudanov]

Trapper item creation is described in details in official docs: https://www.zabbix.com/documentation...mtypes/trapper

The it would look approximately in that way:
You hava a bash script, that is called from cron and analyzes the local network. If the script detects a new ip, it calls zabbix_sender, a new value appear in database, trigger gets activated, actioni happens and email is sent.
But in this way zabbix is not utilized to it's full power - it's just one item for all the hosts, you can not see list of all hosts in web interface, etc.

I would probably do this with zabbix network discovery tools (https://www.zabbix.com/documentation...work_discovery) in following way:
Zabbix network discovery scans ip range to find new ip addresses. When a new ip is found, a new host is created. Created host gets a set of items and triggers from so called prototype. The created host has a custom item, that calls your script, giving it host ip; the script checks with db, if ip corresponds to the mac address, and returns 0/1, or "ok"/<incorrect mac address>. Trigger reacts to received value.
In this way you can add more items to your discovered hosts - ping them, etc...

[schranz]

OK i will read the two link for the moment. I comming to you when i have make the script et crate the item trapper.

Thanks

[schranz]

Hi , i have read your link but i have question.

you say:

You hava a bash script, that is called from cron and analyzes the local network. If the script detects a new ip, it calls zabbix_sender, a new value appear in database, trigger gets activated, actioni happens and email is sent.

yes that's pretty much what I'm looking to do. For the script i have make that .

req1=$'LOAD DATA LOCAL INFILE "/var/lib/arpwatch/arp.dat"
                 INTO TABLE arpwatch
                 FIELDS ENCLOSED BY " "
                 LINES TERMINATED BY "\n";'

req2=$"select * from arpwatch where arpwatch.adresse_ip not in(select dhcp.adresse_ip from dhcp);"

It's juste a comparison between the adresse ip,mac from the dhcp and my utility.

But in this way zabbix is not utilized to it's full power - it's just one item for all the hosts, you can not see list of all hosts in web interface, etc.

That exactly but the hosts are already discovered made with autodiscovery tool. Also the hosts are simple devices I don't need to have an advanced monitoring.

the script checks with db, if ip corresponds to the mac address, and returns 0/1, or "ok"/<incorrect mac address>.

If I understand you must always return an answer for zabbix or the script does not work. However I do not see how to return the value 0/1 my request sql? Please have you an example?

Thanks you

[jan.garaj]

Just for the record - reference: http://stackoverflow.com/questions/2...-a-mysql-query