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