use iptables to manage firewalling of ubuntu 16.04 bridge on VMware

背景: IDC分配了大量IP地址,但是自己并不能做网关,但我又不能把所有的机器全部直接置于外网,增加管理成本。
方案:
使用ubuntu 16.04做防火墙(FW)
外网(internet)只连接FW的ens160。
增加DMZ网络(DMZ),所有的需要外网IP的机器和FW的ens192全部连接到DMZ。

在FW中安装bridge-utils并桥接ens160与ens192,不知道怎么桥接的请直接参考这里

接下来就是在VMware上启用DMZ和internet的混杂模式(Promiscuous Mode)。记住,两个网络的混杂模式都必须开启。不知道怎么开启混杂模式的可以看这里。这里没必要开启整个vSwitch的混杂模式,只需要开启DMZ和internet端口组就足够了。

到了这步,你的桥接就做完了。这个时候,你DMZ里面所有的机器都有完全的外网访问权限,你在iptables里面设置的规则完全限制不了DMZ的网络。接下来就是启用iptables对bridge的限制。

运行下面命令启用bridge netfilter模块

modprobe br_netfilter

修改 /etc/modules-load.d/modules.conf,在最后加上下面一行,这样机器在重启后也会自动加载bridge netfilter模块

br_netfilter

再修改 /etc/sysctl.conf,在文件的最后添加下面几行:

###################################################################
# For iptables to manage bridge firewalling
net.bridge.bridge-nf-call-ip6tables = 1
net.bridge.bridge-nf-call-iptables = 1
net.bridge.bridge-nf-call-arptables = 1

最后,运行下面命令使配置生效

sysctl -p

现在,就可以使用iptables对DMZ进行管理了。
不过,我目前还没摸清这里面进出端口(iptables里面的-i和-o)的规则,只能直接使用IP了。

How to remove the read failed after 0 of 4096 I/O error

from: https://freeshell.de/~jose/317/kludges/how-to-temove-the-read-failed-after-0-of-4096-io-error/

Recently in a very old server I had to remove a disk. My server was composed by two physical volumes: a RAID5 volume, shown as /dev/sda to the operating system by the SCSI controller, and a single hard disk as /dev/sdb.

My /dev/sdb, since there is no way to have a new disk with the same geometry, I decided to remove it.
The bios utility of my SCSI controller is quite strange and I cannot remove my une-disk logical volume without remove all the configuration.

root@arch:~# pvdisplay
  /dev/sdb: read failed after 0 of 4096 at 0: input/output Error
  /dev/sdb: read failed after 0 of 4096 at 146695716864: input/output Error
  /dev/sdb: read failed after 0 of 4096 at 146695774208: input/output Error
  /dev/sdb: read failed after 0 of 4096 at 4096: input/output Error
  — Physical volume —
  PV Name               /dev/sda1
  VG Name               vg_system
  PV Size               838,12 GiB / not usable 2,00 MiB
  Allocatable           yes
  PE Size               4,00 MiB
  Total PE              214559
  Free PE               21184
  Allocated PE          193375
  PV UUID               ZQQwAs-yGgP-LZXk-3cTy-yaOb-gijr-bnUCz4

So I’d better to leave my controller untouched and tell my linux CentOS to forget the /dev/sdb disk.

root@arch:~# echo 1 > /sys/block/sdb/device/delete

How to install a OpenVPN System Based On User/Password Authentication with mysql & Day Control (libpam-mysql)

Source Page: http://sysadmin.compxtreme.ro/how-to-install-a-openvpn-system-based-on-userpassword-authentication-with-mysql-day-control-libpam-mysql/

I have added some notes through the article.

here comes the article:

This document describes how to install a OpenVPN server with User/Password authentication with mysql and day control using libpam-mysql. This will be a brief, but a very practical document.

Install mysql server

apt-get install mysql-server

Create a mysql user and a database to be used later

Create user

mysql -u root -p
CREATE DATABASE openvpn;
USE openvpn;

create database

CREATE USER 'openvpn'@'localhost' IDENTIFIED BY 'lNPg5TAIy82zFpEn';
GRANT ALL PRIVILEGES ON `openvpn`.* TO 'openvpn'@'localhost';
FLUSH PRIVILEGES;

Create user table

CREATE TABLE IF NOT EXISTS `user` (
    `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `user_pass` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1234',
    `user_mail` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_start_date` date NOT NULL,
    `user_end_date` date NOT NULL,
    `user_online` enum('yes','no') NOT NULL DEFAULT 'no',
    `user_enable` enum('yes','no') NOT NULL DEFAULT 'yes',
PRIMARY KEY (`user_id`),
KEY `user_pass` (`user_pass`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create log table

CREATE TABLE IF NOT EXISTS `log` (
    `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `log_trusted_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_trusted_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_remote_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_remote_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `log_end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `log_received` float NOT NULL DEFAULT '0',
    `log_send` float NOT NULL DEFAULT '0',
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Install OpenVPN

apt-get install openvpn

Generate keys

apt-get install openssl

Copy certificate generate file from example

cp -R /usr/share/doc/openvpn/examples/easy-rsa /etc/openvpn/.
cd /etc/openvpn/easy-rsa/2.0/

Replace some options (I have no idea why author want to do this)

sed -i -e 's/--interact //g' build-key

search and replace the following values in /etc/openvpn/easy-keys/2.0/vars

vim vars
export KEY_SIZE=2048

export KEY_COUNTRY="SE"
export KEY_PROVINCE="SE"
export KEY_CITY="STOCKHOLM"
export KEY_ORG="Company Name"
export KEY_EMAIL="email@example.org"
export KEY_CN=vpn.example.org
export KEY_NAME=operations
export KEY_OU=operations
export PKCS11_MODULE_PATH=changeme
export PKCS11_PIN=1234

save and exit

Disable unqiue subject

sed -i -e 's/unique_subject = yes/unique_subject = no/g' /etc/openvpn/easy-rsa/2.0/keys/index.txt.attr

Start to bulid certificates

source ./vars
./clean-all
./build-ca
./build-key-server vpn.example.org
./build-dh
cp -a keys /etc/openvpn/.

Install libpam-mysql and setup pam authentication based on it

apt-get install libpam-mysql

Add a new file inside pam

touch /etc/pam.d/openvpn

Paste the following content into the file we just created

auth sufficient pam_mysql.so user=openvpn passwd=lNPg5TAIy82zFpEn host=localhost db=openvpn [table=user] usercolumn=user.user_id passwdcolumn=user.user_pass [where=user.user_enable=1 AND user.user_start_date!=user.user_end_date AND TO_DAYS(now()) >= TO_DAYS(user.user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user.user_end_date) OR user.user_end_date='0000-00-00')] sqllog=0 crypt=0
account required pam_mysql.so user=openvpn passwd=lNPg5TAIy82zFpEn host=localhost db=openvpn [table=user] usercolumn=user.user_id passwdcolumn=user.user_pass [where=user.user_enable=1 AND user.user_start_date!=user.user_end_date AND TO_DAYS(now()) >= TO_DAYS(user.user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user.user_end_date) OR user.user_end_date='0000-00-00')] sqllog=0 crypt=0

Create scripts to log OpenVPN access activity

mkdir /etc/openvpn/scripts/ && cd $_

Create the following file

vim /etc/openvpn/scripts/config.sh
#!/bin/bash
##Dababase Server
HOST='127.0.0.1'
#Default port = 3306
PORT='3306'
#Username
USER='openvpn'
#Password
PASS='lNPg5TAIy82zFpEn'
#database name
DB='openvpn'
vim /etc/openvpn/scripts/connect.sh
#!/bin/bash
. /etc/openvpn/scripts/config.sh
##insert data connection to table log
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "INSERT INTO log (log_id,user_id,log_trusted_ip,log_trusted_port,log_remote_ip,log_remote_port,log_start_time,log_end_time,log_received,log_send) VALUES(NULL,'$common_name','$trusted_ip','$trusted_port','$ifconfig_pool_remote_ip','$remote_port_1',now(),'0000-00-00 00:00:00','$bytes_received','$bytes_sent')"
##set status online to user connected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online='yes' WHERE user_id='$common_name'"
vim /etc/openvpn/scripts/disconnect.sh
#!/bin/bash
. /etc/openvpn/scripts/config.sh
##set status offline to user disconnected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online='no' WHERE user_id='$common_name'"
##insert data disconnected to table log
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE log SET log_end_time=now(),log_received='$bytes_received',log_send='$bytes_sent' WHERE log_trusted_ip='$trusted_ip' AND log_trusted_port='$trusted_port' AND user_id='$common_name' AND log_end_time='0000-00-00 00:00:00'"

Change the permission of the files we just created

chmod 755 /etc/openvpn/scripts/*.sh

Create the password file for accessing OpenVPN management interface via telnet

echo "wYYoFlaQa8nGQoO8" > /etc/openvpn/pw-management-file
chmod 600 /etc/openvpn/pw-management-file

Configure OpenVPN

vim /etc/openvpn/vpn.example.org.conf
##general settings
port 1194
proto udp
dev tun

##keys
ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/vpn.example.org.crt
key /etc/openvpn/keys/vpn.example.org.key
dh /etc/openvpn/keys/dh2048.pem

##FIXME: ip for the clients
server 10.0.1.0 255.255.255.0
ifconfig-pool-persist ipp.txt
##FIXME: routes pushed to the client
push "route 172.16.1.0 255.255.255.0"
push "route 10.0.0.0 255.0.0.0"
push "route 195.248.229.19 255.255.255.255"

##Auth
comp-lzo
user nobody
#group nogroup
client-to-client
#client-cert-not-required
username-as-common-name

##user/pass auth from mysql
plugin /usr/lib/openvpn/openvpn-auth-pam.so openvpn

##script connect-disconnect
script-security 3 system
client-connect /etc/openvpn/scripts/connect.sh
client-disconnect /etc/openvpn/scripts/disconnect.sh

##management
management localhost 1194 pw-management-file

keepalive 10 120
persist-key
persist-tun
status status.log
verb 3

Start OpenVPN

/etc/init.d/openvpn start

Note: I also created a script to manage OpenVPN users. If you would like to have it, please post a comment.
Note2: feel free to use my user management script! I am pretty sure it has a lot of bugs and things to improve!!! Use it on your own risk!

the 2 notes are written by original author.

webpage to statistic OTRS time

最近好无聊啊,被老板要求统计OTRS里面所用的时间,OTRS本身的PDF报靠可读性不是很友好,而且,这个时间统计本身是不支持的,就开始自己动手写一个了。。。

由于SQL比较长,我就先做了几个视图。。。

time_stats视图(所有时间):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`)) group by `u`.`id`,`t`.`type_id`

time_stats_3mons视图(最近三个月内):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_3mons` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (`ta`.`create_time` between (now() - interval 3 month) and now())) group by `u`.`id`,`t`.`type_id`

time_stats_6mons视图(最近6个月内):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_6mons` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (`ta`.`create_time` between (now() - interval 6 month) and now())) group by `u`.`id`,`t`.`type_id`

time_stats_lastmon视图(上个月):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_lastmon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format((curdate() - interval 1 month),'%y-%m'))) group by `u`.`id`,`t`.`type_id`

time_stats_lastweek视图(上周):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_lastweek` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (yearweek(`ta`.`create_time`,0) = (yearweek(now(),0) - 1))) group by `u`.`id`,`t`.`type_id`

time_stats_thismon视图(本月):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_thismon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format(now(),'%y-%m'))) group by `u`.`id`,`t`.`type_id`

time_stats_thisweek视图(本周):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_thismon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format(now(),'%y-%m'))) group by `u`.`id`,`t`.`type_id`

然后就是php代码。。。。

<?php
        $remote_ip = $_SERVER["REMOTE_ADDR"];
        $page = $_SERVER["SCRIPT_NAME"];
        $ips=array(
                        '10.0.0.1',        //Sample King IP
                        '10.0.0.2',      //Brutex IP
                        '10.0.0.3',
                );
        if(!in_array($remote_ip,$ips)){
                die("<h1>Permession Denied!</h1>");
        }
?>


<?php

        echo "<!DOCTYPE html>\n";
        echo "<html>\n";
        echo "<head>\n";
        echo "<title>OTRS Time Stats</title>\n";
        echo "<style>\ntable,th,td\n{\n\tborder:1px solid black;\n\tborder-collapse:collapse\n}\n</style>\n";
        echo "</head>\n";
        echo "<body>\n";

?>

<?php
        $times = array(
                        "time_stats_thisweek" => "This week",
                        "time_stats_lastweek" => "Last week",
                        "time_stats_thismon" => "This month",
                        "time_stats_lastmon" => "Last month",
                        "time_stats_3mons" => "Latest 3 months",
                        "time_stats_6mons" => "Latest 6 months",
                        "time_stats" => "All time",
                );

        $timeperiod = $_POST['timeperiod'];
        if(!array_key_exists($timeperiod,$times)){
                echo "<h2>Select a time period you want to query:</h2>\n";
                echo "<form action=\"$page\" method=\"post\">\n";
                echo "<select name=\"timeperiod\">\n";
                foreach($times as $key => $val){
                        echo "\t\t<option value=\"$key\">".$val."</option>\n";
                }
                echo "\t</select>\n";
                echo "<input type=submit>\n</form>";
        }else{
?>

<?php

        $tablename = $timeperiod;

        $conn = @mysql_connect("localhost","readonly","readonly") or die("Cannot connect to DB");
        mysql_select_db("otrs",$conn);
        $user_list = mysql_query("select distinct name from $tablename order by uid",$conn);
        while($row = mysql_fetch_array($user_list)){
                //$newuids[] = $row['uid'];
                $newusers[] = $row['name'];
        }
        $type_list = mysql_query("select distinct type,tid from $tablename order by tid",$conn);
        while($row = mysql_fetch_array($type_list)){
                $newtids[] = $row['tid'];
                $newtypes[] = $row['type'];
        }

        echo "<h3>Time Statistics for <em><font color=red>".$times["$tablename"]."</font></em></h3>\n";
        echo "<table border=1>\n\t<tr><td>&nbsp;</td>";
        foreach($newtypes as $val){
                echo "<td>".$val."</td>";
        }
        echo "<td><b>Sum</b></td></tr>\n";

        foreach($newusers as $val){
                echo "\t<tr><td>$val</td>";
                $timesum = 0;
                foreach($newtids as $val2){
                        $time_res = mysql_query("select minutes from $tablename where tid=$val2 and name='$val'",$conn);
                        #$time_res = mysql_query("select round(minutes/60,1) as minutes from $tablename where tid=$val2 and name='$val'",$conn);
                        if(!time_res){
                                echo "<td>&nbsp;</td>";
                        }else{
                                $time = mysql_result($time_res,0);
                                echo "<td>".round($time/60,1)."</td>";
                                $timesum = $timesum+$time;
                        }
                }
                echo "<td>".round($timesum/60,1)."</td></tr>\n";
        }

        echo "\t<tr><td><b>Total</b></td>";
        foreach($newtids as $val){
                $time_res = mysql_query("select round(sum(minutes)/60,1) as minutes from $tablename where tid=$val",$conn);
                if(!time_res){
                        echo "<td>&nbsp;</td>";
                }else{
                        $time = mysql_result($time_res,0);
                        echo "<td>".$time."</td>";
                }
        }
        $timesum_res = mysql_query("select round(sum(minutes)/60,1) as minutes from $tablename",$conn);
        $timesum = mysql_result($timesum_res,0);
        echo "<td>".$timesum."</td></tr>\n";

        echo "</table>\n";
        echo "<br>\n";
        echo "<div><a href=\"".$page."\">Back</a></div>\n";
        mysql_close($conn);
?>

<?php
        }
?>
<?php
        echo "</body>\n";
        echo "</html>\n";
?>

php script to monitor OTRS queue size from SNMP

最近公司上线OTRS,感觉还不错,顾问居然还给了数据库的结构图,闲下来就写了这个php脚本给Opsview用来监控OTRS支持队列的大小。别问我为什么用php…因为我实在是不会pel….

测试环境:

DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=12.04
DISTRIB_CODENAME=precise
DISTRIB_DESCRIPTION="Ubuntu 12.04.4 LTS"

废话不说,先帖脚本:

#!/usr/bin/php
<?php

# Tomas Tang on 18/02/2014
# Check the OTRS support queue size

# Disable error report
error_reporting(0);

# get arguments
$procname = $argv[0];
$queuename = $argv[1];
$warnlevel = $argv[2];
$critilevel = $argv[3];

# keep it for future develop
#$QUEUE = array(
#               "allnew",
#               "escalated",
#               "second_level_support",
#               "first_level_support",
#);


# set query SQL and help
if("$queuename"=='allnew'){
        # all the tickets with state new but not in delete queue
        $sql = "select count(1) from ticket where ticket_state_id=1 and queue_id!=3;";
}elseif("$queuename"=='escalated'){
        # not in use yet, to be finished
        $sql = "select count(1) from ticket where ticket_state_id=1 and queue_id!=3;";
}elseif("$queuename"=='first_level_support'){
        # all tickets which are not (pending to) closed
        $sql = "select count(1) from ticket where ticket_state_id in (1,4,6,11) and queue_id=5;";
}elseif("$queuename"=='second_level_support'){
        # all tickets which are not (pending to) closed
        $sql = "select count(1) from ticket where ticket_state_id in (1,4,6,11) and queue_id=6;";
}else{
        echo "Usage: $procname  <QueueName>  <WarnNumber>  <CriticalNumber>\n";
        echo "      QueueName:      Queueu name to query, right now only in \"allnew\",\"escalated\",\"first_level_support\" and \"second_level_support\"\n";
        echo "      WarnNumber:     Tickets quantity start to be warn, required\n";
        echo "      CriticalNumber: Tickets quantity start to be critical, must greater than WarnNumber, required\n";
        exit(3);
}

# Double check query SQL
if(empty($sql)){
        echo "UNKNOWN - Unknown error\n";
        exit(3);
}

# Check arguments again.
if(!is_numeric("$warnlevel")||!is_numeric("$critilevel")){
        echo "UNKNOWN - Incorrect arguments.\n";
        exit(3);
}elseif($warnlevel>=$critilevel){
        echo "UNKNOWN - Incorrect warn/critical level.\n";
        exit(3);
}

# connect to database
$conn = @mysql_connect("localhost","readonly","readonly") or die("Cannot connect to DB");
mysql_select_db("otrs",$conn);

# fetch data
$result = mysql_query("$sql",$conn) or die("Query failed");
$result = mysql_result($result,0);

# close db
mysql_close($conn);

if ($result>=$critilevel){
        echo "CRITICAL - Queue size is $result, queue is huge!!!\n";
        exit(2);
}elseif($result>=$warnlevel){
        echo "WARN - Queue size is $result, queue is big!\n";
        exit(1);
}else{
        echo "OK - Queue size is $result, queue is OK.\n";
        exit(0);
}

?>

脚本本身没什么好看的,不过最开始用来传参的东西还是蛮好玩的,这种脚本不能跟在web里面一直的传参,只能通过这个叫$argv的数组。

把脚本保存为

/usr/local/bin/check_otrs_queue

修改/etc/snmp/snmpd.conf,加上下面几行

exec otrs_new_tickets /usr/local/bin/check_otrs_queue allnew 15 20
exec first_level_support /usr/local/bin/check_otrs_queue first_level_support 30 50
exec second_level_support /usr/local/bin/check_otrs_queue second_level_support 10 15

最后就是使用check_snmp_exec.sh检测了~~~

How to use gmail as relay host for postfix in Ubuntu 12.04

Reference: https://rtcamp.com/tutorials/linux/ubuntu-postfix-gmail-smtp/

first of all, please note that when the recipient receives your email, the sender will be changed to the gmail account you are going to use

Add the following code in /etc/postfix/main.cf

# Define relay host to gmail
relayhost = smtp.gmail.com:587

# Gmail requires tls connections
smtp_use_tls=yes
smtp_tls_CAfile = /etc/ssl/certs/ca-certificates.crt #Your trusted CA list

# Gmail requires authentication
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd

# define authentication mechanism
smtp_sasl_security_options = noanonymous
smtp_sasl_mechanism_filter = plain
smtp_sasl_tls_security_options = noanonymous

Then, edit your authentication file /etc/postfix/sasl_passwd as the following format:

smtp.gmail.com    USERNAME:PASSWORD

please note that username should be your full mail address, include “@gmail.com” or your own domain name.
then update the file to postfix format for using:

#postmap /etc/postfix/sasl_passwd

remember restart postfix at the last

#service postfix restart

lsiutil to add hot spare drive on line for mpt raid controllers

environment: HP ProLiant ML150 G5 with Ubuntu 8.04 installed

  • Run “mpt-status –i 1” to get the current array information.

    ioc0 vol_id 1 type IM, 2 phy, 231 GB, state DEGRADED, flags ENABLED
    ioc0 phy 0 scsi_id 2 ATA      GB0250C8045      HPG2, 232 GB, state ONLINE, flags NONE
    ioc0 phy 1 scsi_id 9 ATA      GB0250EAFJF      HPG1, 232 GB, state FAILED, flags OUT_OF_SYNC
  • Run “cat /proc/scsi/scsi” to get the available drives information
  • Run “lsiutil”
    • Select the correct port, you can find the information from the 1st step
    • Select 8, and then 16 to confirm the physical drive information, recommended
    • Then select 21 to enter “RAID actions”
    • Select 50 “Create hot spare”
    • You need to figure out the 2 parameters yourselves, I forgot to write down
    • 1st is something like the drive number, you have to compare the information I asked you to collect
    • 2nd is something like the spare group, I suggest you use the same number you use for “mpt-status –i”
    • Then if you select 2 “show physical disks” you will see your hot spare drive
    • Last we run 99 “reset port” to activate the hot spare.
    • Select 0 until you back to shell
  • Run “mpt-status –i 1” again to confirm it is now recovering
    ioc0 vol_id 1 type IM, 2 phy, 231 GB, state DEGRADED, flags ENABLED RESYNC_IN_PROGRESS
    ioc0 phy 2 scsi_id 2 ATA      GB0250C8045      HPG2, 232 GB, state ONLINE, flags NONE
    ioc0 phy 0 scsi_id 3 ATA      GB0250EAFYK      HPG1, 232 GB, state ONLINE, flags OUT_OF_SYNC

Ubuntu 1204 安装NUT监控APC UPS

安装相关程序

apt-get update
apt-get install nut nut-client nut-server

利用udev设定固定的设备文件名

在/etc/udev/rules.d/下面添加52-nut-usb.rules文件,以下是文件内容

# APC - usbhid-ups
SYSFS{idVendor}=="051d", SYSFS{idProduct}=="0002", MODE="664", GROUP="nut", ATTRS{serial}=="ASNNNNNNNNNN", SYMLINK+="ups-test01"

ASNNNNNNNNNN是你的APC UPS序列号,你可以直接在机器上找到,也可以用lsusb -v命令得到(iSerial字段)。

重启udev与ups驱动

udevadm control --reload-rule
udevadm trigger
upsdrvctl start

配置NUT
进入/etc/nut目录。

修改nut.conf文件,将MODE修改为netserver

MODE=netserver

修改ups.conf,在文件最后添加如下内容:

[ups-test01]
        driver = usbhid-ups
        port = /dev/ups-test01
        desc = "UPS-TEST01"
        serial = "ASNNNNNNNNNN"

修改upsd.conf,修改LISTEN字段,把127.0.0.1改为你希望监听的地址,如果监听所有地址,用0.0.0.0

LISTEN 0.0.0.0 3493

修改upsd.users,按如下格式添加用户,这里不详解用户权限了。

[upsclient]
        password = upsclient
        upsmon slave

[upsmaster]
        password = upsmaster
        upsmon master

修改upsmon.conf,添加MONITOR字段

MONITOR ups-test01@localhost 1 upsmaster upsmaster master

重启nut服务之后,ups就已经在监控之中了,你可以用下面的命令查看基本信息了

upsc ups-test01@localhost

添加网页控制页面
直接用命令虽然看的很全面,但是很多时候还是不太方便。nut也提供网页查看方式,设置起来也很方便。

安装相关的软件

apt-get install nut-cgi apache2

修改/etc/nut/hosts.conf,添加如下内容:

MONITOR ups-test01@localhost "UPS-TEST01"

修改apache设置,添加alias到/usr/share/nut下面,然后就可以在网页上查看监控内容了。