Syed Jahanzaib Personal Blog to Share Knowledge !

March 8, 2018

Short Notes on LibreNMS !

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 3:46 PM

libre dashboard

This post contains short notes & step by step guide on open source network omonitoring system called LIBRENMS , it is based on Php/MySQL/Snmp.


STEP BY STEP iNSTALLATION FOR LIBRENMS

OS: Ubuntu 16 (Server Edition/64bit)

First install required support packages

apt-get update
# Now install all the required components in a single GO ...
apt install -y apache2 composer fping git graphviz imagemagick libapache2-mod-php7.0 mariadb-client mariadb-server mtr-tiny nmap php7.0-cli php7.0-curl php7.0-gd php7.0-json php7.0-mcrypt php7.0-mysql php7.0-snmp php7.0-xml php7.0-zip python-memcache python-mysqldb rrdtool snmp snmpd whois curl acl

Add local user for librenms

useradd librenms -d /opt/librenms -M -r
usermod -a -G librenms www-data
systemctl restart mysql

Create user & DB

mysql -uroot -pzaib1234
CREATE DATABASE librenms CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE USER 'librenms'@'localhost' IDENTIFIED BY 'zaib1234';
GRANT ALL PRIVILEGES ON librenms.* TO 'librenms'@'localhost';
FLUSH PRIVILEGES;
exit

Now edit maria-db config file

nano /etc/mysql/mariadb.conf.d/50-server.cnf

& add following

[Within the [mysqld] section please add]

innodb_file_per_table=1
sql-mode=""
lower_case_table_names=0
systemctl restart mysql

Save & Exit.

Web Server Config

Edit PHP files & setup TIMEZONEs

nano /etc/php/7.0/apache2/php.ini
nano /etc/php/7.0/cli/php.ini

Search for ‘date.timezone‘ & change it to

date.timezone = Asia/Karachi

Save & EXit.

Configure Apache

nano /etc/apache2/sites-available/librenms.conf

Add the following config,



DocumentRoot /opt/librenms/html/
ServerName librenms.localhost
AllowEncodedSlashes NoDecode

Require all granted
AllowOverride All
Options FollowSymLinks MultiViews



Save & Exit

a2dissite 000-default
a2ensite librenms.conf
a2enmod rewrite
systemctl restart apache2

Configure snmpd

cp /opt/librenms/snmpd.conf.example /etc/snmp/snmpd.conf

Now edit SNMPD config file and add our SNMP community string like public or other

nano /etc/snmp/snmpd.conf

Edit the text which says RANDOMSTRINGGOESHERE and set your own community string.
save and exit.

curl -o /usr/bin/distro https://raw.githubusercontent.com/librenms/librenms-agent/master/snmp/distro
chmod +x /usr/bin/distro
systemctl restart snmpd

CRONJOBS for librenms

cp /opt/librenms/librenms.nonroot.cron /etc/cron.d/librenms
cp /opt/librenms/misc/librenms.logrotate /etc/logrotate.d/librenms
chown -R librenms:librenms /opt/librenms
setfacl -d -m g::rwx /opt/librenms/rrd /opt/librenms/logs
setfacl -R -m g::rwx /opt/librenms/rrd /opt/librenms/logs

Install LIBRE-NMS via composer command

cd /opt
composer create-project --no-dev --keep-vcs librenms/librenms librenms dev-master

Example:

1- install libre via cmd

Now access libre-nms via browser …

http://yourip/install.php

& follow onscreen steps…

2- libre first step of gui

Make sure to add id password which is authorized to change DB , as required …

 

 

 

 

AT one point, You have to create the config.php manually as showed here.

nano /opt/librenms/config.php

& paste the data as showed in the config window of gui.

<?php
## Have a look in defaults.inc.php for examples of settings you can set here. DO NOT EDIT defaults.inc.php!

### Database config
$config['db_host'] = 'localhost';
$config['db_port'] = '3306';
$config['db_user'] = 'librenms';
$config['db_pass'] = 'zaib1234';
$config['db_name'] = 'librenms';
$config['db_socket'] = '';

// This is the user LibreNMS will run as
//Please ensure this user is created and has the correct permissions to your install
$config['user'] = 'librenms';

### Locations - it is recommended to keep the default
#$config['install_dir'] = "/opt/librenms";

### This should *only* be set if you want to *force* a particular hostname/port
### It will prevent the web interface being usable form any other hostname
#$config['base_url'] = "http://librenms.company.com";

### Enable this to use rrdcached. Be sure rrd_dir is within the rrdcached dir
### and that your web server has permission to talk to rrdcached.
#$config['rrdcached'] = "unix:/var/run/rrdcached.sock";

### Default community
$config['snmp']['community'] = array("public");

### Authentication Model
$config['auth_mechanism'] = "mysql"; # default, other options: ldap, http-auth
#$config['http_auth_guest'] = "guest"; # remember to configure this user if you use http-auth

### List of RFC1918 networks to allow scanning-based discovery
#$config['nets'][] = "10.0.0.0/8";
#$config['nets'][] = "172.16.0.0/12";
#$config['nets'][] = "192.168.0.0/16";

# Update configuration
#$config['update_channel'] = 'release'; # uncomment to follow the monthly release channel
#$config['update'] = 0; # uncomment to completely disable updates 

Save & Exit.

and click on FINISH on GUI.


librenms install done

 

Make sure to remove COMPOSER & set permissions when all is OK. Else it will give error in VALIDATION part.

chown -R librenms:librenms /opt/librenms
apt-get remove composer

 


Some more snaps

  • Add Device

 

add device

librenms add device with snmp

Devices Overview

device view

libre dashboard.JPG

Advertisements

February 26, 2018

Power of Open Source / Traffic Controlling with TC

Filed under: Linux Related — Syed Jahanzaib / Pinochio~:) @ 9:49 AM

speed meter

 

pwoerful pawn

Living in the Dark & Playing  & with the Open Source is Fun & Amazingly Powerful ! z@iB


Scenario:

We have few Linux base FTP/HTTP base media file sharing server setup. User can access these server by bypassing the router as we donot want to put load on the router itself & user should access these servers from switch level by intervlan routing setup.

Problem:

Since the FTP data is in many terabytes and hundreds of users are accessing it, this is creating bottleneck for server’s network/storage.

Solution:

On Linux we can use TRAFFIC CONTROLLER (TC) to limit bandwidth on per user IP basis.  We will create a simple bash script which will do the following in sequence …

  • FTP Server IP: 101.11.11.254
  • Clear any existing shaping rules
  • Create 4 bandwidth packages as following
    – 1024kbps  [for General Users subnet 101.11.11.0/24]
    – 2048kbps  [for Support Staff  subnet 101.11.50.0/24]
    – 512kbps     [for ICMP, so that ping should work fine , Protocol base]
    – 128kbps    [for any unknown IP series, that is no define above, for ALL 0.0.0.0/0.0.0.0]
  • Assign ip subnet to these packages (/24 and it will create per ip base rules via LOOP statement)
  • In this example, we are limiting Bandwidth in one way only, means packets sending from server to client will be applied limitation .. For both ways just duplicate rules and use src as well.

Create , the script!

mkdir /temp
touch /temp/shaper.sh
chmod +x /temp/shaper.sh

Now Edit

nano /temp/shaper.sh

& paste following,

#!/bin/bash
# Traffic Shaping Using `TC` in Linux / This was made for specific requirements.
# ITs customzied designed to fulfill some specific requirements & it went well.
# Following Script will limit outgoing bandwidth for specific ip range series.
# It will also allow ICMP more bandwidth so that ping to local server may not delay or imtout.
# It will then limit all other ips that are not listed to minimum bandwidth that is 128kbps.
# Adjust is as per your requirements.
# Syed Jahanzaib / aacable @ hotmail . com / https://aacable.wordpress.com :~)
# 23-Febraury-2018

# Enable following to see debug
# set -x

# Local Network Interface name
INT="ens33"
INT_SPEED="100Mbit"

# First IP Range & its package with class
IP1="101.11.11"
IP1_PKG="1mbit"
IP1_CLASSID="1:10"

# Second IP Range & its package with class
IP2="101.11.50"
IP2_PKG="2mbit"
IP2_CLASSID="1:20"

# Separate Bandwidth for ICMP protocol to minimze delay in PING/ICMP packets
ICMP_PKG="512kbit"
ICMP_CLASSID="1:200"

# Other Default Package Line, means all other unmarked IP's or traffic
UNKNOWN_PKG="128kbit"
UNKNOWN_CLASSID="1:201"

if [[ "$1" != "start" && "$1" != "stop" && "$1" != "status" ]]; then
echo "Usage:

./shaper.sh start
./shaper stop
./shaper status

by Jz!"
exit 1
fi

if [ $1 == "stop" ]; then
echo "Clearing all existing Queues on user request ...

Done!"
tc qdisc del dev $INT root 2> /dev/null > /dev/null
exit 1
fi

if [ $1 == "status" ]; then
echo "Showing Current TC Class and Filter Status
=========================================="
tc class show dev $INT
echo "

"
tc -s -d class show dev $INT
echo "=========================================="

exit 1
fi

if [ $1 == "start" ]; then
echo "Starting Shaper ...
Clearing all existing Queues for re-implementation ...
=========================================="
"

# clean existing down- and uplink qdiscs, hide errors
tc qdisc del dev $INT root 2> /dev/null > /dev/null

echo "
Adding Queues ...."
# Create egress shaping for all required classes
tc qdisc add dev $INT root handle 1: htb default 20 r2q 50
tc class add dev $INT parent 1: classid 1:1 htb rate $INT_SPEED ceil $INT_SPEED
tc class add dev $INT parent 1:1 classid $IP2_CLASSID htb rate $IP1_PKG ceil $IP1_PKG prio 100
tc class add dev $INT parent 1:1 classid $IP1_CLASSID htb rate $IP2_PKG ceil $IP2_PKG prio 100
tc class add dev $INT parent 1:1 classid $ICMP_CLASSID htb rate $ICMP_PKG ceil $ICMP_PKG prio 100
tc class add dev $INT parent 1:1 classid $UNKNOWN_CLASSID htb rate $UNKNOWN_PKG ceil $UNKNOWN_PKG prio 100

# ICMP reply Adjustment so ping may not timeout or high delay may not occur at client end for this server local IP
tc filter add dev $INT parent 1: protocol ip prio 100 u32 match ip protocol 1 0xff flowid $ICMP_CLASSID

# IP-1 series limitation applies here ...
for (( i = 1 ; i <= 255; i++ ))
do
b=$i
tc filter add dev $INT parent 1: protocol ip prio 100 u32 match ip dst $IP1.$i flowid $IP1_CLASSID
done

# IP-2 series limitation applies here ...
for (( i = 1 ; i <= 255; i++ ))
do
b=$i
tc filter add dev $INT parent 1: protocol ip prio 100 u32 match ip dst $IP2.$i flowid $IP2_CLASSID
done

# Send every unknown IP to classid UNKNOWN_CLASS that is lowest. so that any IP that is not listed above will get htis lowest bandwidth
tc filter add dev $INT parent 1: protocol ip prio 100 u32 match ip dst 0.0.0.0/0.0.0.0 flowid $UNKNOWN_CLASSID

echo "
Done!"
fi

Result:

linux TC down-result.jpg

 

January 8, 2018

Mikrotik with Freeradius/mySQL – Quota Limit # Part-7

Filed under: freeradius, Linux Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:36 AM

FreeRadius Core info

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration

FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding

FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions

FREERADIUS  WITH MIKROTIK – Part # 6 – External Auth Script & RADPOSTAUTH

FREERADIUS WITH MIKROTIK – Part #7  – Quota Limit  < You are Here


Personnel Note:

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not an ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal. And with the right search, reading, understanding logic’s, you can do all on your own. I strongly encourage to read the FR mailing list and Google

OS: Ubuntu 16.04.3 LTS / 64bit


qt-logo.jpg

Scenario:

Assuming, we have already configured freeradius and user can authenticate & all is working fine. Now we want to provide users with Quota limit. But we donot want to use RADACCT table to calculate there usage & we also don’t want to check there quota on REAL time using freeradius UNLAG query. we will simply cron a script that will run every 10 minutes and will check for quota used for each user from the USERS table. We will make a separate table where we will add the QUOTA user and there limit to avoid re checking for same user. and in USERS table we will simply update there USED data record (in qt_used column in users table) using TRIGGER on radacct table.


USERS table Example.

Where all users information like name/mobile etc will be saved. Also it will contain a column qt_used which we will update dynamically using trigger on radacct table.

----------------------------------------------------------
-- Table structure for table `users`

CREATE TABLE `users` (
`id` int(10) NOT NULL,
`username` varchar(128) NOT NULL,
`password` varchar(32) NOT NULL,
`firstname` text NOT NULL,
`lastname` text NOT NULL,
`email` text NOT NULL,
`mobile` text NOT NULL,
`cnic` text NOT NULL,
`srvname` text NOT NULL,
`srvid` int(3) NOT NULL,
`expiration` date DEFAULT NULL,
`mac` varchar(30) NOT NULL,
`bwpkg` varchar(256) NOT NULL,
`pool` varchar(128) DEFAULT 'other',
`is_enabled` int(1) NOT NULL,
`is_days_expired` int(1) NOT NULL,
`is_qt_expired` int(1) NOT NULL,
`is_uptime_expired` int(1) NOT NULL,
`qt_total` varchar(32) NOT NULL,
`qt_used` varchar(20) NOT NULL,
`uptime_limit` varchar(20) NOT NULL,
`uptime_used` varchar(32) NOT NULL,
`owner` text NOT NULL,
`createdon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `username` (`username`) USING BTREE;

ALTER TABLE `users_qt`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

USERS Quota table Example!

If user is added in this product (username & qoota). then bash script will only check these users against there used quota (qt_used column in USERS table)

------------------------------------------------------------
-- Table structure for table `users_qt`

CREATE TABLE `users_qt` (
`id` int(11) NOT NULL,
`username` varchar(32) NOT NULL,
`quota` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users_qt`
ADD PRIMARY KEY (`id`);

LOG Table Example!

We will use this table to log any disconnect message.

-- Table structure for table `log`
CREATE TABLE `log` (
`id` int(11) NOT NULL,
`data` varchar(32) NOT NULL,
`msg` varchar(128) NOT NULL,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `log`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

Trigger for RADACCT

This trigger will simply update the qt_used column for all users (which are enabled, not expired, not over-quota already)

DELIMITER $$
CREATE TRIGGER `radacct_after_update` AFTER UPDATE ON `radacct` FOR EACH ROW BEGIN
#if (NEW.acctterminatecause ='Session-Timeout')
#then
#UPDATE users set is_days_expired ='1' WHERE username =OLD.username;
#UPDATE radusergroup set groupname ='expired' WHERE username =OLD.username;
#ELSE
if (NEW.acctoutputoctets > 1)
OR (NEW.acctinputoctets > 1)
then
UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE is_enabled ='1' AND is_qt_expired ='0' AND is_uptime_expired ='0' AND is_days_expired ='0';
END IF;
#END IF;
END
$$
DELIMITER ;

Now insert a record in users_qt table for a user in this table. (quota limit 300 bytes testing only)

INSERT INTO 'users_qt' ('id', 'username', 'quota') VALUES (NULL, 'zaib', '300');

Now check the record.

root@radius:/temp# mysql -uroot -pPASS -s --skip-column-names -e "use radius; select * from users_qt;"

1 zaib 300

Ok now we simply connect the user and will do some data download or browse , since we have defined very small bytes limit it will fill in seconds.

Now to check the over-quota user we will use a small bash script (which will be cron to run every 10 minutes)


BASH script to check over quota user.

qc_check.sh

root@radius:/temp# cat qc.sh
#!/bin/bash
##set -x
SQLID="root"
SQLPASS="SQLPASS"
export MYSQL_PWD=$SQLPASS
DB="radius"
#Table which contain main users information
TBL="users"
#Table which contains users name which will be scanned for quota
QTDB="users_qt"
#Log table in which few actions will be logged
TBL_LOG="log"
#Rad user group in which we will update user profile like from 1mb to expired or likewise
GROUP="radusergroup"
#What is the Next pool name which will be
NEXTSRV="overquota"
# Temp file where user list will be saved
TMP="/tmp/qt_users.txt"
# NAS IP & port to send COA or disconenct pkt
NAS="10.0.0.1:3799"
# Radius Secret on NAS
RADSECRET="testing123"
CMD="mysql -u$SQLID --skip-column-names -s -e"
$CMD "use $DB; select username from $QTBL;" > $TMP
if [ ! -s $TMP ]
then
echo "No user found to check for QUOTA in table, exit"
exit 1
fi

# Run loop forumla to run CMD for single or multi usernames
num=0
cat $TMP | while read users
do
num=$[$num+1]
USERNAME=`echo $users |awk '{print $1}'`
TOTQT=`$CMD "use $DB; select quota from users_qt where username ='$USERNAME';"`
USEDQT=`$CMD "use $DB; SELECT qt_used FROM $TBL WHERE UserName='$USERNAME';"`

# If quota exceeds then perform multiple actions
if [ "$USEDQT" -gt "$TOTQT" ]; then

# Pull account session id from radacct table, which will be used to send to NAS for user COA OR Disconnection
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
echo "Warning ! $USERNAME user quota reached limits. Allowed = $TOTQT Used = $USEDQT Disconnectig him now by sendin POD to NAS and adding LOG as well"

#Delete user from Quota check table, so it may reapt checking every time for same user
$CMD "use $DB; delete from users_qt where username ='$USERNAME';"

# Update User group to NEXT Service , like expired pool
$CMD "use $DB; update $GROUP set groupname='$NEXTSRV' where username ='$USERNAME';"

# If user is Online , Kick him using session id with RADCLIENT
if [ ! -z "$ACCTSESID" ]; then
echo user-name=$USERNAME,Acct-Session-Id=$ACCTSESID | radclient -x $NAS disconnect $RADSECRET

#Add in LOG table about kicked action
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User Kickd dueto Quota limit exceeded');"
else
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User quota found exceeded, but could not found its online session in Radius');"
fi
fi
done

Done.

Now run the Quota checking script …

root@radius:/temp# ./qc.sh

Warning ! zaib user quota reached limits. Allowed = 300 Used = 400 Disconnectig him now by sending POD to NAS and adding LOG as well

Once script found user Over Quota, it will update the user group in RADGROUPREPLY  so user should get OVER QUOTA pool …

group_updated

Entry in LOG table will be added …

over-qt.JPG

(in above pic, user was not online but he reached the quota, therefore the log informed about this too 🙂 )

Ok lets test user with radclient.

echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth RADSECRET

RESPONSE:

Received response ID 218, code 2, length = 89
Reply-Message = "Exp-Mod-Reply: Your account has expired."
Framed-Pool = "expired-pool"
Mikrotik-Rate-Limit = "1k/1k"

Total approved auths: 1
Total denied auths: 0
Total lost auths: 0

 

Disclaimer:

I might be missing any part, message me if any one required further info in it.

In the end I would like to thanks Mr. Khazoum Yaghi to give me hints for the separate table for quota checking. I was stucked on the script which keep checking for over-quota users again and again but after adding user user quota in separate table, the script now delete user entry from this table, so next time the script will run, it will not check that user because of missing entry in the quota table.

There are some other proper methods as well, but I just managed to achieve my task this way.

Regard’s

Syed Jahanzaib

December 25, 2017

Freeradius External Authentication Script & log reject request only in radpostauth with customized REPLY message

Filed under: freeradius, Linux Related — Syed Jahanzaib / Pinochio~:) @ 11:49 PM

FreeRadius Core info

  1. FREERADIUS WITH MIKROTIK – Part #1
  2. FREERADIUS WITH MIKROTIK – Part #2 
  3. FREERADIUS WITH MIKROTIK – Part #3
  4. FREERADIUS WITH MIKROTIK – Part #4
  5. FREERADIUS WITH MIKROTIK – Part #5
  6. FREERADIUS EXTERNAL AUTH SCRIPT & RADPOSTAUTH – Part #6 >>> YOU ARE HERE

Note: This post is for reference purposes only. This post may change rapidly as at the time of writing , frequent changes are being made to adjust many parameters to fulfill local OP requirements. So you may see some display text difference as compared in the code itself … worry not 🙂 Just read and Enjoy, Pick your desired section.

Also you may want to read this reply came from ALAN (freeradius) when I asked on external auth script.

FreeRadius users mailing list (freeradius-users@lists.freeradius.org)

________________________________
From: Freeradius-Users <freeradius-users-bounces+aacable=hotmail.com@lists.freeradius.org> on behalf of Alan DeKok <aland@deployingradius.com>
Sent: Saturday, January 13, 2018 8:30:02 PM
To: FreeRadius users mailing list
Subject: Re: External Auth Script or local Auth

On Jan 13, 2018, at 8:40 AM, JAHANZAIB SYED <aacable@hotmail.com> wrote:
> I am using Freeradius ver 2.1.10

You should upgrade to 2.2.10. There’s just no reason to use a version which is almost 10 years old

> I have noticed that some commercial radius servers (with freeradius backend) using External script (php/perl or C code) to authenticate users. I just wanted to know that what are the additional benefits of using external auth script over freeradius own authentication (via rad-groups) ?

So that they can avoid GPL licensing issues.

> I made my own bash script that runs fine for authentication by checking user status in my_users table like expiry, disable/enable, quota, uptime etc , but for heavy load network like 20-30k users, what is recommended?

Use the features in FreeRADIUS. They work. They’re also MUCH faster than forking an external program.

i.e. FreeRADIUS can do 10’s of 1000’s of DB queries per second, and 10’s of 1000’s of authentications per second. When you use shell script, that number can drop by 10x to 100x.

On top of that, why re-implement features which already work? It doesn’t make any sense to write a shell script to do something, when FreeRADIUS can already do it.
Most people who are re-packaging FreeRADIUS are figuring this out. Some have taken the source, and hacked it up… at which point they have their own magic server that no one understands.
And a few years later, FreeRADIUS has more / better features than their hacked-up version, and they can no longer sell decent features to their customers.

Alan DeKok.

z@ib

Following is an BASH script which we can use to authenticate users in freeradius. Using external auth script have several benefits over traditional attributes. Example we can provide NAS with radreply based on various attributes in users table like expiration, quota, disabled/expired users & other crazy stuff 🙂

It took many hours for a duffer like me to accomplish this task. Some says BASH is not suitable for servers under heavy load, therefore you may look for C/C++ approach for faster fetching results. I have tested this with 1000 + loop auth requests & it worked fine without putting any load on the server & without any invalid  or missing any single request !

Regard’s
Syed Jahanzaib


Scenario:

  • OS: Ubuntu 12.4 Server Edition
  • FreeRADIUS Version 2.1.10, for host i686-pc-linux-gnu
  • Mysql Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.2

>>> OP Requirements :

  1. If users not found, It should display error message in debug log & REJECT user request
  2. If user password does not match with the radcheck table entry, REJECT the user request
  3. If user is Disabled by OP, let him login with disabled-pool (for redirection purposes)
  4. If user is Expired, let him login with expired-pool (for redirection purposes)
  5. If user is logged in for the first time, his MAC should update in USERS table for AUTO MAC Binding purposes. So only this device should be able to connect in future
  6. If user try to login from other device, let him login with invalidmac-pool (for redirection purposes)
  7. Check for Over Quota users (not required at a moment)
  8. LOG requests in mysql RADPOSTAUTH table for REJECT actions

So in short we donot want to REJECT user login request if it matches above criteria.

Yes I know its a kind of weird stuff, but still its good for learning !

>>> Mysqle ‘USERS’ Table example

In mysql we have following users table [Just an example, its not fine tuned]

#### USERS TABLE
root@testradius:/temp# mysql -uroot -pROOTPASS -e "use radius; describe users;"
+------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | MUL | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(11) | NO | | NULL | |
| expiration | datetime | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| bwpkg | varchar(1000) | NO | | NULL | |
| pool | varchar(128) | YES | | other | |
| enabled | varchar(1) | NO | | NULL | |
| owner | text | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------+------+-----+-------------------+-----------------------------+
17 rows in set (0.00 sec)

root@testradius:/temp# mysql -uroot -pROOTPASS -e "use radius; select * from users;"
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+
| id | username | password | firstname | lastname | email | mobile | cnic | srvname | srvid | expiration | mac | bwpkg | pool | enabled | owner | createdon |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+
| 1 | zaib | | syed | jahanzaib | aacableAThotmail.com | 03333021909 | 1234567890-1-1 | | 0 | 2018-12-23 00:00:00 | 00:0C:29:35:F8:2F | 1024k/1024k | public-pool | 1 | galaxy | 2017-12-23 16:25:09 |
| 7 | test | | test firstname | test lastname | test@test.com | 13434234234 | 242342420424-42-2 | | 0 | 2017-12-24 00:00:00 | 00:0C:29:35:F8:2F | 2048k/2048k | public-multinet-pool | 1 | zaib | 2017-12-25 16:27:39 |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+

FREERADIUS config to accommodate external authentication script [auth.sh] execution

We need to add following directives in FR config files in order to execute the bash script for auth purpose once the username/password is found ok by FR.


>>> Edit freeradius/USERS file in FR

nano /etc/freeradius/users

& Add following in the end of this file …

DEFAULT Auth-Type := PAP
Exec-Program-Wait = "/temp/auth.sh %{User-Name} %{User-Password} %{Calling-Station-Id}"

SAVE & EXIT !


>>> Now edit sites-enabled/default file …

nano /etc/freeradius/sites-enabled/default

& use following … [my working sample file, you must modify it as per your requirements]

authorize {
preprocess
chap
pap
mschap
digest
# suffix
# eap {
# ok = return
# }
files
### ZAIB Section-1 Start Here ##
sql{
notfound = 1
}
if(notfound){
update reply {
Reply-Message = 'Username not found'
}
reject
}
### ZAIB Section-1 Ends Here ##
# checkval
# expiration
# logintime
# pap
}

authenticate {
Auth-Type PAP {
pap
}
Auth-Type CHAP {
chap
}
Auth-Type MS-CHAP {
mschap
}
digest
unix
# eap
}

preacct {
preprocess
acct_unique
suffix
files
}

accounting {
detail
unix
radutmp
sql
exec
attr_filter.accounting_response
}

session {
# radutmp
sql
}

### ZAIB Section-2 Start Here ##
post-auth {
exec
Post-Auth-Type REJECT {
update reply {
Reply-Message = 'Wrong Password'
}
sql
attr_filter.access_reject
}
}
### ZAIB Section-2 ENDS Here ##

pre-proxy {
}

post-proxy {
eap
}

SAVE & EXIT !


 


AUTH.SH [bash script as external auth for FR]

Create auth.sh file

  • mkdir /temp
  • touch /temp/auth.sh
  • chmod+x /temp/auth.sh
  • nano /temp/auth.sh

& paste following data [after necessary modifications]

#!/bin/bash
# Bash script for Freeradius / external auth script
# By Syed Jahanzaib / aacable at hotmail dot com
# https://aacable.wordpress.com
# 22-DEC-2017
# Last modified on 27-DEC-2017
# You may want to disable few sections in it like user validation, password validation
# because both are now handled by FR with our custom reply message hurrahhhhh zaib.
#set -x
USERNAME=$1
PASS=$2
MAC=$3
MYSQL_USR="root"
MYSQL_PASS="ROOT-OR-SQL-PASS"
DB="radius"
TBL="users"
MSG=""
USRISVALID=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select id from $TBL where username ='$USERNAME';" | tr "'" " "`
if [ -z "$USRISVALID" ];then
echo "$USERNAME >>>> User Not Found."
exit 1
fi
CHKPASS=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select password from users where username='$USERNAME';"`
if [ "$PASS" != "$CHKPASS" ];then
echo "$USERNAME / $PASS >>> Incorrect Password."
exit 1
fi
ENORDIS=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select enabled from $TBL where username ='$USERNAME';" | tail -n 1`
if [ "$ENORDIS" == "0" ];then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'disabled-pool'",Session-Timeout="'0'"'
exit 0
fi
CHECKMAC=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select mac from $TBL where username ='$USERNAME';" | tail -n 1`
if [ -z "$CHECKMAC" ];then
mysql -u$MYSQL_USR -p$MYSQL_PASS -e "use $DB; UPDATE $TBL SET mac ='$MAC' WHERE users.username ='$USERNAME' LIMIT 1;"
fi
if [ "$MAC" != "$CHECKMAC" ];then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'invalidmac-pool'",Session-Timeout="'0'"'
exit 0
fi
SECONDS=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select time_to_sec(TIMEDIFF (expiration, NOW() ) ) as secs from users where username ='$USERNAME';" | tail -n 1`
if [ "$SECONDS" -lt 0 ]; then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'expired-pool'",Session-Timeout="'0'"'
exit 0
fi
POOL=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select pool as pool from $TBL where username ='$USERNAME';" | tail -n 1`
BWPKG=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select bwpkg as bwpkg from $TBL where username ='$USERNAME';" | tail -n 1`
echo 'Framed-Pool="'$POOL'",Session-Timeout="'$SECONDS'",Mikrotik-Rate-Limit="'$BWPKG'",'
exit 0
fi

EXAMPLE:

After all is configured properly, we can see the results as following.

>>> To test with the script it self

root@testradius:/temp# ./auth.sh MR.PONKA PASS 00:0C:29:35:F8:2F
MR.PONKA >>>> User Not Found... *****

root@testradius:/temp# ./auth.sh zaib PASS 00:0C:29:35:F8:2F
Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",

root@testradius:/temp# ./auth.sh zaib PASS 11:11:11:11:11:11
Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"

root@testradius:/temp# ./auth.sh test PASS 00:0C:29:35:F8:2F
Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"

>>> To test from RADCLIENT ,

we can use the following radclient syntax to test username/pass/mac.

echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth RADIUS_SECRET

If user not found

Sending delayed reject for request 2
Sending Access-Reject of id 32 to 127.0.0.1 port 57901
 Reply-Message = "Username not found"
Waking up in 4.9 seconds.

If user is connecting from other device [mac binding scenario]

+- entering group post-auth {...}
Exec-Program output: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"
Exec-Program-Wait: value-pairs: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 113 to 127.0.0.1 port 50894
Mikrotik-Rate-Limit = "1k/1k"
Framed-Pool = "invalidmac-pool"
Session-Timeout = 0

If user account is expired…

+- entering group post-auth {...}
Exec-Program output: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"
Exec-Program-Wait: value-pairs: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 176 to 127.0.0.1 port 36544
Mikrotik-Rate-Limit = "1k/1k"
Framed-Pool = "expired-pool"
Session-Timeout = 0

& IF all is OK, & account is valid …. then user will be able to connect with his assigned profile present in the users table …

+- entering group post-auth {...}
Exec-Program output: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program-Wait: value-pairs: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 0 to 127.0.0.1 port 37090
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"

RADPOSTAUTH mysql query section

Following is RADPOSTAUTH Table to accommodate modified message.

mysql>describe radpostauth;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | | | |
| pass | varchar(64) | NO | | | |
| mac | varchar(18) | NO | | NULL | |
| nasipaddress | varchar(16) | NO | | NULL | |
| reply | varchar(200) | NO | | | |
| authdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| reason | varchar(100) | NO | | NULL | |
+--------------+--------------+------+-----+-------------------+-----------------------------+

MYSQL QUERY:

This is mysql query which will be executed on every login attempt. Either success or failed. Replace existing or modify as required.

cat /etc/freeradius/sql/mysql/dialup.conf
postauth_query = "INSERT into ${postauth_table} (username, pass, mac, nasipaddress, reply, authdate, reason) values ('%{User-Name}', '%{User-Password:-Pap-Password}', '%{Calling-Station-Id}', '%{NAS-IP-Address}', '%{reply:Packet-Type}', NOW(), '%{reply:Reply-Message}')"

Now restart your freeradius service in DEBUG mode

freeradius -X

& issue following commands in other terminal window

# echo "User-Name = zaib1, Password = wrongpass, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 30, code 3, length = 40
Reply-Message = "Username not found"

Total approved auths: 0
Total denied auths: 1
Total lost auths: 0

# echo "User-Name = zaib, Password = wrongpass, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 77, code 3, length = 47
Reply-Message = "Wrong Password"

Total approved auths: 0
Total denied auths: 1
Total lost auths: 0

# echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 121, code 2, length = 58
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"

Total approved auths: 1
Total denied auths: 0
Total lost auths: 0

Now look at the debug window

& you will get

# debug for USERNAME not found request

# debug for WRONG Username

Sending delayed reject for request 13
Sending Access-Reject of id 30 to 127.0.0.1 port 46089
Reply-Message = "Username not found"

# debug for WRONG Password

Sending Access-Reject of id 77 to 127.0.0.1 port 41764
Reply-Message = "Wrong Password"
Waking up in 4.9 seconds.
Cleaning up request 14 ID 77 with timestamp +666
Ready to process requests.

# debug for SUCCESSFUL request

[pap] login attempt with password "zaib"
[pap] Using clear text password "zaib"
[pap] User authenticated successfully
++[pap] returns ok
# Executing section post-auth from file /etc/freeradius/sites-enabled/default
+- entering group post-auth {...}
Exec-Program output: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program-Wait: value-pairs: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 121 to 127.0.0.1 port 46954
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"
Finished request 15.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 15 ID 121 with timestamp +675
Ready to process requests.

Now look @ the radpostauth table


Alhamdolillah !

 

December 15, 2017

Daily report for backup folder via email

Filed under: Linux Related — Tags: — Syed Jahanzaib / Pinochio~:) @ 9:23 AM

This is just for my personnel reference, If you like to use it, just modify whatever is required.

I made this script to get a daily report for support staff so that they can monitor if backup’s are being performed properly & regularly. The backup scheduler make backup after every 2 hours. so there should be 12 backup files in /backup folder per day. This script just query them. I used somewhat some unusual method to acquire the files date, BUT there are other proper methods to get the same , dueto some urgency i just made whatever worked for me. Will update it if any changes will be required.


the Script !

mkdir /temp
touch /temp/bkpreport.sh
chmod +x  /temp/bkpreport.sh
nano /temp/bkpreport.sh

& paste following …


#!/bin/bash
# This bash script is made to send report for files that were created in /backup folder by another scheduler
# I made this to ensure that backups are done properly with automated reports including some other system level information.
# 12-DEC-2017
# Syed Jahanzaib / aacable @ hotmail . com
# https://aacable . wordpress . com
#set -x

#MYSQL Related Details
HOSTNAME=`hostname`
MYSQLID="root"
MYSQLPASS="SQLPASS"
SQL_ACCOUNTING_TABLE="radacct"
GMAILID="YOURGMAIL@gmail.com"
GMAILPASS="GMAILPASS"
ADMINMAIL1="TO1@hotmail.com"
COMPANY="ZAIB Ltd."
#GMAILS MTP
SMTP="64.233.184.108:587"

# Script will search this folder for the files
BKPFOLDER="/backup"
MAILFILE="/tmp/bkpdir.log"
#Set Human Friendly Date Format for yesterday & Display
DATE=`date`
TODAY=`date +"%d-%b-%Y"`
YESTERDAY=`date +"%d-%b-%Y" -d '-1 days'`
MAILSUB="$COMPANY RADIUS Backup Rerport for $YESTERDAY"
# Count number of files
LIST=`ls -lh $BKPFOLDER --time-style=+"%d-%b-%Y" |grep $YESTERDAY`
TOTFILES=`ls -lh $BKPFOLDER --time-style=+"%d-%b-%Y" |grep $YESTERDAY |wc -l`

# disk we want to monitor, make sure to change this
# You need to adjust this , to match your drive, use df -h & modify this
DISK="/dev/mapper/vg_myradius-lv_home"

# Get DB size in MB
MYSQLDBSIZE=`mysql -u$MYSQLID -p$MYSQLPASS --skip-column-names -e "SELECT table_schema "radius", sum(data_length + index_length)/1024/1024 FROM information_schema.TABLES WHERE table_schema='radius' GROUP BY table_schema;" | cut -f1 -d"." | sed 's/[^0-9]*//g'`
DISKTOT=`df -h $DISK |awk '{print $2}'| sed -n 3p`
DISKUSED=`df -h $DISK |awk '{print $3}'| sed -n 3p`
DISKAVA=`df -h $DISK |awk '{print $4}'| sed -n 3p`
DISKUSEPER=`df -h $DISK |awk '{print $5}'| sed -n 3p`
MEMTOT=`free -m |awk '{print $2}'| sed -n 2p`
MEMUSED=`free -m |awk '{print $3}'| sed -n 2p`
MEMAVA=`free -m |awk '{print $4}'| sed -n 2p`
MEMUSEDPER=`free -m | grep Mem | awk '{print $3/$2 * 100.0}'`
MEMAVAPER=`free -m | grep Mem | awk '{print $4/$2 * 100.0}'`
IPADD=`ifconfig | awk -v RS="\n\n" '{ for (i=1; i<=NF; i++) if ($i == "inet" && $(i+1) ~ /^addr:/) address = substr($(i+1), 6); if (address != "127.0.0.1") printf "%s\t%s\n", $1, address }'`
SESSIONS=`mysql -u$MYSQLID -p$MYSQLPASS --skip-column-names -e "use radius; SELECT username FROM $SQL_ACCOUNTING_TABLE WHERE acctstoptime IS NULL;" |wc -l`
FOOTER="This automated report is generated on $DATE
Powered by $COMPANY
Coding by Syed.Jahanzaib / aacable at hotmail dot com"

echo "Daily report for $HOSTNAME System @ COMPANY.

$TOTFILES backup sets were made with intervale of 2 hours on $YESTERDAY

List of files that were backuped $YESTERDAY...

$LIST

OTHER DETAILS: JUST FYI,

===============
NETWORK DETAILS:
===============
Hostname = $HOSTNAME
Network IP Details
$IPADD

==========================
RADIUS SERVER MYSQL REPORT:
==========================
MYSQL 'RADIUS' DB SIZE = $MYSQLDBSIZE MB
RADIUS Online Users = $SESSIONS Users / [at the time of this script running, generaly 00:00 Hours / Midnight]

====================
DIS / STORAGE REPORT:
====================
Total Disk Space = $DISKTOT
Total Disk Space Used = $DISKUSED
Total Disk Space Available = $DISKAVA
Total Disk Space = $DISKUSEPER

==============
MEMORY REPORT:
==============
Total RAM = $MEMTOT MB
Total RAM Used = $MEMUSED MB
Total RAM Available = $MEMAVA MB
Total RAM Used Percent = $MEMUSEDPER %
Total RAM Available Percent = $MEMAVAPER %

$FOOTER" >$MAILFILE

#Display Results in terminal
cat $MAILFILE

# Send results via email to $ADMINMAIL1
sendemail -t $GMAILID -u "$MAILSUB" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MAILFILE -o message-content-type=text

Sample Email !

Click to enlarge

Regard’s
Syed Jahanzaib

November 22, 2017

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

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 9:25 AM

Fix Read Only Error


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

November 10, 2017

Centralized Syslog-ng logging to MySql DB

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 11:59 AM

configure-centralized-syslog-server-2.jpg


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

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

 


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]


syslog-ng sample file

Note: Make sure to modify/add/remove entries as  per your requirements.

@version: 3.5
@include "scl.conf"
@include "`scl-root`/system/tty10.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.info"); };
destination d_mailwarn { file("/var/log/mail.warn"); };
destination d_mailerr { file("/var/log/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(`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/*.conf"
source s_net { udp (); };

# Add Mikrotik 1 Host
filter f_mikrotik { host( "10.0.0.1" ); };
# Add 2nd Mikrotik 2 Host
filter f_mikrotik2 { host( "10.0.0.2" ); };
# Add Cisco Switch
filter f_ciscoswnoc { host( "10.0.0.5" ); };
# Add 2nd Cisco Switch
filter f_ciscosw2 { host( "10.0.0.4" ); };
# Define local files where log for this specific host will be created
destination d_mikrotik { file("/var/log/mikrotik/$HOST.mikrotik.${YEAR}.${MONTH}.${DAY}.log"); };
# Define local files where log for this specific host will be created
destination d_mikrotik2 {
file("/var/log/mikrotik/$HOST.mikrotik.${YEAR}.${MONTH}.${DAY}.log"); };
# Define local files where log for this specific host will be created
destination d_ciscoswnoc { file("/var/log/mikrotik/$HOST.ciscosw.${YEAR}.${MONTH}.${DAY}.log"); };
# Define local files where log for this specific host will be created
destination d_ciscosw2 { file("/var/log/mikrotik/$HOST.ciscosw.${YEAR}.${MONTH}.${DAY}.log"); };

# Define Action to Log file 'from - to '
log { source(s_net); filter(f_mikrotik); destination(d_mikrotik); };
log { source(s_net); filter(f_mikrotik2); destination(d_mikrotik2); };
log { source(s_net); filter(f_ciscoswnoc); destination(d_ciscoswnoc); };
log { source(s_net); filter(f_ciscosw2); destination(d_ciscosw2); };

# Define mysql
source s_mysql {
udp(port(514));
tcp(port(514));
};
destination d_mysql {
sql(type(mysql)
host("localhost")
username("root")
password("MYSQL_PASSWORD")
database("syslog")
table("logs")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
indexes("datetime", "host", "program", "msg")
);
};

# Define logging to mysql , like from - to mysql
log {
source(s_net);
filter(f_mikrotik);
destination(d_mysql);
};

# Define logging to mysql , like from - to mysql
log {
source(s_net);
filter(f_ciscoswnoc);
destination(d_mysql);
};

# Define logging to mysql , like from - to mysql
log {
source(s_net);
filter(f_ciscosw2);
destination(d_mysql);
};
# Syed Jahanzaib / aacable at hotmail dot com

Save & Exit.


MYSQL DB to store syslog-ng logs

Create DB in mysql where are logs will be stored.

LOGIN to mysql & create DB

mysql-uroot -pMYSQL_OR_ROOT_PASS
#Create DB / Syed Jahanzaib
create 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(15) 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-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
#!/bin/bash
if [ ! -e /var/log/mysql.pipe ]
then
mkfifo /var/log/mysql.pipe
fi
while [ -e /var/log/mysql.pipe ]
do
mysql -u root --password=MYSQL_OR_ROOT_PASSWORD syslog < /var/log/mysql.pipe >/dev/null
done

Save & Exit.

Add it in startup like /etc/rc.local (before exit line)
or run manual like mysql-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 '
<table width="1400" border="1" align="left" cellpadding="1" cellspacing="1">';
echo '
<tr>
<th>ID</th>
<th>Host</th>
<th>Date</th>
<th>Info</th>
<th>Type</th>
<th>Messgae</th>
</tr>
';
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 "
<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>
";
}else if($host_column=='101.11.11.36'){
echo "
<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>
";
}else if($host_column=='101.11.12.225'){
echo "
<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>
";
}
}
echo '</table>
';
mysql_close();
?>

resulkt


– 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

sample


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.


Regard’s
Syed Jahanzaib


dua.jpg

October 2, 2017

Safest method to clean /boot partition

Filed under: Linux Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:37 AM

Today morning ,when I tried to upgrade one of remote Ubuntu kernel I received error stating that /boot partition is full.

If your /boot partition usage goes to 100% (or near about) as showed in the image below, then its a good idea to make some room in in order to perform kernel upgrade.

boot part full.JPG

Usually one of safest method is as below …


Note: in this post, I am using Ubuntu 12.4 / 32 bit version.

First check the current kernel version

uname -r

This will show you the current kernel version like below …

root@radius:~# uname -r
3.13.0-112-generic

As we can see that its 3.13.0-112-generic, make a note of it

Now run this command for a list of installed kernels:

dpkg --list 'linux-image*'

This will show you the current & all previous versions of kernels, like below …

kernel list.JPG

just delete the old kernels (marked in red) that we dont require anymore. Use following command

sudo apt-get remove linux-image-VERSION

Replace VERSION with the version of the kernel you want to remove.

WARNING: Make sure you dont delete the current running kernel (number acquired by uname -r command)

Once you are done removing all old kernels, issue following command

sudo apt-get autoremove

And finally run this to update grub kernel list

sudo update-grub

Done.

space after removal.JPG


Regard’s
Syed Jahanzaib

June 16, 2017

Quick Notes on moving MySQL database(s) directory to New Partition

Filed under: Linux Related — Syed Jahanzaib / Pinochio~:) @ 4:59 PM

moving

Following are short notes on how you can move mysql db directory to another partition.

Background:

Our Government base telecom authority called PTA (in PK) regularly issues letter to local ISP’s asking them to keep user traffic data for period of minimum one year.

As showed here (Thanks to Mr. Khalid for providing this notice copy)

pta letter

We know that almost all small scale ISP are natting there users by using local dsl or other medium bandwidth, and keeping private IP data traffic is (almost) useless because finding any thing from such data is like finding a needle in the haystack. If OP is assigning users with public ip, then OP should record users public ip assignment only like showed here, but as private natted IP are still being used therefore OP should save users traffic as per law despite of it will almost useless in practical)

In general we can log user traffic by using mikrotik own small scale web proxy, or adding external proxy like SQUID and route all traffic to squid proxy and keep logs there.

But if you are using Radius Manager, we have option of CONNECTION TRACKING. Using this method, we can track all users connection in mysql DB.

c1

c2

Connection Tracking requires lot of disk space for local ISP. Recently I recommend many OP to use SSD disk as SSD disks are more reliable , long life & offer fast disk access with minimal latency, but as SSD are costly therefore as alternative, we can add secondary disk for mysql like 2.TB and move our MySQL DB in this drive, rest OS or RM will operate from our primary SSD.


Scenario:

We have 2 disk in system as follows

  1. 128 GB SSD [Ubuntu 12.4 installed along with radius manager 4.1.5]
  2. 2 TB SATA Disk [Empty & Mounted as /2tb, howto mount disk read this ]

So our requirement is to move MySQL DB to this 2 TB disk.


Quick Cmd’s …

First login to MySQL and see your current Data Directory location.


mysql> select @@datadir;
+-------------+
| @@datadir |
+-------------+
| /var/lib/mysql |
+-------------+
1 row in set (0.00 sec)

Now we need to move this folder to our new 2tb. Follow below ,,,


Stop MySQL Service & Moves files to 2 TB Disk

sudo service mysql stop

Copy mysql DB Data directory to our 2tb disk with permissions intact, this part is crucial, pay attention to this section. We will use RSYNC to have same permission level in new folder.

sudo rsync -av /var/lib/mysql /2tb/

Rename current MySQL DB directory /var/lib/mysql to .bak [for backup purposes so that in case any thing goes wrong , we still have this restore point]

sudo mv /var/lib/mysql /var/lib/mysql.bak

Change PATH in MySQL INF file

Edit mysql inf file to change the DB directory

sudo nano /etc/mysql/my.cnf

in this file, find DATADIR line and change the old path to new one As showed below …

datadir = /2tb/mysql

Save & Exit


Apparmor Section [for Ubuntu OS]:

Allow new folder in APPARMOR (if you will skip this, you will get access / permission  errors)

sudo nano /etc/apparmor.d/tunables/alias

at the bottom add this line

alias /var/lib/mysql/ -> /2tb/mysql/,

Save n Exit.

It’s also recommended to disable SELINUX.


Start MySQL & Test

Now start the mysql service

service mysql start

& if all ok you may see following …

mysql start/running, process 1881

further verify it with process check

root@radius:~# ps aux |grep mysql
mysql 1881 0.1 3.9 328928 40536 ? Ssl 16:09 0:00 /usr/sbin/mysqld

Login to my mysql and verify all db/tables showing ok


root@radius:~# mysql -uroot -pMYSQLPASSWORD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 964
Server version: 5.5.54-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@datadir;
+-------------+
| @@datadir |
+-------------+
| /2tb/mysql/ |
+-------------+
1 row in set (0.00 sec)

mysql>

🙂


Regard’s
Syed Jahanzaib

January 6, 2017

Gathering Stats from remote Windows via Linux Shell

Filed under: Linux Related, Uncategorized — Tags: , , , , , , , — Syed Jahanzaib / Pinochio~:) @ 2:43 PM

Reference Post:

Following are few simple methods to query information for various instances like remote windows service status , performance monitor instance result with trimming , , execute commands on remote windows box , all being done from our beloved Linux boX 😉

I must admit that even after spending years in this field, I still feel myself very doodle, blockhead & light brain in almost every topic or subject I get confronted with ! STML plays an important role in my Deficiency  ‘_’    – 😉

ots1087__97717-1410905363-1280-1280


Executing command on remote windows server, and get its result in output

$WINEXE --user=$DOMAIN/$ADMINID%$ADMINPASS //$SERVERIP "C:\TEMP\COMMAND.EXE -syntax-if-any"

Note: above command requires WINEXE tool (Linux tools to execute command on remote windows)

Querying Remote Windows Performance Monitor Instances

Example, we have Forefront TMG 2010 and we want to see its Cache Hit % from our linux box shell, so we can use following command (It was real hard to escape nested double quotes :O )

This is very very useful command and it took few hours for me to trim the required result for plotting graph.

winexe -U domain/admin%"password" //MYSERVER 'typeperf -sc 1 -si 1 "\\MYSERVER\Forefront TMG Web Proxy\Cache Hit Ratio (%)"'

and with bash script I used it like

root@linux:/temp# cat tmg-cachehit.sh

#!/bin/bash
# Script to query TMG cache HIT after trimming
#set -x
IP="10.0.0.1"
DOMAIN="MYDOMIN"
ID="ADMIN"
PASS="PASSWORD"
TMP_HOLDER="/tmp/$IP.cache.hit.txt"
winexe -U $DOMAIN/$ID%"$PASS" //$IP 'typeperf -sc 1 -si 1 "\\101.11.11.6\Forefront TMG Web Proxy\Cache Hit Ratio (%)"' > $TMP_HOLDER
RESULT=`cat $TMP_HOLDER | sed -n 3p | awk '{print $2}' | cut -d "," -f 2 | tr -d '"' | cut -f1 -d"."`
echo $RESULT
echo $RESULT

Result:

tmg-cache-hit


Check remote windows service status

Example if we want to query service status result of Lotus domino mail server  from our linux box …

root@linux:/temp# net rpc service status "Lotus Domino Server (DLotusDominodata)" -I 10.0.0.1 --user=DOMAIN/ADMINID%PASSWORD

RESULT:

Lotus Domino Server (DLotusDominodata) service is running.
Configuration details:
Controls Accepted = 0x5
Service Type = 0x110
Start Type = 0x2
Error Control = 0x0
Tag ID = 0x0
Executable Path = "X:\Lotus\nservice.exe" "=X:\Lotus\notes.ini" "-jc" "-c"
Load Order Group =
Dependencies = /
Start Name = LocalSystem
Display Name = Lotus Domino Server (DLotusDominodata)

Allah Shuker


I used all above commands in various script for alerts and mrtg graphing. you can use it to fulfill any customized requirements.

Regard’s
Syed Jahanzaib

Older Posts »

%d bloggers like this: