LibreNMS Error Running ./validate.php – Database: incorrect column…

LibreNMS error while running ./validate.php

Recently I started getting the following error while running ./validate.php.

$ sudo -u librenms ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS  | 21.8.0-41-g0a76ca4
DB Schema | 2021_08_26_093522_config_value_to_medium_text (217)
PHP       | 7.4.20
Python    | 3.6.8
MySQL     | 10.5.10-MariaDB
RRDTool   | 1.4.8
SNMP      | NET-SNMP 5.7.2
====================================

[OK]    Composer Version: 2.1.6
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  Database: incorrect column (notifications/datetime)
[FAIL]  Database: incorrect column (users/created_at)
[FAIL]  We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
        [FIX]:
        Run the following SQL statements to fix.
        SQL Statements:
         SET TIME_ZONE='+00:00';
         ALTER TABLE `notifications` CHANGE `datetime` `datetime` timestamp NOT NULL DEFAULT '1970-01-02 00:00:00' ;
         ALTER TABLE `users` CHANGE `created_at` `created_at` timestamp NOT NULL DEFAULT '1970-01-02 00:00:01' ;

Reading online it sounds like some of the recent changes are causing the issue. Looks fairly easy to resolve though.

First we’ll need to get a MySQL prompt. We’ll do that by running

mysql -u librenms -p librenms

It’ll ask use for the librenms user’s mysql password.

Once we have the MySQL prompt we can just copy and paste the commands in.

SET TIME_ZONE='+00:00';
ALTER TABLE `notifications` CHANGE `datetime` `datetime` timestamp NOT NULL DEFAULT '1970-01-02 00:00:00' ;
ALTER TABLE `users` CHANGE `created_at` `created_at` timestamp NOT NULL DEFAULT '1970-01-02 00:00:01' ;

Type ‘quit’ to exit MySQL and lets run the validate script again.

sudo -u librenms ./validate.php

Everything should check out OK.

LibreNMS update to Python 3

You may get the following alert in LibreNMS. Basically you need to install python 3 to keep things up to date.

Python 3 is required to run LibreNMS as of May, 2020. You need to install Python 3 to continue to receive updates. If you do not install Python 3 and required packages, LibreNMS will continue to function but stop receiving bug fixes and updates.

Install Python 3

Install Python 3 with yum, or apt if you are on a Debian based distro.

sudo yum install python3
sudo pip3 install -r /opt/librenms/requirements.txt

Verify LibreNMS is updated and working

Run the following commands to make sure that LibreNMS is working correctly and is up to date.

cd /opt/librenms
sudo ./validate.php
sudo ./daily.sh

LibreNMS – Daily.sh failed

All the following commands are run from the “/opt/librenms” directory

$ sudo ./daily.sh 
Re-running /opt/librenms/daily.sh as librenms user
Updating to latest codebase                        FAIL
error: Your local changes to the following files would be overwritten by merge:
        html/js/lang/de.js
        html/js/lang/en.js
Please, commit your changes or stash them before you can merge.
...
$

Checking with validate.php shows that you can run githup-remove to fix it

$ sudo ./validate.php
[WARN]  Your install is over 24 hours out of date, last update: Mon, 23 Feb 2020 05:43:12 +0000
         [FIX]: 
         Make sure your daily.sh cron is running and run ./daily.sh by hand to see if there are any errors.
 [WARN]  Your local git contains modified files, this could prevent automatic updates.
         [FIX]: 
         You can fix this with ./scripts/github-remove
         Modified Files:
          html/js/lang/de.js
...
$

Run “sudo ./scripts/github-remove -d”

$ sudo ./scripts/github-remove -d
Are you sure you want to delete all modified and untracked files? [y/N] y
$

You should also be able to remove the files individually if the above command does not work.

Run validate again to make sure it checks out good.

Now run ./daily.sh

LibreNMS – Setting up Alerts to monitor Voltage on Mikrotik Routers

Create Alert Rule

Go to “Alerts -> Alert Rules” Then “Create rule from Collection”

Search for “Sensor under limit” and Select

Setup Sensor under limit

Configure Alert Rule

Add another rule to limit to just the Voltage Sensors by using the “sensors.sensor_class = Voltage”

Select your Groups and Transports and Save.

Modify Alert Rule

Disable Alerts for Unused Ports

You may get some alerts because there are some ports that do not have any any voltage on them. You can disable them on a per device basis by going to the “device -> Edit -> Health” and turning alerts off

Turn off voltage alerts on port that has no PoE

UNMS – Set user as Super Admin from command line

The following commands can be run from a ssh session to your UNMS instance.

Show Current Users

sudo ~unms/app/unms-cli set-superadmin

Should get something like the following

            UNMS Users
 ┌─────────┬───────────┬───────────────────────────┬──────────────┬────────────┐
 │ (index) │ username  │           email           │     role     │ two-factor │
 ├─────────┼───────────┼───────────────────────────┼──────────────┼────────────┤
 │    0    │  'larry'  │  'larry@incredigeek.com'  │   'admin'    │   false    │
 │    1    │   'bob'   │   'bob@incredigeek.com'   │   'admin'    │   false    │
 └─────────┴───────────┴───────────────────────────┴──────────────┴────────────┘

Change User to Super Admin

To change a user to a Super Admin, add “–username username” to the above command.

Example:

sudo ~unms/app/unms-cli set-superadmin --username bob
            UNMS Users
 ┌─────────┬───────────┬───────────────────────────┬──────────────┬────────────┐
 │ (index) │ username  │           email           │     role     │ two-factor │
 ├─────────┼───────────┼───────────────────────────┼──────────────┼────────────┤
 │    0    │  'larry'  │  'larry@incredigeek.com'  │   'admin'    │   false    │
 │    1    │   'bob'   │   'bob@incredigeek.com'   │ 'superadmin' │   false    │
 └─────────┴───────────┴───────────────────────────┴──────────────┴────────────┘

Note that there does not appear to be a way to

Raspberry Pi – Ping IP Address and Toggle LED

The following script is for monitoring if an IP address is reachable or not. If it becomes unavailable the script will turn on a LED that is plugged into one of the GPIO pins of the Raspberry Pi. View pinout here

Script

#!/bin/bash
# Script to ping ip address and turn on LED on if device is unreachable.
                                                                                                                                                                                                 nPin="18"  # Change if GPIO pin is different                                                                                                     
ledPin="gpio${nPin}"                                                                                                                                                                                                                            toPing="8.8.8.8"  # Change to address you want to ping

echo "${nPin}" > /sys/class/gpio/export
echo "out" > /sys/class/gpio/${ledPin}/direction

if ( fping -r1 $toPing | grep -v alive ); then
         echo "Internet unreachable"
         # Turn on LED
         echo "1" > /sys/class/gpio/${ledPin}/value
 else
         # Turn off LED 
         echo "0" > /sys/class/gpio/${ledPin}/value
 fi

Save script as ping_led.sh and make it executable.

chmod +x ping_led.sh

and run the script.

sh ping_led.sh

Run script in crontab

You can setup the script to run every minute using a crontab

crontab -e

Add the following line

*/1 * * * * /home/pi/ping_led.sh

Should now execute the script every minute and not need any human interaction.

LibreNMS manually adjust GPS coordinates for a Geo Location

The locations are stored in the LibreNMS locations table

mysql -u librenms -p librenms

To list all the locations

select * from locations;

Set the specific coordinates for a location using the following command.
Replace the lat and lng, also the id should match the line your wanting to update.

update locations set lat = "33.5140",lng = "-86.8178" where id=25;

LibreNMS dump devices in MySQL database

All the devices are stored in the librenms datebase devices table.

You can dump all of them with the following command from a shell prompt.

sudo mysql -u librenms  -p librenms -e "select hostname,sysName from devices"

Example output

[jim@localhost ~]$ sudo mysql -p librenms -e "select hostname,sysName from devices"
 Enter password:
 +----------------------------------------+--------------------------------+
 | hostname                               | sysName                        |
 +----------------------------------------+--------------------------------+
 | 192.168.1.1                            | linksys                        |
 | 192.168.1.9                            | wifi-unifi                     |
 +----------------------------------------+--------------------------------+