Automating Centralized backup for Cisco Switches

Another day to day troubleshooting short notes:

Disclaimer: There are many other proper techniques to achieve the centralized backup task, like Linux base scripting or FTP base route, But dueto my laziness I made this method according to the already available resources with minimum work required . 

---

Scenario:

Let’s assume:

Operator have dozen’s of manageable Cisco switches installed at various indoor/outdoor locations. Switches configuration are dynamic & the OP make changes on almost daily basis. For backup of switches configs, OP uses TFTP server on local windows server /& perform backup commands on all switches manually once a month. We want to automate this by having centralized backup server & all switches should run schedule backup task which should upload backup to the TFTP server automatically on daily or weekly basis.

---

Solution: [for personnel referenec]

1- Install TFTP Server in Windows to receive backup files from devices

Download & install Solarwind free TFTP Server application. Its quite and simple application which can receive backups from your switches/routers/devices and store them in c:\tftp-root folder (default location, you can change it)

At Cisco switch issue following command to test if TFTP is working fine. Your switch must be able to ping/communicate with the tftp server. Login tot switch via terminal or ssh and issue this command to test the TFTP connectivity from switch to tftp server …

en
show running-config | redirect tftp://10.0.0.1/cisco_sw/sw_10.0.0.20.txt

Note: I create new folder name cisco_sw so that all switches configurations should store in a separate folder for easy identification.

---

2 – Add Backup Schedule Job / Task on Cisco Switches

Network Details:

1. TFTP SERVER IN WINDOWS IP : 10.0.0.1
2. SWITCH IP : 10.0.0.20

Use following commands to add schedule task to run backup daily in midnight

en
conf t
kron policy-list daily-backup
cli show startup-config | redirect tftp://10.0.0.10/cisco_sw/sw_10.0.0.20.txt
exit
kron occurrence daily-backup at 00:00 recurring
policy-list daily-backup
exit
wr

 

• Verify the kron configuration by using the show command.

show kron schedule

• Result:

Kron Occurrence Schedule
daily-backup inactive, will run again in 0 days 12:58:04 at 0 :00 on

---

End Result:

---

Tips:

(for specific day and time, use following:

kron occurrence daily-backup at 23:00 Sun recurring)

To run task every 2 minute

kron occurrence daily-backup in 2 recurring
policy-list daily-backup

---

Copy to TFTP manually / Short Version:

copy running-config tftp

---

FTP Base backup

At one network, we configured FILEZILLA FTP Server, to make switch upload to FTP, we used following

#JZ # Make sure to change "MYSWITCHNAME_OR_IP.txt" & path to match your switch ip or name / ftp folder

en
conf term
ip ftp username cisco
ip ftp password View*pak
end

en
conf t
kron policy-list daily-backup
cli show startup-config | redirect ftp://192.168.0.1/cisco/MYSWITCHNAME_OR_IP.txt
exit
kron occurrence daily-backup at 00:00 recurring
policy-list daily-backup
exit
do wr

---

Regard’s
~Syed Jahanzaib

Error: (30)Read-only file system: apache2

---

Problem:

OS Ubuntu 12.4 x86 / Server Edition

Getting error when restarting apache2 service ..

root@radius:~# service apache2 restart
* Restarting web server apache2 (30)Read-only file system: apache2: could not open error log file /var/log/apache2/error.log.
Unable to open logs
Action 'start' failed.
[fail]
The Apache error log may have more information.

---

Reason:

Could be many depend on related situation. Usually Linux puts your file system in read only when errors occur, especially errors with the disk or the file-system itself, errors like a wrong journal entry for example.

You better check your dmesg for disk related errors.

My personnel observations are these error comes specially on system which have busy mysql and it gets unexpected shutdown, or the system got reboot un unexpectedly. IT also occurs dueto bad or about to get faulty disk / Zaib

---

Solution:

Run following command

sudo fsck.ext4 -f /dev/sda1

It will start running FSCK, It will ask you either to FIX or not, simply press Y to fix the entries …

e2fsck 1.42 (29-Nov-2011)
/dev/sda1: recovering journal
Pass 1: Checking inodes, blocks, and sizes
Deleted inode 6029328 has zero dtime. Fix<y>? yes

Pass 2: Checking directory structure

Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
Free blocks count wrong for group #465 (25232, counted=25231).
Fix<y>? yes

Free blocks count wrong (40843516, counted=40843515).
Fix<y>? yes

Inode bitmap differences: -6029328
Fix<y>? yes

Free inodes count wrong for group #736 (8184, counted=8185).
Fix<y>? yes

Free inodes count wrong (10409101, counted=10409102).
Fix<y>? yes

/dev/sda1: *** FILE SYSTEM WAS MODIFIED ***
/dev/sda1: *** REBOOT LINUX ***
/dev/sda1: 68466/10477568 files (0.1% non-contiguous), 1045765/41889280 blocks

Once its done, it will ask you to reboot, simply reboot the system.

root@radius:~# shutdown -r now

After the system reboots, it may ask you to fix more , simply press Y to proceed.
once all done, hopefully the system will be booted in normal mode.

 

Note: If you want to force your root filesystem to remount as rw, you can do the following. (It works sometimes in rare cases.

mount -o remount,rw /

---

Regard’s
Syed Jahanzaib

Centralized Syslog-ng logging to MySql DB

 

---

Part # 1 – Howto Save Mikrotik/Cisco Logs to Remote SYSLOG Server

Part # 2 – Centralized Syslog-ng logging to MySql DB >> You are here

Part # 3 Minimized logging to mysql with dynamic tables & trimming

 

---

In continuation to existing post related to syslog-ng, Following post illustrates how you can push syslog logs entries to mysql DB for easy access and search functions.

We all know that if you have dozen’s of switches / routers / Linux systems to manage, its not an easy task to look at each device’s log for inspection and health check. This is surely an Daunting task for any administrator or support personnel. Recently I was facing some difficulty in troubleshooting remote switch. therefore I made an syslog server and made all switches/routers info logging to this syslog which then put a copy of log in mysql DB as well as in local file too.

This post is not made for likes, dislikes or sharing purposes. Its just simple knowledge sharing on how I managed to achieve the task that looks difficult in the beginning but actually was easy when it got deployed finally.

First make sure you have an working syslog-ng installation. for more information look at syslog-ng part#1

Once you have working syslog-ng, then use the following sample /etc/syslog-ng/syslog-ng.conf

---

OS : Ubuntu 14 / 64bit
Syslog-NG:  3.5.3 [using default apt-get install package]

---

#Prerequisites

Install various packages including syslog-ng server, phpmyadmin, mysql server to store logs in DB, supporting libraries etc.

apt-get -y install apache2 mc wget make gcc mysql-server mysql-client curl phpmyadmin libdbd-pgsql aptitude libboost-system-dev libboost-thread-dev libboost-regex-dev syslog-ng libmongo-client0 libesmtp6 syslog-ng-mod-sql libdbd-mysql libdbd-mysql

During installation of above packages it may ask you to enter mysql/phpmyadmin password, do so as required. Once all the packages are installed, edit the syslog-ng file by

nano /etc/syslog-ng/syslog-ng.conf

& use below sample file to start with …

syslog-ng sample file

Note: Make sure to modify/add/remove entries like mikrotik router ip , sql credentials as  per your requirements.

# Syslog NG Config file for Ubuntu 12.4
# Syed Jahanzaib / aacable at hotmail dot com
@version: 3.3
@include "scl.conf"
options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);
owner("root"); group("adm"); perm(0640); stats_freq(0);
bad_hostname("^gconfd$");
};
source s_src {
system();
internal();
};
destination d_auth { file("/var/log/auth.log"); };
destination d_cron { file("/var/log/cron.log"); };
destination d_daemon { file("/var/log/daemon.log"); };
destination d_kern { file("/var/log/kern.log"); };
destination d_lpr { file("/var/log/lpr.log"); };
destination d_mail { file("/var/log/mail.log"); };
destination d_syslog { file("/var/log/syslog"); };
destination d_user { file("/var/log/user.log"); };
destination d_uucp { file("/var/log/uucp.log"); };
destination d_mailinfo { file("/var/log/mail/mail.info"); };
destination d_mailwarn { file("/var/log/mail/mail.warn"); };
destination d_mailerr { file("/var/log/mail/mail.err"); };
destination d_newscrit { file("/var/log/news/news.crit"); };
destination d_newserr { file("/var/log/news/news.err"); };
destination d_newsnotice { file("/var/log/news/news.notice"); };
destination d_debug { file("/var/log/debug"); };
destination d_error { file("/var/log/error"); };
destination d_messages { file("/var/log/messages"); };
destination d_console { usertty("root"); };
destination d_console_all { file("/dev/tty10"); };
destination d_xconsole { pipe("/dev/xconsole"); };
destination d_ppp { file("/var/log/ppp.log"); };
filter f_dbg { level(debug); };
filter f_info { level(info); };
filter f_notice { level(notice); };
filter f_warn { level(warn); };
filter f_err { level(err); };
filter f_crit { level(crit .. emerg); };
filter f_debug { level(debug) and not facility(auth, authpriv, news, mail); };
filter f_error { level(err .. emerg) ; };
filter f_messages { level(info,notice,warn) and
not facility(auth,authpriv,cron,daemon,mail,news); };
filter f_auth { facility(auth, authpriv) and not filter(f_debug); };
filter f_cron { facility(cron) and not filter(f_debug); };
filter f_daemon { facility(daemon) and not filter(f_debug); };
filter f_kern { facility(kern) and not filter(f_debug); };
filter f_lpr { facility(lpr) and not filter(f_debug); };
filter f_local { facility(local0, local1, local3, local4, local5,
local6, local7) and not filter(f_debug); };
filter f_mail { facility(mail) and not filter(f_debug); };
filter f_news { facility(news) and not filter(f_debug); };
filter f_syslog3 { not facility(auth, authpriv, mail) and not filter(f_debug); };
filter f_user { facility(user) and not filter(f_debug); };
filter f_uucp { facility(uucp) and not filter(f_debug); };
filter f_cnews { level(notice, err, crit) and facility(news); };
filter f_cother { level(debug, info, notice, warn) or facility(daemon, mail); };
filter f_ppp { facility(local2) and not filter(f_debug); };
filter f_console { level(warn .. emerg); };
log { source(s_src); filter(f_auth); destination(d_auth); };
log { source(s_src); filter(f_cron); destination(d_cron); };
log { source(s_src); filter(f_daemon); destination(d_daemon); };
log { source(s_src); filter(f_kern); destination(d_kern); };
log { source(s_src); filter(f_lpr); destination(d_lpr); };
log { source(s_src); filter(f_syslog3); destination(d_syslog); };
log { source(s_src); filter(f_user); destination(d_user); };
log { source(s_src); filter(f_uucp); destination(d_uucp); };
log { source(s_src); filter(f_mail); destination(d_mail); };
log { source(s_src); filter(f_news); filter(f_crit); destination(d_newscrit); };
log { source(s_src); filter(f_news); filter(f_err); destination(d_newserr); };
log { source(s_src); filter(f_news); filter(f_notice); destination(d_newsnotice); };
log { source(s_src); filter(f_debug); destination(d_debug); };
log { source(s_src); filter(f_error); destination(d_error); };
log { source(s_src); filter(f_messages); destination(d_messages); };
log { source(s_src); filter(f_console); destination(d_console_all);
destination(d_xconsole); };
log { source(s_src); filter(f_crit); destination(d_console); };
@include "/etc/syslog-ng/conf.d/"
source s_net { udp (); };
#Mikrotik IP
filter f_mikrotik { host( "192.168.1.1" ); };
log { source ( s_net ); filter( f_mikrotik ); destination ( df_mikrotik ); };
destination df_mikrotik {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
source s_mysql {
udp(port(514));
tcp(port(514));
};
# Play with below, some confusion here
destination d_mysql { pipe("/var/log/mysql.pipe" template("INSERT INTO
logs (host,facility,priority,level,tag,datetime,program,msg) VALUES
('$HOST','$FACILITY','$PRIORITY','$LEVEL','$TAG','$YEAR-$MONTH-$DAY
$HOUR:$MIN:$SEC','$PROGRAM','$MSG');\n") template-escape(yes)); };

#destination d_mysql {
#sql(type(mysql)
#host("localhost")
#username("root")
#password("SQLPASSWORD")
#database("syslog")
#table("_${R_YEAR}_${R_MONTH}_${R_DAY}")
#columns("id int(11) unsigned not null auto_increment primary key", "host varchar(40) not null", "facility varchar(20)", "priority varchar(10)", "level varchar(10)", "program text", "date date not null", "time time not null", "message text not null")
#values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG")#
#indexes("id","host","priority"));
#};
log {
source(s_net);
filter(f_mikrotik);
destination(d_mysql);
};

Save & Exit.

---

MYSQL DB to store syslog-ng logs

Create DB in mysql where are logs will be stored.

LOGIN to mysql & create DB/tables

mysql-uroot -pMYSQL_OR_ROOT_PASS

#Create DB / Syed Jahanzaib
create database syslog;
use syslog;

#Create tables in syslog db
CREATE TABLE `logs` (
`host` varchar(32) DEFAULT NULL,
`facility` varchar(10) DEFAULT NULL,
`priority` varchar(10) DEFAULT NULL,
`level` varchar(10) DEFAULT NULL,
`tag` varchar(10) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`program` varchar(100) DEFAULT NULL,
`msg` text,
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`seq`),
KEY `host` (`host`),
KEY `program` (`program`),
KEY `datetime` (`datetime`),
KEY `priority` (`priority`),
KEY `facility` (`facility`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
exit;

---

Bash script to make mysql mysql.pipe

Create a file name mysql-2-syslog.sh and make sure its start with system startup

mkdir /temp
touch /temp/mysql-2-syslog.sh
chmod +x /temp/mysql-2-syslog.sh
nano /temp/mysql-2-syslog.sh

# Paste following in this file

#!/bin/bash
SQLID="root"
SQLPASS="zaib1234"
export MYSQL_PWD=$SQLPASS
if [ ! -e /var/log/mysql.pipe ]
then
mkfifo /var/log/mysql.pipe
fi
while [ -e /var/log/mysql.pipe ]
do
mysql -u$SQLID syslog <span style="color: var(--color-neutral-600);">< /var/log/mysql.pipe </span>> /dev/null
done

WordPress is not letting proper pasting of codes this is why I am attaching image below just for reference.

Save & Exit.

Add it in startup like /etc/rc.local (before exit line) Now run it manually by

/temp/mysql-2-syslog.sh &

Now perform any activity @ mikrotik like open new terminal or login to winbox, or plug-unplug any cable from Cisco switch, and then issue following command from mysql cli (or use phpmyadmin)

---

 

Fetch information from MYSQL

Now get info from table

mysql> select * from logs;
+---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+
| host | facility | priority | level | tag | datetime | program | msg | seq |
+---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:23:04 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 1 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 2 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 3 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 4 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 5 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:25:09 | 050112 | .Nov 9 10:24:04: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to up | 6 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:25:09 | 050113 | .Nov 9 10:24:05: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to up | 7 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:25:28 | 050114 | .Nov 9 10:24:23: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to down | 8 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:25:29 | 050115 | .Nov 9 10:24:24: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to down | 9 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:26:28 | 050116 | .Nov 9 10:25:23: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to up | 10 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:26:28 | 050117 | .Nov 9 10:25:24: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to up | 11 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:27:20 | 050118 | .Nov 9 10:26:15: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to down | 12 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:27:21 | 050119 | .Nov 9 10:26:16: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to down | 13 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 11:15:52 | 050120 | .Nov 9 11:14:47: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/12, changed state to down | 22 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 11:15:52 | 050121 | .Nov 9 11:14:48: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/12, changed state to down | 23 |
+---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+
23 rows in set (0.00 sec)

• • Some examples of logs fetching command

 

Show all log files select * from logs; OR to look for speicific HOST select * from logs where host='10.0.0.1';

mysql> select * from logs where host='10.0.0.1';
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| host | facility | priority | level | tag | datetime | program | msg | seq |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:23:04 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 1 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 2 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 3 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 4 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 5 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
13 rows in set (0.00 sec)

OR to look for speicific HOST between specific dates select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:00:26' and '2017-11-10 10:50:26' ;

mysql> select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' ;
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| host | facility | priority | level | tag | datetime | program | msg | seq |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
8 rows in set (0.00 sec)

Or look for particular error on Mikrotik log for incorrect login in specific date time range select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' and program='system,error,cr';

mysql> select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' and program='system,error,cr';
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| host | facility | priority | level | tag | datetime | program | msg | seq |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
2 rows in set (0.00 sec)

 

---

 

– Simple PHP page to show results from mysql table

Following is a php page I made to fetch results from the mysql table and display it in browser.

<?php
// by Syed Jahanzaib
$host="localhost";
$username="root";
$password="PASSWORD"; // Mysql password
$db_name="syslog"; // Database name
$tbl_name="logs"; // Table name
// Connect to server and select databse
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
//$sql="SELECT * FROM $tbl_name ORDER BY `LOGS` datetime LIMIT 0 , 10";
$sql = "SELECT * FROM `logs`\n"
. "ORDER BY `logs`.`datetime` DESC LIMIT 0, 50 ";
$result=mysql_query($sql);
// Define $host_column=1
echo '</pre>
'; echo ' '; while($rows=mysql_fetch_array($result)){ //$host_column assign here from result $host_column=$rows['host']; $priority_column=$rows['priority']; if($priority_column=='err'){ echo " "; }else if($host_column=='101.11.11.36'){ echo " "; }else if($host_column=='101.11.12.225'){ echo " "; } } echo '
<table border="1" width="1400" cellspacing="1" cellpadding="1" align="left">
<tbody>
<tr>
<th>ID</th>
<th>Host</th>
<th>Date</th>
<th>Info</th>
<th>Type</th>
<th>Messgae</th>
</tr>
<tr bgcolor="#FFA07A">
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
<tr bgcolor="#bbbbbb">
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
<tr bgcolor="#cccccc">
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
</tbody>
</table>
<pre>';
mysql_close();
?>

---

– Another PHP / Mysql & AJAX base example

to show record with pagination , Date selection and Live Search button option.

http://PHP MYSQL AJAX – WITH PAGINATION / DATE / SEARCH OPTION – BY SYED JAHANZAIB

Sample Output

---

TIP

Query log from multiple tables in syslog DB

Sent by by Mr. Faryad

Q:
For example if your SYSLOG creates daily date wise table on daily basis then how we will write query to search in multiple tables?

In this case simple one line query will not work. For Example you saves 3 days data in 3 tables i.e. `2017-11-10`, `2017-11-11` and `2017-11-12`, and you need to search in these 3 tables, in this case you should need UNION method of MYSQL. I am giving little code here:

select * from `2017-11-10` where host=’10.0.0.1′ AND datetime between ‘2017-11-10 10:00:26’ and ‘2017-11-10 10:50:26′ UNION
select * from `2017-11-11` where host=’10.0.0.1’ AND datetime between ‘2017-11-11 10:00:26’ and ‘2017-11-11 10:50:26′ UNION
select * from `2017-11-12` where host=’10.0.0.1’ AND datetime between ‘2017-11-12 10:00:26’ and ‘2017-11-12 10:50:26’ ;

this multi line query will fetch records from 3 tables just like one line query.

---

TIP:

Unable to initialize database access (DBI); rc=’-1′, error=’No such file or directory (2)’

If you are seeing error on syslog-ng service start/restart

apt-get install libdbd-mysql

---

Persist names error when using multiple hosts

Error checking the uniqueness of the persist names, please override it with persist-name option. Shutting down.; persist_name=’affile_dd_writers(

To settle this add persistent name in config like this

Working example for 5 hosts

filter f_mikrotik10 { host("192.168.0.10") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik10 ); destination ( df_mikrotik10 ); };
destination df_mikrotik10 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};

filter f_mikrotik14 { host("192.168.0.14") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik14 ); destination ( df_mikrotik14 ); };
destination df_mikrotik14 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};

filter f_mikrotik26 { host("192.168.0.26") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik26 ); destination ( df_mikrotik26 ); };
destination df_mikrotik26 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};

filter f_mikrotik34 { host("192.168.0.34") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik34 ); destination ( df_mikrotik34 ); };
destination df_mikrotik34 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};

filter f_mikrotik42 { host("192.168.0.42") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik42 ); destination ( df_mikrotik42 ); };
destination df_mikrotik42 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};

log {
source(s_net);
filter(f_mikrotik10);
destination(d_mysql);
};

log {
source(s_net);
filter(f_mikrotik14);
destination(d_mysql);
};

log {
source(s_net);
filter(f_mikrotik26);
destination(d_mysql);
};

log {
source(s_net);
filter(f_mikrotik34);
destination(d_mysql);
};

log {
source(s_net);
filter(f_mikrotik42);
destination(d_mysql);
};

---

Regard’s
Syed Jahanzaib

TikTik – Script to disconnect hotspot user if its already active in pppoe

its a weird world we live in !

Fix the root cause that is making issue , dont always go for workarounds

WORKAROUND :

Add this in in Hotspot > User Profile > Default > Scripts > On Login)

# Check if this hotspot user is already logged in on PPPOE on same mikrotik, then kick HOTSPOT
:local uname $user;
:local u;
:foreach u in=[/ppp active find name=$user ] do={
:log warning "$user ID is already active in pppoe. Now disconnecting from HotSpot ... Done!"
:foreach i in=[/ip hotspot active find user=$uname] do= {
/ip hotspot active remove numbers=$i;
}
}

.

With some modification you can add script in pppoe login profile as well, which will check if user is already active in hotspot then kick pppoe or hs user.

regard’s
J.

Mikrotik with Freeradius/mySQL – Dealing with STALE sessions in FR – Part 5

~ Dealing with STALE session in Freeradius 2.x ~
! From the CORE of FREERADIUS !
By
Syed jahanzaib

---

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …

---

Scenario:

Mikrotik is working as as NAS (pppoe server) along with Freeradius for authorization/accounting. When electric power or hardware failure occurs or in a situation where NAS cannot update the FR about user is being disconnected & not active any more, the FR will consider user ACTIVE in radacct table, therefore on next dialup attempt by the user (once everything is restored), he will get access denied because

1. There is Simultaneous-Use attribute to prevent multiple login from same user
2. user accctsoptime is NULL because FR have not receive any update from the NAS about user is not online any more.

To remove such stale sessions, there are various methods, you can make your own bash script & schedule it to run every x minutes (example every 5 minutes). Or you can use IF query in authorize session so that when user tries to reconnect & his sessions have NULL  then in this case the query should put stop entry in acctstoptime and allow user new login. or make a PHP program that can be scheuled to run every 5 minutes and then query the radacct session for users whose account update have not received from the NAS.

---

STEP – 1

First you need to add lastupdate column in your RADACCT table (in radius db) . Infact all solutions posted in this guide relies on it. so add it

ALTER TABLE 'radacct' ADD 'lastupdate' TIMESTAMP NOT NULL AFTER 'xascendsessionsvrkey';

---

Now you can use following methods, which ever is ok with you, or you can combine both as well, I tested it a local network & worked well for me …. z@iB

---

Solution # 1
Using BASH in CRON

You can use following bash script to run every 5 minutes which will check for any stale session by matching last update time with current. If the radius have not received any updates for that account from the NAS for more then 5 minutes , it will consider this session as STALE and will close its session its record.

mysql -uroot -pSQLPASS -s --skip-column-names -e "use radius; UPDATE radacct SET acctstoptime = NOW(), acctterminatecause = 'SCRIPT ACTION' WHERE acctstoptime IS NULL AND lastupdate < DATE_SUB(NOW(), INTERVAL 10 MINUTE)";

---

Solution#2
This will update the radacct acctstoptime only if user will try to re-connect, ]

Edit /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

for compiled RADIUS , use this path

nano /usr/local/etc/raddb/sites-enabled/default

in AUTHORIZE { section add following query

if (User-Name){
if("%{sql:UPDATE radacct set AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' and CallingStationId='%{Calling-Station-Id}' and AcctStopTime is NULL}"){
}
}

Save & restart freeradius server. This way if NAS goes out, the session will still show online in radacct table, but when user will relogin next time, his session on radacct table will update and new entry will be created.

Some DRY run theory !

To ALL: read this slowly & carefully …

First, There are some fundamentals you need to understand in order to troubleshoot the STALE session issue or session not appearing in the DMA or your panel.

Example what is interim update & why it is important.

Once user dials-in & get authenticated with the NAS , its sessions is recorded/opened in the Radius database called ‘radacct’ with acctstoptime set to NULL means the session is active (this is how radius tells ACTIVE number of users by checking acctstoptime field)
when you set interim update setting in the NAS , it means NAS will send all active users accounting to the Radius and it will be updates in the radacct table.
Ok, now consider this scenario.
the NAS got rebooted by power failure ,obviously all users got disconnected but the NAS didn’t got the time to inform the RADIUS about this event. Now the NAS is online and receiving the users authentication request, when the NAS ask radius on user account status to decide whether to let user connect or not, Radius will inform NAS that user is already ACTIVE (why? because radius didn’t received any user disconnection request from the NAS earlier so radius still thinks the user is active whereas the user is not). NAS will reject all these users requests and log USER IS ALREADY ACTIVE !!!

Hmmm, so what’s the manual solution? Clear all the session in radacct table whose acctstoptime is NULL but there are caveats in it, playing with such sensitive table is really not understable.

& what about any automated solution to this problem?

in dMA there is a setting called INACTIVITY TIMER, which is set to 10 minute default. What does this means? it means that if the RADIUS does not receives any account update for connected session for 10 mnts, then Radius will consider that these sessions are dead and it should close them in radacct (acctstoptime set to currenttime), this will let user login again.

Now consider this scenario.

All users are logged in, NAS & Radius have same number of online users. Now suddenly dueto to faulty network cable , communication between the NAS and the Radius got disconnected. What will happen now?

All users will remain ONLINE in NAS, but on radius, after 10 mnts radius will clear all online session and declare them dead and consider them offline. Now after 15 minutes the connectivity between NAS and DMA got restored. now the NAS will again send the interim updates to the RADIUS, but since the radius have already closed this session due to INACTIVITY SETTINGS, these interim updates will be DISCARDED, oh Yes !

Now once you understand the logics, & if you have common sense as well, you will understand the possible causes of why there are less number of users bein showed in the ONLINE session of RADIUS.

setting INTERIM update timings to too low will overflood your low specs radius. setting it to too high will create problem of FALSE Session & already logged in issues.

This is why experienced personnels will always invest good in hardware selection like good CPU with multiple cores and CPU cache level, sufficient memory (its really cheap now a days), STORAGE< this is most important section of overall readius performance. INvest in good brand SSD drives , preferably with Radius 10 model as this will give you same level of read+write speed.

Tuning up MYSQL is also equivalent important to digest interim updates at earliest.

Now if some is unable to digest all of these, he is free to vomit out all 🙂

Try setting your interim update to 3 minutes ! rest DMA will settle all as per the 10 mnts intervale ( its user configurable option)

---

Regard’s
SYED JAHANZAIB