Dirty hack to add values mappings in Zabbix

“I’ll be brief.” ©
Here is two things about script published in ZBXNEXT-1424, first it can help you to automate creation of large mappings (and it’s cool), second it will broke your DB (not so cool, maaan).
When you will try to add mapping in broken DB you will see something like this:

poorzabbix

The “Error in query [INSERT INTO valuemaps (name,valuemapid) VALUES (‘Test mapping’,’50’)] [Duplicate entry ’50’ for key ‘PRIMARY’]” mean, that in table valuemaps you already have entry with valuemapid = 50. Why it happened i tell later after we fix DB.

To fix DB, you need to update few entries in table ‘idx‘, first update nextid where table_name = valuemaps:

mysql> update ids set nextid = (select max(valuemaps.valuemapid)+1 from valuemaps) where table_name = 'valuemaps';
Query OK, 1 row affected (0.22 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Second update nextid for mappings:

mysql> update ids set nextid = (select max(mappings.mappingid)+1 from mappings) where table_name = 'mappings';
Query OK, 1 row affected (0.22 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Here it is!

This happened because script does not update table idx. May be it’s ok for zabbix 2.0 that mentioned in feature request, but it’s broke database for zabbix 2.2 and newer. Unfortunately zabbix prior version 3.0 does not have API or ability to import mappings , so that script still useful.

Here is fixed script, i hope author will not offended at me:

#!/usr/bin/perl
 
use warnings;
use strict;
 
my $usage = "$0 valueMapName number newvalue [number2 newvalue2 [...]]
E.g.: 
 $0 'Alarm Status' 1 ok 2 unknown 3 stale 4 problem
 $0 'Aliveness' 0 dead 1 alive
";
 
my $valueMapName = shift() || die "No new valuemap name";
my @mapList = @ARGV;
die "No mappings given. Usage: $usage\n" if scalar(@mapList) == 0;
 
 
my $isEvenNumber = scalar(@mapList) % 2 == 0;
die "Must give mapping->value pairs. Usage: $usage\n" if not $isEvenNumber;
my %mappings = @mapList;
 
my $newValueMapId = int(qx/mysql -N -s -e 'select nextid from zabbix.ids where field_name = "valuemapid"'/) ||
die("Can't fetch max valuemapid\nUsage: $usage\n");
$newValueMapId++;
my $newMappingId = int(qx/mysql -N -s -e 'select nextid from zabbix.ids where field_name = "mappingid"'/) ||
die("Can't fetch max mappingid\nUsage: $usage\n");
$newMappingId++;
 
eval {
 my $valueMapCmd = qq/mysql -e "insert into zabbix.valuemaps (valuemapid, name) values ('$newValueMapId', '$valueMapName');"/;
 print "$valueMapCmd\n";
 system $valueMapCmd;
 eval {
 for my $from (keys %mappings) {
 my $to = $mappings{$from};
 my $mappingCmd= qq/mysql -e "insert into zabbix.mappings (mappingid, valuemapid, value, newvalue) values ('$newMappingId', '$newValueMapId', '$from', '$to');"/;
 print "$mappingCmd\n";
 system $mappingCmd;
 $newMappingId++;
 }
 };
 if ($@) {
 die "something went wrong inserting into mappings $@";
 }
};
if ($@) {
 die "something went wrong inserting into valuemaps $@";
}
 
my $valueMapUpdCmd = qq/mysql -e 'update zabbix.ids set nextid = "$newValueMapId" where field_name = "valuemapid";'/;
print "$valueMapUpdCmd\n";
system $valueMapUpdCmd;
$newMappingId--;
my $mappingUpdCmd = qq/mysql -e 'update zabbix.ids set nextid = "$newMappingId" where field_name = "mappingid";'/;
print "$mappingUpdCmd\n";
system $mappingUpdCmd;