Script to send SMS via Goip4 gateway for ABillS

I will give an example of a PHP script to which I send SMS messages through the GoIP4 gateway.

First, we choose tariffs with only monthly charges (I chose everything, excluding with daily charges, since there are fewer of them):

SELECT name, tp_id FROM tarif_plans;
SELECT name, tp_id FROM tarif_plans WHERE tp_id NOT IN(1,2,4,8,9,10,39,63,64,65,106,107,109,110,114,117,118,119)

Continue reading Script to send SMS via Goip4 gateway for ABillS

How to pull data from one column of a mysql table

To see data from one column, it is enough to execute the SQL query (where “abcd” is the name of the column in the table):

SELECT abcd FROM table;

To export to a file, just run the command in Linux:

mysql -u root -e "SELECT abcd FROM database;" -s -N > file.txt

Here is an example of exporting email addresses from a mysql table to an http page using PHP.
The thought immediately came to this plan (create a php file and open it through the browser):

<?php
// Connecting to mysql server
mysql_connect("localhost", "USER", "PASSWORD") or die (mysql_error ());
// Choosing a database
mysql_select_db("users") or die(mysql_error());
// SQL query
$rows = "SELECT * FROM account";
// Run this SQL query
$d = mysql_query($rows);
// Each row becomes an array ($row) using the mysql_fetch_array
while($row = mysql_fetch_array($d)) {
// Display the values of the email column
echo $row['email'] . "<br />";
}
// Close the connection to the database
mysql_close();
?>

Back Up Cisco Catalyst 6500 Configuration

For the test, I sketched a Cisco Catalyst 6509-E automatic backup configuration script.

Actually the script itself:

#!/bin/bash
# Backup CISCO config
(
sleep 5
echo "user"
sleep 4
echo "password"
sleep 4
echo "copy running-config tftp:"
sleep 2
echo "192.168.1.4"
sleep 2
echo "cisco.cfg"
sleep 6

echo "exit"
) | telnet 192.168.1.5
mv /srv/tftp/cisco.cfg /backups/devices/cisco/`date +%Y-%m-%d`_cisco.cfg

find /backups/devices/cisco/ -type f -mtime +30 -exec rm {} \;

Add the contents of the script, for example, to the backup_cisco.sh file and add it to cron, adding the following line to the /etc/crontab file:

0 2 * * * root /backups/scripts/backup_cisco.sh > /dev/null 2>&1

The file can be opened for example in the text editor nano (Ctrl+X to exit, y/n to save or cancel changes):

sudo nano /etc/crontab

The script connects via telnet to 192.168.1.5 and copies the configuration to the tftp server 192.168.1.4, then the file is moved to a convenient directory for storage.
The last line in the script deletes files older than 30 days.
How to start the tftp server, see my articles: Installing and Configuring a TFTP Server in Ubuntu or Starting a TFTP server in Windows.
See also: Using and configuring CRON.

Script backup configuration DOCSIS ARRIS Cadant C3 CMTS

Actually, this is my script:

#!/bin/bash
# Backup DOCSIS CADANTS config
(
sleep 5
echo "user"
sleep 5
echo "password"
sleep 5
echo "enable"
sleep 2
echo "password"
sleep 2
echo "copy startup-configuration tftp://192.168.0.1/cadant1.xml"
sleep 5
echo "exit"
) | telnet 192.168.0.50
mv /srv/tftp/cadant1.xml /backups/devices/docsis/`date +%Y-%m-%d`_cadant1.xml

Where 192.168.0.50 – cadant, 192.168.0.1 – tftp server.

You can add the script to /etc/crontab for automatic execution (for example, every day at one in the morning):

0 1 * * * root /path/to/script/backup_cadants.sh > /dev/null 2>&1

How to convert a list of IP addresses to DNS names

In Linux, you can convert a list of IP addresses into DNS names, for example, by a simple script.

To do this, create an empty file with the extension .sh, make it executable and add the content to it:

#!/bin/sh
while read ip traf ; do
    name=`host $ip|awk '{print $NF}'`
    echo -e "$name\t$ip\t$traf"
done >name_ip_traf.lst <ip_traf.lst

Where ip_traf.lst is a file with a list of IP addresses that need to be converted to DNS names.

You can make it executable by the command:

chmod +rwx file.sh

Run the script in the directory where it is located by the command:

./file.sh

Or run by specifying the full path:

/dir/file.sh

After the startup, you must wait for a while or interrupt the execution by pressing CTRL+C.

Ip-up and ip-down scripts with ipset for Accel-ppp

I’ll give an example of the scripts I used before, in the allowip list IP addresses were added to which the Internet is allowed, and in denyip those were redirected to the http page with information about the negative deposit.

Continue reading Ip-up and ip-down scripts with ipset for Accel-ppp

The script for adding IP addresses from a file to ipset

It took one day to write a script to add to ipset all the IP for which the session was started on the access server, Abills billing was used, so I decided to take IP addresses from the MySQL billing table.

The first step is to create a test ipset:

ipset create test iphash

Continue reading The script for adding IP addresses from a file to ipset

The Asus router reboot script

On old router firmware Asus often noticed that the web interface is not fully open, not all the menus are displayed, so it can not be configured accordingly and can not be restarted if remote access is also available, since the reset button is not displayed.
So he took the Asus RT-N12E router, ran the Wireshark sniffer, opened the web interface of the router and pressed the reboot button.
In the intercepted packets, you could see that the Reboot.asp file is being rebooted in the root directory.

This resulted in a script for rebooting Asus routers:

#!/bin/sh
ROUTER_IP="192.168.1.1"
USERNAME="admin"
PASSWORD="admin"

# exit if router is down
ping -q -c 1 "$ROUTER_IP" > /dev/null || exit

curl --basic --user "$USERNAME:$PASSWORD" -A "Mozilla/4.73 [en] (X11; U; Linux 2.2.15 i686)" --refer "http://$ROUTER_IP" "$ROUTER_IP/Reboot.asp"

The contents of the script will be placed in a new file, for example, using the nano editor (“CTRL+X” to exit and “y” to save the changes):

nano file.sh

Let’s make it executable:

chmod 777 file.sh

After this, we execute:

./file.sh

You can also manually open the link http://192.168.1.1/Reboot.asp in the browser if you need to reboot the router once.

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

Script to check for an empty directory

Recently noticed on one of his backup scripts that he stopped working and just created an empty directory with the current date, so there was an idea to add to it a check on an empty directory with a notification sent to the email.

Example of a script for checking for an empty directory, taking into account hidden files:

if [ `ls -a /backups/dir/ | grep -v "^\.$" | grep -v "^\..$" | wc -l` = "0" ]; then echo "Empty dir"; else echo "Non empty dir"; fi

Example of checking the directory with the name of the current date and if it is empty – by sending an email:

if [ `ls -a /backups/`date +%Y-%m-%d`/ | grep -v "^\.$" | grep -v "^\..$" | wc -l` = "0" ]; then (echo "Subject:Empty dir"; echo "Empty dir";) | sendmail info@example.com; else echo "Non empty dir"; fi