Autodiscovery duplicate hosts

[perun.84]

Hi. I have next zabbix environment: One server, separate MySQL db and four proxies. Three of proxies have auto discovery rules set to explore my network (few hundred C classes). What is the problem? Sometimes two or three proxies discover same host and it is written two or three times in databases. But only one of them has data, the others are empty. Is there any way to detect and delete unnecessary (duplicate) hosts?

[Linwood]

Is there a reason you have the proxies overlapping? I don't know how to solve your actual question, but off hand it seems like avoiding the situation in the first place would save polling resources and avoid the problem? Give each of the four proxies a quarter to discover?

[perun.84]

I cannot. Devices have interfaces in different subnets and I couldn't separate subnets to reach every interface of device and other proxies to not reach any of device's interface... Do you know how I can delete some devices from database directly? I'm searching for query for complete delete device from MySQL database. It could help me.

[Linwood]

Hopefully someone else will recommend how to do that. I've done some maintenance directly in sql, but in postgresql where I know there are some cascade delete triggers, not sure they are in mysql or not. Plus there's some risk of corrupting your setup, so I do not want to try.

The safest way is probably to build a list of hosts to delete, and write a little API script to delete them all, so it goes through zabbix's logic and cleans up any dangling references appropriately. That's a bit harder the first time, but much safer.

[perun.84]

I've solved it. I wrote bash script which goes through hosts inspecting if it is newly added (name is IP address). If it is scripts checks if host already exists (by snmp name) and deletes it if it is (by python script and hostid). If it isn't script updates host record changing ip address with snmp name.

Here are scripts:

Bash script

#!/bin/bash

#### Description: Looking for automatcally added host and updae its names to snmp.sysName.0
#### Written by: Aleksandar - 
#### 05-2016

### Credentials ###
dbuser="dbuser"
dbpsw="dbpass"
dbname="dbname"
dbhost="dbhost"
####################


function is_valid_ipv4() {
  local -a octets=( ${1//\./ } )
  local RETURNVALUE=0

  # return an error if the IP doesn't have exactly 4 octets
  [[ ${#octets[@]} -ne 4 ]] && return 1

  for octet in ${octets[@]}
  do
    if [[ ${octet} =~ ^[0-9]{1,3}$ ]]
    then # shift number by 8 bits, anything larger than 255 will be > 0
      ((RETURNVALUE += octet>>8 ))
    else # octet wasn't numeric, return error
      return 1
    fi
  done
  return ${RETURNVALUE}
}

mysql -h ${dbhost} --user ${dbuser} -p${dbpsw} ${dbname} -Bse 'select host from hosts where host=name' | while read name; do
        is_valid_ipv4 $name
        if [ $? == 0 ]
        then
                dcheckid=$(mysql --host ${dbhost} --user ${dbuser} -p${dbpsw} ${dbname} -sNe "select dcheckid from dservices where ip='$name'")
                comm=$(mysql --host ${dbhost} --user ${dbuser} -p${dbpsw} ${dbname} -sNe "select snmp_community from dchecks where dcheckid='$dcheckid'")
                sysName=$(snmpwalk -v 2c -c $comm $name SNMPv2-MIB::sysName.0)
                sysName=$(echo $sysName | awk '{print $4}')
                old=$(mysql --host ${dbhost} --user ${dbuser} -p${dbpsw} ${dbname} -sNe "select hostid from hosts where name='$sysName'")
		descr=$(snmpwalk -v 2c -c $comm $ip SNMPv2-MIB::sysDescr.0)
                if [ -z "$old" ]
                then
                        mysql --host ${dbhost} --user ${dbuser} -p${dbpsw} ${dbname} -sNe "update hosts set name='$sysName' where host='$name'"
                else
                        hostid=$(mysql -h ${dbhost} --user ${dbuser} -p${dbpsw} ${dbname} -Bse "select hostid from hosts where host='$name'")
                        echo "$hostid couldn't be inserted"
                        $(./deletehost.py $hostid)
                fi
        else
                if [[ "$name" =~ _[1-9]$ ]]
                then
                        ip=$(echo $name | awk -F"_" '{print $1}')
                        second=$(echo $name | awk -F"_" '{print $2}')
                        is_valid_ipv4 $ip
                        if [ $? == 0 ]
                        then
                                hostid=$(mysql -h ${dbhost} --user ${dbuser} -p${dbpsw} ${dbname} -Bse "select hostid from hosts where host='$name'")
                                $(./deletehost.py $hostid)
                        fi
		fi      
        fi
done

Python script for deleting:

#!/usr/bin/env python
import sys
from pyzabbix import ZabbixAPI

zapi = ZabbixAPI("https://zabbix.test.com")
zapi.login("zabbixuser", "zabbixpass")

zapi.host.delete(sys.argv[1])