A frequent request I get is how to view the availability of hosts and/or services within a Grafana dashboard. Here I demonstrate how to do this for host availability.
To implement this, we first create a new table in our MariaDB on NetEye. I’ll call this table host_daily_sla and put it in the pre-existing reporting DB. You can decide to call it something else if you like and put it in its own DB at your discretion. Now for the next step we create an Icinga 2 report for host availability:
We need a Host Availability Report for the last day, and we have to schedule this report every day and save it as a CSV file in a directory on the NetEye Server to be able to process it with a script and load the data into our table we created above. I did that using NetEye Eventhandler and sent the Report to eventgw (the NetEye host will know what to do with this local user) and created an Email Rule which saves the attachment to /var/spool/neteye.
Now to our script. Here you only need to change the DB Parameters to adapt it to your system:
! /bin/sh
#
MYSQL Params
DBHOST=mariadb.neteyelocal
DB=reporting
DBUSER=myrouser
DBPASS=myropass
MYSQLCMD="mysql -BN -h $DBHOST -u $DBUSER -p$DBPASS $DB"
for i in $(ls -1 /var/spool/neteye/tmp/icinga_reporting_*.csv)
do
FILE="$i"
while read ll
do
if echo "$ll" | grep 'SLA in' >/dev/null
then
continue
fi
host=$(echo "$ll" | cut -d, -f1)
DATE=$(echo "$ll" | cut -d, -f2)
sla=$(echo "$ll" | cut -d, -f3)
if [ -z "$sla" -o "$sla" = "$DATE" ]
then
echo "ERROR wrong file format!"
exit 1
fi
echo "INSERT INTO host_daily_sla (sladate,hostname,sla) VALUES ('$DATE','$host','$sla') ON DUPLICATE KEY UPDATE sla='$sla'" | $MYSQLCMD
done < "$FILE"
if [ -n "$ll" ]
then
host=$(echo "$ll" | cut -d, -f1)
DATE=$(echo "$ll" | cut -d, -f2)
sla=$(echo "$ll" | cut -d, -f3)
if [ -z "$sla" -o "$sla" = "$DATE" ]
then
echo "ERROR wrong file format!"
exit 1
fi
echo "INSERT INTO host_daily_sla (sladate,hostname,sla) VALUES ('$DATE','$host','$sla') ON DUPLICATE KEY UPDATE sla='$sla'" | $MYSQLCMD
fi
rm -f "$FILE"
done
You can also see that I save the Icinga 2 report with the prefix icinga_reporting_ to be able to load the right report, but you’ll need to take care of that yourself.
So now we have the data in the MariaDB table structured as 1 record x day x host. We can now make a new Datasource in Grafana to access that table:
Next is the Dashboard. Here you can just create a table element and then use this query in it:
SELECT
hostname,
sum(sla)/count(*) as SLA
FROM host_daily_sla
WHERE
hostname like $hostname AND
sladate >= $__timeFrom() AND sladate <= $__timeTo() GROUP BY hostname order by hostname
After that you can also add a selection variable in Grafana for your host from the same DB using this query:
I have over 20 years of experience in the IT branch. After first experiences in the field of software development for public transport companies, I finally decided to join the young and growing team of Würth Phoenix. Initially, I was responsible for the internal Linux/Unix infrastructure and the management of CVS software. Afterwards, my main challenge was to establish the meanwhile well-known IT System Management Solution WÜRTHPHOENIX NetEye. As a Product Manager I started building NetEye from scratch, analyzing existing open source models, extending and finally joining them into one single powerful solution. After that, my job turned into a passion: Constant developments, customer installations and support became a matter of personal. Today I use my knowledge as a NetEye Senior Consultant as well as NetEye Solution Architect at Würth Phoenix.
Author
Juergen Vigna
I have over 20 years of experience in the IT branch. After first experiences in the field of software development for public transport companies, I finally decided to join the young and growing team of Würth Phoenix. Initially, I was responsible for the internal Linux/Unix infrastructure and the management of CVS software. Afterwards, my main challenge was to establish the meanwhile well-known IT System Management Solution WÜRTHPHOENIX NetEye. As a Product Manager I started building NetEye from scratch, analyzing existing open source models, extending and finally joining them into one single powerful solution. After that, my job turned into a passion: Constant developments, customer installations and support became a matter of personal. Today I use my knowledge as a NetEye Senior Consultant as well as NetEye Solution Architect at Würth Phoenix.
As you may have noticed from recent posts from some of my colleagues about "Boosting NetEye CI Speed: Test Parallelization" or "Speeding up the NetEye CI Testing Phase", we are improving our CI in order to have a reliable, fast Read More
Icinga Director is a powerful configuration tool included in NetEye that streamlines the process of defining and managing monitoring configurations. However, you may encounter issues with duplicate addresses and spaces in hostnames, which can lead to confusion and monitoring inaccuracies. Read More
Hostgroups are a grouping of hosts with similar characteristics such as geographical location, type, severity, environment, operating system, applications and much more. Hostgroups can be created for multiple purposes such as: Grouping hosts with common characteristics to view them as Read More
Scenario NetEye 4 is a comprehensive monitoring platform which natively supports Business Processes. A Business Process is an abstract view of a customer’s Business from the Application point of view. Usually, it’s a collection of Icinga2 checks aggregated by “AND, Read More
Scenario NetEye 4 is a comprehensive monitoring platform which natively supports Icinga 2 checks on remote hosts and devices. Several Icinga 2 checks support an historical view of the status. An example is the firewall interface performance status below, which Read More