#!/bin/bash
 
# a script to check Zabbix database for inconsistencies and problems
# it is attempted to keep each actual check contained in sql only
# currently mysql specific - help to make this db agnostic appreciated
# bash specific - no plans to change that
 
# performs checks, then allows user to choose category to explain errors found
# other actions are planned, like fixing the problem (by dropping erroneous records, for example)
 
# current checks:
#    1. graph items that reference non-existent items;
#    2. items that reference non-existent host;
#    3. items without description;
#    4. triggers without function references;
#    5. actions with zero eventid;
#    6. functions that refer non-existent items;
#    7. duplicate host-group relationship records
 
DB=${1:-zabbix}
MYSQL="mysql -N"
#DBUSER=zabbix
# note ! passing password on the commandline is not safe
#DBPASSWORD=zabbix
 
echo "performing tests on database $DB"
IFS=" " # needed so that 'read' can read line containing tabs
 
[[ "$DBUSER" ]] && USERNAME="-u $DBUSER"
[[ "$DBPASSWORD" ]] && PASSWORD="-p$DBPASSWORD"
 
# -------------------------- checks
 
# *** 1
graphitems() {
    while read i; do
        ((GRAPHBADITEMS++))
        GRAPHITEM_gitemid[$GRAPHBADITEMS]=$(echo $i | awk -F"\t" '{print $1}')
        GRAPHITEM_graphid[$GRAPHBADITEMS]=$(echo $i | awk -F"\t" '{print $2}')
        GRAPHITEM_itemid[$GRAPHBADITEMS]=$(echo $i | awk -F"\t" '{print $3}')
    done < <(echo "select gitemid,graphid,graphs_items.itemid from graphs_items\
 left join items on graphs_items.itemid=items.itemid where items.itemid is null;" | $MYSQL $USERNAME $PASSWORD $DB)
}
 
# *** 2
itemswithouthost() {
    while read i; do
        ((ITEMSWITHOUTHOST++))
        ITEMWITHOUTHOST_itemid[$ITEMSWITHOUTHOST]=$(echo $i | awk -F"\t" '{print $1}')
        ITEMWITHOUTHOST_key_[$ITEMSWITHOUTHOST]=$(echo $i | awk -F"\t" '{print $2}')
        ITEMWITHOUTHOST_hostid[$ITEMSWITHOUTHOST]=$(echo $i | awk -F"\t" '{print $3}')
        ITEMWITHOUTHOST_host[$ITEMSWITHOUTHOST]=$(echo $i | awk -F"\t" '{print $4}')
    done < <(echo "select itemid,key_,items.hostid,host from items left join hosts on items.hostid=hosts.hostid\
 where hosts.hostid is null;" | $MYSQL $USERNAME $PASSWORD $DB)
}
 
# *** 3
itemswithoutdescription() {
    while read i; do
        ((ITEMSWITHOUTDESCR++))
        ITEMWITHOUTDESCR_itemid[$ITEMSWITHOUTDESCR]=$(echo $i | awk -F"\t" '{print $1}')
        ITEMWITHOUTDESCR_key_[$ITEMSWITHOUTDESCR]=$(echo $i | awk -F"\t" '{print $2}')
        ITEMWITHOUTDESCR_hostid[$ITEMSWITHOUTDESCR]=$(echo $i | awk -F"\t" '{print $3}')
        ITEMWITHOUTDESCR_host[$ITEMSWITHOUTDESCR]=$(echo $i | awk -F"\t" '{print $4}')
    done < <(echo "select itemid,key_,items.hostid,hosts.host from items left join hosts on items.hostid=hosts.hostid\
 where description='';" | $MYSQL $USERNAME $PASSWORD $DB)
}
 
# *** 4
triggerswithoutfunctionreference() {
    while read i; do
        ((TRIGGERSWOFUNCREF++))
        TRIGGERWOFUNCREF_triggerid[$TRIGGERSWOFUNCREF]=$(echo $i | awk -F"\t" '{print $1}')
        TRIGGERWOFUNCREF_expression[$TRIGGERSWOFUNCREF]=$(echo $i | awk -F"\t" '{print $2}')
        TRIGGERWOFUNCREF_description[$TRIGGERSWOFUNCREF]=$(echo $i | awk -F"\t" '{print $3}')
    done < <(echo "select triggerid,expression,description from triggers where expression\
 not regexp '.*{[0-9]*}.*';" | $MYSQL $USERNAME $PASSWORD $DB)
}
 
# *** 5
actionswithzeroeventid() {
    while read i; do
        ((ACTIONSWITHZEROID++))
        ACTIONWITHZEROID_alertid[$ACTIONSWITHZEROID]=$(echo $i | awk -F"\t" '{print $1}')
        ACTIONWITHZEROID_alias[$ACTIONSWITHZEROID]=$(echo $i | awk -F"\t" '{print $2}')
        ACTIONWITHZEROID_sendto[$ACTIONSWITHZEROID]=$(echo $i | awk -F"\t" '{print $3}')
    done < <(echo "select alertid,alias,sendto from alerts left join users on alerts.userid=users.userid\
 where eventid=0;" | $MYSQL $USERNAME $PASSWORD $DB)
}
 
# *** 6
functionswithbaditems() {
    while read i; do
        ((FUNCTIONSWITHBADITEMS++))
        FUNCTIONWITHBADITEMS_functionid[$FUNCTIONSWITHBADITEMS]=$(echo $i | awk -F"\t" '{print $1}')
        FUNCTIONWITHBADITEMS_itemid[$FUNCTIONSWITHBADITEMS]=$(echo $i | awk -F"\t" '{print $2}')
        FUNCTIONWITHBADITEMS_trigger_description[$FUNCTIONSWITHBADITEMS]=$(echo $i | awk -F"\t" '{print $3}')
    done < <(echo "select functionid,functions.itemid,triggers.description from functions left join\
 items on functions.itemid=items.itemid left join triggers on functions.triggerid=triggers.triggerid\
 where items.itemid is null;" | $MYSQL $USERNAME $PASSWORD $DB)
}
 
# *** 7
 
dupehostgroups() {
    while read i; do
        ((DUPEHOSTGROUPS++))
        DUPEHOSTGROUPS_amount[$DUPEHOSTGROUPS]=$(echo $i | awk -F"\t" '{print $1}')
        DUPEHOSTGROUPS_hostid[$DUPEHOSTGROUPS]=$(echo $i | awk -F"\t" '{print $2}')
        DUPEHOSTGROUPS_groupid[$DUPEHOSTGROUPS]=$(echo $i | awk -F"\t" '{print $3}')
    done < <(echo "SELECT COUNT(*) as qty,hostid,groupid FROM hosts_groups GROUP BY hostid,groupid\
 HAVING qty > 1;" | $MYSQL $USERNAME $PASSWORD $DB)
}
 
# -------------------------- check explanations
 
explaingraphitems() {
    echo "this check finds graph items that reference non-existent items. graph items found :"
    ( echo -e "gitemid\tgraphid\titemid"
    for ((count=1;count<=$GRAPHBADITEMS;count++)); do
        echo -e "${GRAPHITEM_gitemid[$count]}\t${GRAPHITEM_graphid[$count]}\t${GRAPHITEM_itemid[$count]}"
    done ) | column -t -s$'\t'
}
 
explainitemswithouthost() {
    echo "this check finds items that reference non-existent hosts. items found :"
    ( echo -e "itemid\tkey_\thostid\thost"
    for ((count=1;count<=$ITEMSWITHOUTHOST;count++)); do
        echo -e "${ITEMWITHOUTHOST_itemid[$count]}\t${ITEMWITHOUTHOST_key_[$count]}\t${ITEMWITHOUTHOST_hostid[$count]}\t${ITEMWITHOUTHOST_host[$count]}"
    done ) | column -t -s$'\t'
}
 
explainitemswithoutdescription() {
    echo "this check finds items with empty description. items found :"
    ( echo -e "itemid\tkey_\thostid\thost"
    for ((count=1;count<=$ITEMSWITHOUTDESCR;count++)); do
        echo -e "${ITEMWITHOUTDESCR_itemid[$count]}\t${ITEMWITHOUTDESCR_key_[$count]}\t${ITEMWITHOUTDESCR_hostid[$count]}\t${ITEMWITHOUTDESCR_host[$count]}"
    done ) | column -t -s$'\t'
}
 
explaintriggerswithoutfunctionreference() {
    echo "this check finds triggers that do not reference any function. triggers found :"
    ( echo -e "triggerid\texpression\tdescription"
    for ((count=1;count<=$TRIGGERSWOFUNCREF;count++)); do
        echo -e "${TRIGGERWOFUNCREF_triggerid[$count]}\t${TRIGGERWOFUNCREF_expression[$count]}\t${TRIGGERWOFUNCREF_description[$count]}"
    done ) | column -t -s$'\t'
}
 
explainactionswithzeroeventid() {
    echo "this check finds actions (alerts, notifications) that have zero eventid. such entries could occur if migration has happened from version 1.4 to 1.6 before 1.6.3 or .4. actions like that do not appear in the frontend. actions found :"
    ( echo -e "alertid\talias\tsendto"
    for ((count=1;count<=$ACTIONSWITHZEROID;count++)); do
        echo -e "${ACTIONWITHZEROID_alertid[$count]}\t${ACTIONWITHZEROID_alias[$count]}\t${ACTIONWITHZEROID_sendto[$count]}"
    done ) | column -t -s$'\t'
}
 
explainfunctionswithbaditems() {
    echo "this check finds functions that reference non-existent items. functions found :"
    ( echo -e "functionid\titemid\ttrigger_description"
    for ((count=1;count<=$FUNCTIONSWITHBADITEMS;count++)); do
        echo -e\
 "${FUNCTIONWITHBADITEMS_functionid[$count]}\t${FUNCTIONWITHBADITEMS_itemid[$count]}\t${FUNCTIONWITHBADITEMS_trigger_description[$count]}"
    done ) | column -t -s$'\t'
}
 
explaindupehostgroups() {
    echo "this check finds duplicate entries for host-group linking. duplicates found :"
    ( echo -e "amount\tgroupid\thostid"
    for ((count=1;count<=$DUPEHOSTGROUPS;count++)); do
        echo -e\
 "${DUPEHOSTGROUPS_amount[$count]}\t${DUPEHOSTGROUPS_groupid[$count]}\t${DUPEHOSTGROUPS_hostid[$count]}"
    done ) | column -t -s$'\t'
}
 
# --------------------------
 
finaloutput() {
    echo "----------------------------------------"
    echo "problems in $PROBLEMCATEGORIES categories found:"
    echo -e $INFOSTRING
    echo "enter category number for actions for that category, 'a' for all categories and 'q' to quit"
}
#
for i in graphitems itemswithouthost itemswithoutdescription triggerswithoutfunctionreference\
 actionswithzeroeventid functionswithbaditems dupehostgroups; do
    echo -n "running check $i... "
    $i
done
echo
 
[[ "$GRAPHBADITEMS" ]] && {
    ((PROBLEMCATEGORIES++))
    PROBLEMCATEGORY[$PROBLEMCATEGORIES]=graphitems
    INFOSTRING="$INFOSTRING\n${PROBLEMCATEGORIES}. $GRAPHBADITEMS bad graph items found"
}
 
[[ "$ITEMSWITHOUTHOST" ]] && {
    ((PROBLEMCATEGORIES++))
    PROBLEMCATEGORY[$PROBLEMCATEGORIES]=itemswithouthost
    INFOSTRING="$INFOSTRING\n${PROBLEMCATEGORIES}. $ITEMSWITHOUTHOST items without host found"
}
 
[[ "$ITEMSWITHOUTDESCR" ]] && {
    ((PROBLEMCATEGORIES++))
    PROBLEMCATEGORY[$PROBLEMCATEGORIES]=itemswithoutdescription
    INFOSTRING="$INFOSTRING\n${PROBLEMCATEGORIES}. $ITEMSWITHOUTDESCR items without description found"
}
 
[[ "$TRIGGERSWOFUNCREF" ]] && {
    ((PROBLEMCATEGORIES++))
    PROBLEMCATEGORY[$PROBLEMCATEGORIES]=triggerswithoutfunctionreference
    INFOSTRING="$INFOSTRING\n${PROBLEMCATEGORIES}. $TRIGGERSWOFUNCREF triggers that do not reference any function found"
}
 
[[ "$ACTIONSWITHZEROID" ]] && {
    ((PROBLEMCATEGORIES++))
    PROBLEMCATEGORY[$PROBLEMCATEGORIES]=actionswithzeroeventid
    INFOSTRING="$INFOSTRING\n${PROBLEMCATEGORIES}. $ACTIONSWITHZEROID actions with zero eventid found"
}
 
[[ "$FUNCTIONSWITHBADITEMS" ]] && {
    ((PROBLEMCATEGORIES++))
    PROBLEMCATEGORY[$PROBLEMCATEGORIES]=functionswithbaditems
    INFOSTRING="$INFOSTRING\n${PROBLEMCATEGORIES}. $FUNCTIONSWITHBADITEMS functions that reference non-existent items found"
}
 
[[ "$DUPEHOSTGROUPS" ]] && {
    ((PROBLEMCATEGORIES++))
    PROBLEMCATEGORY[$PROBLEMCATEGORIES]=dupehostgroups
    INFOSTRING="$INFOSTRING\n${PROBLEMCATEGORIES}. $DUPEHOSTGROUPS duplicate host-group links found"
}
 
 
chooseaction() {
    read -p "choose action ('e' for explain): " ACTION
    echo $ACTION
}
 
performaction() {
    case $1 in
        e)
            echo "----- explaining ${PROBLEMCATEGORY[$INPUT]} category -----"
            explain${PROBLEMCATEGORY[$INPUT]}
            echo
            ;;
        *)
            echo "no such action"
            performaction $(chooseaction)
            ;;
    esac
}
 
[[ "$INFOSTRING" ]] && {
    while true; do
        finaloutput
        read INPUT
        case $INPUT in
            [0-9]*)
                if [ "${PROBLEMCATEGORY[$INPUT]}" == "" ]; then
                    echo "no such category"
                else
                    performaction $(chooseaction)
                fi
                ;;
            a)
                ACTION=$(chooseaction)
                for ((i=1;i<=$PROBLEMCATEGORIES;i++)); do
                    INPUT=$i
                    performaction $ACTION
                done
                ;;
            q)
                exit
                ;;
            *)
                echo "invalid input"
                ;;
        esac
    done
}