I had an issue where for what ever reason, some auto discovered hosts that where added, where named after their IP address and not their DNS name. The problem was both Network Admin's not updating DNS with Router/Switch information and Zabbix not resolving known devices DNS name.
With 700+ hosts, I only had about 80+ with this issue. This led me down the track of creating a series of scripts to automatically update Windows DNS with the sysName obtained via SNMP of the problem device.
DNZ_Updater.v.1.0.pl connects to your zabbix db, identifies devices named with an IP, loops through each device and does a SNMP sysName lookup for the device, than spits this out in CSV format for DNS_Record_Creator.ps1 to create the host and reverse lookup record for it.
* You will need to identify the Group ID in SQL your device belongs to or alter the select statement that returns list of IP based devices.
** You will need to change 192.%.%.% and 10.%.%.% to include your ip ranges for devices
*** DNS_Record_Creator.ps1 has been modified to fit out PTR Zones which where created incorrectly.
DNZ_Updater.v.1.0.pl
#!/usr/bin/perl
# DNS Updater script for IP based network devices
# tested with Zabbix 2.0.7
#
# author : sydneysider
#
# This script provides an automatic meathod for DNS Updates of host devices that do no have dns entries.
# It is currently configured to produce a text CSV of IP, DNS Host Names which than get imported via Windows into Windows DNS
#
# Changes made for Zabbix 2.0.3-2.0.7
# v0.1
#
use DBI;
use Net::SNMP;
$zabbix_server = "server1.domain.com";
$zabbix_port = 10051;
# zabbix database settings
$zabbix_db_user = "zabbixadmin";
print "Enter mysql password for $zabbix_db_user: ";
my $zabbix_db_pass = <STDIN>;
chomp $zabbix_db_pass;
# Query zbx DB for ipaddresses
$dbh = DBI->connect('dbi:mysql:zabbix:'.$zabbix_server,$zabbi x_db_user,$zabbix_db_pass) or die "Connection Error: $DBI::errstr\n";
# Forced use of Network Device group. This can be changed to included other groups if neccessary.
$sql = "select distinct h.host from hosts h join hosts_groups hg on h.hostid = hg.hostid where hg.groupid = '100100000000006' and h.host like '192.%.%.%' or h.host like '10.%.%.%'";
$sth = $dbh->prepare($sql);
$sth->execute or die "SQL Error: $DBI::errstr\n";
$count = 0;
my $ipaddress = "";
while (@row = $sth->fetchrow_array) {
$ipaddress .= "@row,"; # join(", ", @row), "\n";
$count ++;
}
print "\nIP Addresses found in Zabbix Database: $count\n\n";
#chomp($ipaddress);
# Convert to list
my @a = ();
my $exclude = "";
@a =split(/\,/, $ipaddress);
#print "size of array: " . @a . ".\n";
open(FILE, ">DNS_out.csv") or die "Could not open file: $!";
# Print CSV Header
print FILE "Computer,IP\n";
#Loop through each IP attempting to retrieve the SNMP hostname
foreach my $ip (@a) {
#print FILE system("snmpget -v2c -cpublic -M /usr/share/mibs ".$ip." iso.3.6.1.2.1.1.5.0 | awk '{ print $4}'");
# Check if reverse lookup does actually exist
$exclude = "yes";
my $nslookupresult = "nslookup $ip | awk '{ print \$4 }' | sed '/^\$/d'";
my $nsres = `$nslookupresult`;
#Could not work if you have "find" in a devices name
if (index($nsres, 'find') != -1) {
$nsres = "Not Found!\n";
#Exclude from writing to file
$exclude = "no";
}
print "Nslookup result $ip : ".$nsres;
my $OID_sysName = '.1.3.6.1.2.1.1.5.0';
my ($session, $error) = Net::SNMP->session(
-hostname => shift || $ip,
-community => shift || 'public',
);
if (!defined $session) {
printf "ERROR: %s.\n\n", $error;
next;
#exit 1;
}
my $result = $session->get_request(-varbindlist => [ $OID_sysName ],);
if (!defined $result) {
printf "ERROR: %s.\n\n", $session->error();
next;
# $session->close();
# exit 1;
}
my $hostName = $result->{$OID_sysName};
$hostName =~ s/.domain.com//;
print "Retrieving $ip hostname : ".$hostName."\n\n";
$session->close();
if ($exclude eq "no") {
print FILE $hostName;
print FILE ",$ip\n";
}
}
DNS_Record_Creator.ps1
#By CSS ERW 14 Sept 2013
#This script create DNS A Record and associate Reverse PTR
#If Reverse Zone doesn't exist script create it
#Create records.csv file with Computer,IP information
#see example below add first line to your csv file
#
#Computer,IP
#Computer,192.168.0.1
#Computer1,192.168.0.2
#Computer2,192.168.0.3
#
#Change with your dns server info $Servername and $Domain
$ServerName = "w2K8-rodc"
$domain = "venus.com"
Import-Csv c:\DNS_out.csv | ForEach-Object {
#Def variable
$Computer = "$($_.Computer).$domain"
$addr = $_.IP -split "\."
$rzone = "$($addr[0]).in-addr.arpa"
#Create Dns entries
dnscmd $Servername /recordadd $domain "$($_.Computer)" /CreatePTR A "$($_.IP)"
#Create New Reverse Zone if zone already exist, system return a normal error
#dnscmd $Servername /zoneadd $rzone /primary
#Create reverse DNS
# dnscmd $Servername /recordadd $rzone "$($addr[3]).$($addr[2]).$($addr[1])." PTR $Computer
With 700+ hosts, I only had about 80+ with this issue. This led me down the track of creating a series of scripts to automatically update Windows DNS with the sysName obtained via SNMP of the problem device.
DNZ_Updater.v.1.0.pl connects to your zabbix db, identifies devices named with an IP, loops through each device and does a SNMP sysName lookup for the device, than spits this out in CSV format for DNS_Record_Creator.ps1 to create the host and reverse lookup record for it.
* You will need to identify the Group ID in SQL your device belongs to or alter the select statement that returns list of IP based devices.
** You will need to change 192.%.%.% and 10.%.%.% to include your ip ranges for devices
*** DNS_Record_Creator.ps1 has been modified to fit out PTR Zones which where created incorrectly.
DNZ_Updater.v.1.0.pl
#!/usr/bin/perl
# DNS Updater script for IP based network devices
# tested with Zabbix 2.0.7
#
# author : sydneysider
#
# This script provides an automatic meathod for DNS Updates of host devices that do no have dns entries.
# It is currently configured to produce a text CSV of IP, DNS Host Names which than get imported via Windows into Windows DNS
#
# Changes made for Zabbix 2.0.3-2.0.7
# v0.1
#
use DBI;
use Net::SNMP;
$zabbix_server = "server1.domain.com";
$zabbix_port = 10051;
# zabbix database settings
$zabbix_db_user = "zabbixadmin";
print "Enter mysql password for $zabbix_db_user: ";
my $zabbix_db_pass = <STDIN>;
chomp $zabbix_db_pass;
# Query zbx DB for ipaddresses
$dbh = DBI->connect('dbi:mysql:zabbix:'.$zabbix_server,$zabbi x_db_user,$zabbix_db_pass) or die "Connection Error: $DBI::errstr\n";
# Forced use of Network Device group. This can be changed to included other groups if neccessary.
$sql = "select distinct h.host from hosts h join hosts_groups hg on h.hostid = hg.hostid where hg.groupid = '100100000000006' and h.host like '192.%.%.%' or h.host like '10.%.%.%'";
$sth = $dbh->prepare($sql);
$sth->execute or die "SQL Error: $DBI::errstr\n";
$count = 0;
my $ipaddress = "";
while (@row = $sth->fetchrow_array) {
$ipaddress .= "@row,"; # join(", ", @row), "\n";
$count ++;
}
print "\nIP Addresses found in Zabbix Database: $count\n\n";
#chomp($ipaddress);
# Convert to list
my @a = ();
my $exclude = "";
@a =split(/\,/, $ipaddress);
#print "size of array: " . @a . ".\n";
open(FILE, ">DNS_out.csv") or die "Could not open file: $!";
# Print CSV Header
print FILE "Computer,IP\n";
#Loop through each IP attempting to retrieve the SNMP hostname
foreach my $ip (@a) {
#print FILE system("snmpget -v2c -cpublic -M /usr/share/mibs ".$ip." iso.3.6.1.2.1.1.5.0 | awk '{ print $4}'");
# Check if reverse lookup does actually exist
$exclude = "yes";
my $nslookupresult = "nslookup $ip | awk '{ print \$4 }' | sed '/^\$/d'";
my $nsres = `$nslookupresult`;
#Could not work if you have "find" in a devices name
if (index($nsres, 'find') != -1) {
$nsres = "Not Found!\n";
#Exclude from writing to file
$exclude = "no";
}
print "Nslookup result $ip : ".$nsres;
my $OID_sysName = '.1.3.6.1.2.1.1.5.0';
my ($session, $error) = Net::SNMP->session(
-hostname => shift || $ip,
-community => shift || 'public',
);
if (!defined $session) {
printf "ERROR: %s.\n\n", $error;
next;
#exit 1;
}
my $result = $session->get_request(-varbindlist => [ $OID_sysName ],);
if (!defined $result) {
printf "ERROR: %s.\n\n", $session->error();
next;
# $session->close();
# exit 1;
}
my $hostName = $result->{$OID_sysName};
$hostName =~ s/.domain.com//;
print "Retrieving $ip hostname : ".$hostName."\n\n";
$session->close();
if ($exclude eq "no") {
print FILE $hostName;
print FILE ",$ip\n";
}
}
DNS_Record_Creator.ps1
#By CSS ERW 14 Sept 2013
#This script create DNS A Record and associate Reverse PTR
#If Reverse Zone doesn't exist script create it
#Create records.csv file with Computer,IP information
#see example below add first line to your csv file
#
#Computer,IP
#Computer,192.168.0.1
#Computer1,192.168.0.2
#Computer2,192.168.0.3
#
#Change with your dns server info $Servername and $Domain
$ServerName = "w2K8-rodc"
$domain = "venus.com"
Import-Csv c:\DNS_out.csv | ForEach-Object {
#Def variable
$Computer = "$($_.Computer).$domain"
$addr = $_.IP -split "\."
$rzone = "$($addr[0]).in-addr.arpa"
#Create Dns entries
dnscmd $Servername /recordadd $domain "$($_.Computer)" /CreatePTR A "$($_.IP)"
#Create New Reverse Zone if zone already exist, system return a normal error
#dnscmd $Servername /zoneadd $rzone /primary
#Create reverse DNS
# dnscmd $Servername /recordadd $rzone "$($addr[3]).$($addr[2]).$($addr[1])." PTR $Computer