SQL queries for Zabbix

I will write some useful examples of sql queries for the Zabbix database:

Search for a host by name:

SELECT * FROM hosts WHERE host like '%name%';
SELECT * FROM hosts WHERE name like '%name%';

Find the data items of the specified host:

SELECT * FROM items WHERE hostid = '10105';

Find the history of the values for the specified data item:

SELECT * FROM history WHERE itemid = '24526';

Delete the whole history of the data element until 01.11.2014 (the time is specified in Unix format, converters can be found through the search engine):

DELETE FROM history WHERE itemid = '24526' AND clock < '1414800000';

Delete all data history until 01.11.2014:

DELETE FROM history WHERE clock < '1414800000';

Massively changed the interval and dynamics of changes to the data elements of templates and hosts, I have the following queries (the first identifies the template ID or host, the second changes the intervals):

SELECT * FROM `hosts` WHERE host="Template ICMP Ping";
UPDATE items SET delay=3600 WHERE hostid=10105 AND delay=600;
UPDATE items SET trends=180 WHERE hostid=10047 AND trends=365;

Adding ONT PICOTEL PU-G510 on Huawei SmartAX MA5683T

Suppose that we set up Huawei SmartAX MA5683T and added lineprofile/srvprofile using this instruction: Configuring Huawei SmartAX MA5683T through the console

Next, add ONT similarly as I wrote in the article Alcatel-Lucent I-010G

On the test after adding ONT PICOTEL PU-G510 to OLT Huawei SmartAX MA5683T, when connecting the computer to ONT – the Internet worked, when connecting the router – no.
Therefore, I still had to open the ONT web interface, pointing in the address line http://192.168.1.1 (login and password – admin), before registering the computer with a static IP 192.168.1.2.
In the web interface, select the menu “Admin” – “GPON Settings”, where “OMCI OLT Mode:”, if using Huawei OLT – select “Huawei OLT Mode” and click “Apply Changes”.

See how I did it on video:

Etnernet ports are disabled on ONT Huawei EchoLife

I recently noticed a problem that, when ONT, the Huawei EchoLife HG8110, HG8120 and HG8120R watched the Ethernet ports shutdown about 30 seconds after the ONT was turned on.
At the same time, ONT continued to work, the optical connection did not disappear.
On the Huawei EchoLife HG8110 the link did not disappear, but the interface ONT 192.168.100.1 stopped pinging.

The cause was defective power supplies.

Adding ONT Huawei EchoLife HG8120 and HG8120R

On the test I will use ONT Huawei EchoLife HG8120 (works only in Bridge mode as a media converter) and Huawei EchoLife HG8120R (can work as Router & Bridge, with reset settings has no configuration, so it works the same as HG8120 in Bridge mode).
The standard IP for both models is 192.168.100.1, the login is telecomadmin, the password is admintelecom.
I will use VLAN 228 for the Internet.

Continue reading “Adding ONT Huawei EchoLife HG8120 and HG8120R”

Firmware Update TP-Link TL-WR841N

It was necessary to flash TP-Link TL-WR841N router, because it periodically hung up, disconnected from the Internet, the router itself was pinging at that moment, but it was impossible to enter its web interface.

First, check the hardware version of the device, usually it is indicated on the label under the router, in our case it’s V8. I note that updating the firmware not from that version of the device can spoil it! Also, you can not update the firmware on Wi-Fi and turn off the power of the router during the firmware!

And so, to update the firmware you need:
1) Download the archive with the new firmware from the official site and unpack it
https://www.tp-link.com/us/download/TL-WR841N.html

2) Open the router settings by typing in the browser address http://192.168.0.1 and enter your login – admin, password – admin.

3) In the menu choose System Tools -> Firmware Upgrade, click the “Browse” button, select the previously downloaded firmware. Click “Upgrade”. Wait for the firmware to be installed and the device will reboot automatically.

After updating the firmware, the settings in the router are reset to standard.

Done.

This router model also has unofficial firmware DD-WRT
http://www.dd-wrt.com/site/support/router-database

Logging activity using IPTables

Using iptables, you can write network activity to the log file, that is, which data is sent to and from where it comes from.
Example command:

sudo iptables -t filter -A FORWARD -s 192.168.1.0/24 -m tcp -p tcp --dport 80 -j LOG --log-prefix "iptables: "

That the information was written not in rsyslog a file, and separately, we will create a file:

sudo nano /etc/rsyslog.d/10-iptables.conf

And add the following to it:

:msg, contains, "iptables: " -/var/log/iptables.log
& ~

To apply the changes, restart rsyslog:

sudo /etc/init.d/rsyslog reload

Done, the network activity specified in the first rule will be written to the file /var/log/iptables.log.

It is also desirable to configure logrotate to remove old logs, to save disk space.

See also:
Configuring logrotate for log rotation
Configuring IPTables

Monitoring of Apache2 parameters in Zabbix

Install the necessary components:

sudo apt-get install curl

Activate the module information about apache2 (usually it is activated initially):

sudo a2enmod info

Open the configuration file of the module and specify the IP address of the zabbix server to allow it to view information about apache2 (if apache2 is on the local machine, then access is usually allowed by specifying local or 127.0.0.1):

sudo nano /etc/apache2/mods-enabled/status.conf

In the nano editor, CTRL+X is used to exit and y/n to save or discard changes. Example of specifying IP:

<Location /server-status>
    SetHandler server-status
    Require local
    Require ip 192.168.1.5
</Location>

Restart the web server to apply the changes:

sudo service apache2 restart

Now the information about apache2 is available through the browser by the link http://HOST/server-status

Let’s move on to Zabbix. Create a folder for scripts if it does not exist:

mkdir /etc/zabbix/scripts/
chown root:zabbix -R /etc/zabbix/scripts/
chmod 750 /etc/zabbix/scripts/

In the zabbix server configuration file, we specify the path to this folder:

sudo nano /etc/zabbix/zabbix_server.conf
ExternalScripts=PATH

Now I will give the content of the script:

#!/bin/bash
if [[ -z "$1" || -z "$2" || -z "$3" ]]; then
  exit 1
fi
##### PARAMETERS #####
RESERVED="$1"
METRIC="$2"
URL="$3"
STATSURL="${URL}?auto"
#
CACHE_TTL="55"
CACHE_FILE="/tmp/zabbix.apache2.`echo ${URL} | md5sum | cut -d" " -f1`.cache"
EXEC_TIMEOUT="2"
NOW_TIME=`date '+%s'`
##### RUN #####
if [ -s "${CACHE_FILE}" ]; then
  CACHE_TIME=`stat -c"%Y" "${CACHE_FILE}"`
else
  CACHE_TIME=0
fi
DELTA_TIME=$((${NOW_TIME} - ${CACHE_TIME}))
#
if [ ${DELTA_TIME} -lt ${EXEC_TIMEOUT} ]; then
  sleep $((${EXEC_TIMEOUT} - ${DELTA_TIME}))
elif [ ${DELTA_TIME} -gt ${CACHE_TTL} ]; then
  echo "" >> "${CACHE_FILE}" # !!!
  DATACACHE=`curl -sS --insecure --max-time ${EXEC_TIMEOUT} "${STATSURL}" 2>&1`
  echo "${DATACACHE}" > "${CACHE_FILE}" # !!!
  echo "URL=${URL}"  >> "${CACHE_FILE}" # !!!
  chmod 640 "${CACHE_FILE}"
fi
#
if [ "${METRIC}" = "accesses" ]; then
  cat "${CACHE_FILE}" | grep -i "accesses" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "kbytes" ]; then
  cat "${CACHE_FILE}" | grep -i "kbytes" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "cpuload" ]; then
  cat "${CACHE_FILE}" | grep -i "cpuload" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "uptime" ]; then
  cat "${CACHE_FILE}" | grep -i "uptime" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "avgreq" ]; then
  cat "${CACHE_FILE}" | grep -i "ReqPerSec" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "avgreqbytes" ]; then
  cat "${CACHE_FILE}" | grep -i "BytesPerReq" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "avgbytes" ]; then
  cat "${CACHE_FILE}" | grep -i "BytesPerSec" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "busyworkers" ]; then
  cat "${CACHE_FILE}" | grep -i "BusyWorkers" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "idleworkers" ]; then
  cat "${CACHE_FILE}" | grep -i "idleworkers" | cut -d':' -f2 | head -n1
fi
if [ "${METRIC}" = "totalslots" ]; then
  cat "${CACHE_FILE}" | grep -i "Scoreboard" | cut -d':' -f2 | sed -e 's/ //g' | wc -c | awk '{print $1-1}'
fi
#
exit 0

Let’s make the script file executable:

chown root:zabbix /etc/zabbix/scripts/apache2-status.sh
chmod 550 /etc/zabbix/scripts/apache2-status.sh

Script validation example:

sudo -u zabbix /etc/zabbix/scripts/apache2-status.sh none accesses http://HOST/server-status

Open the zabbix agent configuration file in the editor:

sudo nano /etc/zabbix/zabbix_agentd.conf

And we will specify the following parameters:

UserParameter=apache2[*],/etc/zabbix/scripts/apache2-status.sh "none" "$1" "$2"

Some commands allow you to see the number of apache2 processes and connections to port 80, for this you do not need a script, for example, you can specify:

UserParameter=apache2.count_processes,ps aux | grep apache | wc -l
UserParameter=connections_on_80_port,netstat -na | grep :80 | wc -l

Restart the zabbix agent to apply the changes:

sudo /etc/init.d/zabbix-agent restart

Let’s check:

zabbix_get -s 127.0.0.1 -k "apache2[accesses,http://HOST/server-status]"

Now we will create a template and add data elements, an example of created data items:

apache2[KEY,http://HOST/server-status]

Exported ready template – apache2-status

We also need to add a macro to the monitored host:

Macro: {$APACHE_STATS_URL}
Value: http://HOST/server-status

Done.

The number of apache2 processes can be obtained from the Zabbix agent by creating on the Zabbix server a data element with a key:

proc.num[apache2]

Executing a SQL query from a script in Linux

It was necessary recently to write a script that executes a sql query into the MySQL database.
Example content:

# Description, here I wrote for others that the script is added to the crontab, so that it is not moved
mysql -u USER -pPASSWORD -h 127.0.0.1 -e "UPDATE nika_system.abon SET otkl=0 WHERE depozit > '10' AND (otkl='-1' OR otkl='-2');";

In order for the script to run automatically, open the crontab file in any text editor (in the nano editor CTRL+X for the output and y/n for saving or canceling the changes):

sudo nano /etc/crontab

Add the following line to it:

0 9 * * * root /home/nika/scripts/reset_credit_nika.sh > /dev/null 2>&1

Now every day at 9 am the script will be executed.

Here is an example of daily automatic output of data from a sql table into a text file:

#!/bin/bash
mysql -u USER -pPASSWORD -h 192.168.1.1 -s -N -e "SELECT id FROM nika_system.abon WHERE tarif=109;";

In /etc/crontab we add:

0 8 * * * root /scripts/freektb.sh > /srv/samba/dir/mirazh/$(date +%Y-%m-%d).txt

For security reasons, it’s better not to specify the password in scripts, see my article – Connecting to MySQL from localhost without entering a password

Firmware Update D-Link DIR-815

For the test, I will update the firmware on the router D-Link DIR-815 revision A1.
The router has firmware 1.00 (Fri 06 Aug 2010), on official FTP found version 1.04, b03 (Wed May 15, 2013).

To update the firmware in the D-Link DIR-815 router, perform the following necessary steps:

1) Let’s see the revision on the label under the router and download the new firmware from the official FTP for it http://ftp.dlink.ru/pub/Router/DIR-815/Firmware/
Updating the firmware of the router for the wrong revision can lead to its failure.

2) Open the router settings by typing in the browser address http://192.168.0.1 (can be 192.168.1.1) and enter the default login — admin without password.

3) In the opened interface at the top, open the tab “Tools“, left in the menu select “Firmware“. On the opened page the current version of the firmware will be displayed, if it is older than downloaded, then click “Browse” and select the previously downloaded new firmware file, and then click “Upgrade” to start the update process.

We will wait for the update to be completed, usually about 5 minutes. After the termination the router itself will reboot.
Categorically, you can not turn off the power when the firmware is updated.