Exchange 2016: replace internal name certificate

please read this article first: https://blog.digicert.com/replace-internal-names-certificates-part-2/

But this article is only working for Exchange 2013 only, for Exchange 2016 you will still get a certificate warning even you finished all the steps provided.

and here comes the finally solution:

Reason: MAPI over HTTP function on Exchange 2016, which is new for Exchange 2016 https://technet.microsoft.com/EN-US/library/mt634322(v=exchg.160).aspx

1. login to your EMS (Exchange Management Shell)
2. run this command:

Get-MapiVirtualDirectory |Set-MapiVirtualDirectory -InternalUrl https://external.domain.name/mapi

3. reboot the whole server. (I tried many services but still not work, so I just reboot the whole server)

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.

cisco端口限速

最近老被投诉说无线太慢,追其原因是原来使用squid做速度限制的时候由于不太懂,把限速搞的很低还不起作用,今天花点时间,总算把cisco限速搞定了。

定义access group

access-list 130 permit ip any 10.30.168.0 0.0.0.255
access-list 150 permit ip any 10.50.168.0 0.0.0.255

定义class

class-map match-all BYOD
  match access-group 150
class-map match-all GUEST
  match access-group 130

定义policy

policy-map nonwork
  class BYOD
    police 4000000 1000000 exceed-action drop
    trust dscp
  class GUEST
    police 4000000 500000 exceed-action drop
    trust dscp

应用policy

#conf t
(config)#interface FastEthernet1/0/1
(config-if)#service-policy input nonwork

别问我什么意思,我也是按网上教程做的,我对cisco交换机了解极浅。

exchange management basic

官方资料

查询用户邮箱统计详情:

Get-MailboxStatistics username|fl

给用户分配Mailbox Import Export角色,需要重新登陆后生效,不然无法使用import/export命令:

New-ManagementRoleAssignment -User username -Role "Mailbox Import Export"

导出用户数据到pst文件:

New-MailboxExportRequest -Mailbox username -FilePath \\Server\PSTFileShare\username.pst

查询导出状态(identity可以在导出的时候查到):

Get-MailboxExportRequestStatistics -Identity username\MailboxExport

从pst文件导入用户数据:

New-MailboxImportRequest -Mailbox username -FilePath \\Server\PSTFileShare\username.pst

查询导入状态(identity可以在导入的时候查到)

Get-MailboxImportRequestStatistics -Identity username\MailboxImport

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";
?>

mysqldump at replica master

一直以来,数据库备份就是我最头痛的问题,特别是从库完全挂掉的时候,以前总是人把所有的程序完全停止,现在好了,终算让我看到这个参数了

mysqldump --master-data=1 --all-databases > all-databases.sql

–lock-all-tables:在备份全过程中锁住所有的表,默认不启用。当启用时会自动关闭–single-transaction和–lock-tables参数。

–master-data:当值为0时,什么也不做。当值为1时,在SQL中自动加上CHANGE MASTER命令,当值为2时自动加上CHANGE MASTER命令,但是被注释的,导入时并不会自动执行。启用这个参数时如果没有启用–single-transaction,就会自动启用–lock-all-tables,当然了–lock-tables永远都是关闭的。

-x, --lock-all-tables
                      Locks all tables across all databases. This is achieved
                      by taking a global read lock for the duration of the
                      whole dump. Automatically turns --single-transaction and
                      --lock-tables off.
  --master-data[=#]   This causes the binary log position and filename to be
                      appended to the output. If equal to 1, will print it as a
                      CHANGE MASTER command; if equal to 2, that command will
                      be prefixed with a comment symbol. This option will turn
                      --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      taken a short time at the beginning of the dump; don't
                      forget to read about --single-transaction below). In all
                      cases, any action on logs will happen at the exact moment
                      of the dump. Option automatically turns --lock-tables
                      off.

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检测了~~~