Install and configure a Grafana server

Install Grafana and the sqlite plugin

• Create a bare-bones nw server - no host-classes needed, just an IP address and host

• Add the Grafana repository to /etc/apt/sources.list.d/grafana.list:

deb https://packages.grafana.com/enterprise/deb stable main

• Grab the Grafana gpg key to allow installation from the repository:

apt install -y apt-transport-https software-properties-common wget gnupg
wget -q -O - https://packages.grafana.com/gpg.key
cat gpg.key | gpg --dearmor -o /etc/apt/trusted.gpg.d/grafana.gpg

• Install the Grafana package:

apt update && apt install grafana-enterprise

• Install sqlite3:

apt install sqlite3

• Install sqlite3 datasource plugin for grafana:

grafana-cli plugins install frser-sqlite-datasource

• (Re)start the service:

systemctl restart grafana-server

Create the database

• Create schema.sql:

CREATE TABLE stats
(
  ts integer not null,
  hostname text not null,
  metric text not null,
  value real not null,
  hosttype text not null
);

• Create grafana database:

sqlite3 /var/lib/grafana/data.db < schema.sql
chown grafana. /var/lib/grafana/data.db

Set up the stat-gathering

• Install snmpget:

apt install snmp

• Default snmp mibs seem to be broken out of the box; lots of errors from snmpget.
• I had to copy some files from a working box:

 scp nw-syd-monitor1:/usr/share/snmp/mibs/IANA-ADDRESS-FAMILY-NUMBERS-MIB.txt /usr/share/snmp/mibs/
 scp nw-syd-monitor1:/usr/share/snmp/mibs/RFC-1215.txt /usr/share/snmp/mibs/
 mv /usr/share/snmp/mibs/UCD-SNMP-MIB.txt /usr/share/snmp/mibs/UCD-SNMP-MIB-old.txt
 scp nw-syd-monitor1:/usr/share/snmp/mibs/UCD-SNMP-MIB.txt /usr/share/snmp/mibs/

- I don't know why, and I don't like it.

• Create script to populate the db - /usr/local/infrastructure/bin/getstats.sh (look in ~jbc/Projects/nw-k17-grafana)
• Or roll your own. to use snmpget and/or nagios-stat to gather load / usercount and populate the table.
• Hosttype column to distinguish different classes of hosts, to populate the different rows in grafana
• Prune stats older than 60 minutes at the end of the script
• Copy in nagios-stat script from acoustics:

scp acoustics:/import/acoustics/1/status/infoterm/nagios-stat /usr/local/infrastructure/bin

• The need for nagios-stat will go away once everything is new-world and we can just use snmp
• add /etc/cron.d/getstats

* * * * * grafana /usr/local/infrastructure/bin/getstats.sh 2>&1 > /dev/null

Configure the Grafana app

• http to port 3000 on the machine, and log in. Default password is admin:admin - you'll be prompted to change it immediately.
• Create a new dashboard
• In the Dashboard Settings (top right, in the new dashboard), create variables for each row you want to populate - following the hosttype values in the database.
• For instance, backendhosts => select distinct(hostname) from stats where hosttype="backend", ensure that Multi-value, All-option and Sort are all enabled.
• In the dropdown for each variable, select 'All'.
• Now create a new row, name it eg. Backend Hosts, and expand it.
• Create a new panel, and drag it into the row.
• Edit the panel from its little dropdown
• Set the panel type to 'Stat', set its repeat value to eg. backendhosts, and max per row = 12
• Blow away the existing query, and add eg.

SELECT ts, value
FROM stats
WHERE hostname='$backendhosts'
AND metric="load"

- note the different quotes for the two fields.

- you don't need to worry about the time range, as we're already pruning the whole db to the range we want.

• Pick the other options to make it look pretty - set background colour mode, create thresholds as appropriate, set Title to eg. $backendhosts
• Save and return to the dashboard, *save* the dashboard, then refresh the page. The row should auto-populate with a panel for each hosts.
• Drag them to a reasonable size.
• Rinse and repeat for the other rows - and a panel below for metric="user" for the rows that need a user-count. Leave the title blank for that one, to save space.
• Star the dashboard to make it a favourite
• Return to the 'home' dashboard, then go to 'preferences' (gear lower left => preferences), then set the 'home dashboard' to the dashboard you created.