Otrs stat from last week, last month, last everyday

Hace unos días había tenido la necesidad de automatizar el envió de reportes en OTRS (3.1.2), lo que buscábamos era enviar el reporte de las actividades realizadas en los últimos N días, por ejemplo enviar automáticamente cada domingo por la noche un reporte semanal de los tickets creados en la semana actual.

El problema: Las estadísticas con objetos dinámicos no permiten especificar un parámetro como "LastWeek", solo permite dos opciones

• Definir un rango de fechas estéticamente.
• Dejar a elección del usuario escoger la fecha en la interface web.

¿Que pasa con el script de generación de estadísticas?
El script otrs.GenerateStats.pl recibe algunos parámetros como el ID de la estadística, el directorio destino donde colocara el archivo, el formato en que entregara la estadística (csv, pdf). Incluso una opción "-p" que recibe valores para Format, GraphSize, StatID  y ExchangeAxis. Debo mencionar que esta parte tiene una documentación muy pobre.

Como la idea es automatizar el proceso, tenemos que pensar en cron job, pero cron implica otrs.GenerateStats.pl, y este ultimo no tiene el soporte que ocupamos, esperando que esta descripción sea clara ¿quien podrá ayudarnos?

La solución:  Construcción de un script traductor.

En la base de datos de OTRS, las estadísticas se almacenan en "xml_storage", cada generación de estadísticas consultara las fechas primeramente en un cache y luego en la tabla

select * from xml_storage where xml_key=29

xml_type	xml_key	xml_content_key	xml_content_value
Stats	29	[0]{'otrs_stats'}[1]{'UseAsRestriction'}[1]{'TimeStop'}	2012-07-18 23:59:59
Stats	29	[0]{'otrs_stats'}[1]{'UseAsRestriction'}[1]{'TimeStart'}	2012-07-11 00:00:00

Es recomendable crear un usuario en mysql con acceso de select/update a nuestra tabla y nada mas, este para ser usado en nuestro script. Con esta información preliminar les presento el script resultante:

#!/bin/bash

################################################
# Martin Edmundo, [ Julio 2012 ]
# @martinedmundo
# www.elrincondemartin.org
# Funcion: Script de generacion de estadisticas
#          basado en los ultimos dias.
# Release: OTRS 3.1.2
################################################

#Definicion de variables usadas
OTRS_DIR=/opt/otrs
OTRS_BIN=$OTRS_DIR/bin
BODYMAIL=/usr/local/sbin/bodymail.txt

DBUSER='otrsstat'
DBPASSWD='xxxxxx'
DBNAME='otrs'
DBTABLE='xml_storage'

STATNUM='';
TARGETDIR=$HOME;
DAYSNUM='7';
FILENAME='';

FILENAMESTAT='';
PREFIXFILENAMESTAT='Estad';
datestart=''
datestop=''

function phelp {
        echo -e "Usage: $0 -n  -o  -d  | -h"
        echo -e "  -n Numero de estadistica."
        echo -e "  -o Directorio destino de la estadistica. [ Predeterminado: $HOME ]"
        echo -e "  -d Rango de fechas de los utimos dias hasta hoy. [ Predeterminado: 7 ]"
        echo -e "  -h Ayuda, este mensaje.\n"
}

function chdb {
        #Se define el rango de fechas para los ultimos DAYSNUM dias
        datestart=`date --date="-$DAYSNUM day" +"%F "`00:00:00;
        datestop=`date +"%F %H:%M:%S"`;

        #Limpiamos cache de estadisticas
        rm $OTRS_DIR/var/tmp/CacheFileStorable/XML/* 2> /dev/null

        #Actualizamos fechas de estadisticas
        mysql -u $DBUSER -p$DBPASSWD --database=$DBNAME --execute="UPDATE $DBTABLE SET xml_content_value='$datestart' WHERE xml_key=$STATNUM and xml_content_key LIKE '%TimeStart%'"
        mysql -u $DBUSER -p$DBPASSWD --database=$DBNAME --execute="UPDATE $DBTABLE SET xml_content_value='$datestop' WHERE xml_key=$STATNUM and xml_content_key LIKE '%TimeStop%'"
}

function sendmail_ {
        #Nos vamos al diretorio de los reportes
        cd $TARGETDIR

        #Se revisa los destinatarios de correo
        for mail in `cat $FILENAME`; do
                isComment=`echo "$mail" | cut -d "#" -f1`

                #Si no es un comentario, analizamos
                if [ -n "$isComment" ]; then
                        #Se requiere mejorar la expresion regular para correo
                        isMail=`echo "$mail" | grep ".*@.*"`

                        #Enviamos correo electronico
                        if [ "$?" = "0" ]; then
                                ( echo -e "Reporte de actividades correspondientes al siguiente periodo: \n
[ $datestart a $datestop ]"; uuencode $FILENAMESTAT Reporte_$STATNUM.pdf  ) | mail -s "Servicios update" $mail
                        else
                                echo "Formato de correo desconocido: $mail"
                        fi
                fi
        done
}

while getopts n:o:d:f:h OPT
do
        case $OPT in
                n) STATNUM="$OPTARG" ;;
                o) TARGETDIR="$OPTARG" ;;
                d) DAYSNUM="$OPTARG" ;;
                f) FILENAME="$OPTARG" ;;
                h)
                        phelp
                        exit 0
                ;;
                ?)
                        phelp
                        exit 1
                ;;
                esac
done

if [ -z "$STATNUM" ]; then
        echo "*** Ocurrio un error o hace falta el parametro -n"
        phelp
        exit 1;
fi

#Se actualiza la base de datos
chdb

#Se genera el reporte
rm $TARGETDIR/*.pdf 2> /dev/null
FILENAMESTAT=`$OTRS_BIN/otrs.GenerateStats.pl -n $STATNUM -o $TARGETDIR -f Print 2> /dev/null | grep -o "$PREFIXFILENAMESTAT.*pdf"`

#¿Quiere que se envie por correo?
if [ -n "$FILENAME" ]; then
        if [ -f $FILENAME ]; then
                sendmail_
        else
                echo "No existe el archivo $FILENAME"
                exit 1;
        fi
fi

exit 0;

Como lo usamos:

martinbarriga@monitoreo:/usr/local/sbin$ ./otrs.GenerateStats.sh -h
Usage: ./otrs.GenerateStats.sh -n  -o  -d  | -h
 -n Numero de estadística.
 -o Directorio destino de la estadistica. [ Predeterminado: /home/user ]
 -d Rango de fechas de los utimos dias hasta hoy. [ Predeterminado: 7 ]
 -h Ayuda, este mensaje.

OTRS y Nagios/Centreon

Como les había comentado antes, había estado trabajando un poco en OTRS para el control de servicios dentro de una unidad organizacional. Por simple curiosidad se me ocurrió agregar dos características al monitoreo para que se generen gráficas estadistas del los servicios que se realizan.

¿Por que construir un propio plugin y no usar los mecanismos que otrs nos ofrece para integrarse con Nagios? Primeramente por cuestiones de facilidad de implementación, la documentación de estos mecanismos no son muy claros (al menos para mi), sobre todo por que otrs ha cambiado entre versiones. La otra razón es por que los mecanismos de otrs tienen la limitante de trabajar únicamente en un entorno donde conviven en un mismo servidor Otrs/Nagios.

El script es muy básico, para este post, soporta  únicamente el monitoreo de tickets abiertos y tickets creados por día:

#!/bin/bash

dbHostname="127.0.0.1"
dbUser="otrsguest"
dbPasswd="qwerty"
dbName="otrs"
dbWhere=""

WARNING=10
CRITICAL=15
TYPE=""

# Plugin return codes
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3

function report {
        val=`mysql -h $dbHostname -u $dbUser -p$dbPasswd --database=$dbName --execute="select count(*) as open from ticket $dbWhere" | awk 'BEGIN{FS="\n"; RS="\n\n";}{print $2}'`
        if [ "$val" -le "$WARNING"  ]; then
                echo "Tickets $TYPE: $val [ OK ] | value=$val"
                exit $STATE_OK
        else
                if [ "$val" -le "$CRITICAL" ]; then
                        echo "Tickets $TYPE: $val [ WARNING ] | value=$val"
                        exit $STATE_WARNING
                else
                        echo "Tickets $TYPE: $val [ CRITICAL ] | value=$val"
                        exit $STATE_CRITICAL
                fi
        fi
}

function phelp {
        echo -e "Usage: $0 -t  -w  -c  | -h"
        echo -e "  -t Tipo de tickets a consultar"
        echo -e "  -c Valor critico"
        echo -e "  -w Valor warning"
        echo -e "  -h Help, this message.\n"
}

while getopts t:w:c:h OPT
do
        case $OPT in
                t) TYPE="$OPTARG" ;;
                w) WARNING="$OPTARG" ;;
                c) CRITICAL="$OPTARG" ;;
                h)
                        phelp
                        exit $STATE_UNKNOWN
                ;;
        esac
done


if [ "$TYPE" = "abiertos" ]; then
        dbWhere="where ticket_state_id='4'"
        report
else
        if [ "$TYPE" = "creados" ]; then
                date=`date +"%Y-%m-%d"`
                dbWhere="where create_time like '$date%'"
                report
        else
                echo "Carecteristica a monitorear desconocida. Conocidas: abiertos, creados"
                exit $STATE_UNKNOWN
        fi
fi

La siguiente gráfica nos representa la actividad de tickets abiertos para los últimos dos días.

 La siguiente gráfica nos representa la actividad de tickets creados por día, en los últimos tres días.

Exportando M$ Active Directory a MySQL

El siguiente script me ayudo para exportar las cuentas de Active Directoy a una base de datos en MySql. El problema y/o interés surgió debido a la implementación de otrs, una solución para la gestión de servicios IT y help desk, donde se pretendía centralizar las cuentas locales de Active Directory como clientes en el contexto de la aplicación (otrs).

Para la solución de este problema (pensando en la automatización) había que hacer dos cosas, llenar la tabla de clientes con los datos de cada cuenta en la base de datos y cambiar el tipo de autentificación usando ldap.

Requisitos:

• awk
• openLdap
• mysql-client

Script: ldap2mysql.sh

#!/bin/bash
# Martin Edmundo 2012
# Orden del listado AD: sn, givename, userPrincipalName

#Inicialización de variables
dbUser="otrs";
dbPasswd="/Gvxsn1CFaj5Q";
dbName="otrs";
let _i=0;

searchBase="DC=PGJ,DC=MICH";
bindDistinguishedName="account@domain";
ldapPasswd="AcbjdaIQ6P/9g";
ldapHost="ldap://172.16.1.2";

apellido="";
nombre="";
user="";

#Leemos el arbol completo de usuarios
tree=`ldapsearch -x -z 0 -b $searchBase -D $bindDistinguishedName -w $ldapPasswd "(objectclass=user)" -H $ldapHost | egrep "userPrincipalName|givenName|sn\: "`

#Leemos el ultimo customer_id que se tiene en la BD, sobre ese se incrementaran los siguientes INSERT
let lastCustomer=`mysql -u $dbUser -p$dbPasswd --database=$dbName --execute="select max(customer_id) as a from customer_user" | awk 'BEGIN{FS="\n"; RS="\n\n";}{print $2}' `

echo "$tree" | (
     while read linea;
             do
             te_conozco=`echo $linea | cut -c1-2`
             if [ "$te_conozco" = "sn" ]; then
                     apellido=`echo $linea | cut -f2 -d" "`;
             fi
             if [ "$te_conozco" = "gi" ]; then
                     nombre=`echo $linea | cut -f2 -d" "`;
             fi
             if [ "$te_conozco" = "us" ]; then
                     user=`echo $linea | cut -f2 -d" "`;
                     if [ "$apellido" != "" -a "$nombre" != "" ]; then
                             let lastCustomer+=1;
                             uid=`echo $user | cut -f1 -d"@"`
                             daleDale=`mysql -u $dbUser -p$dbPasswd --database=$dbName --execute="select login from customer_user where login='$uid'" | awk 'BEGIN{FS="\n"; RS="\n\n";}{print $2}'`

                             #Si la cuenta no esta ya registra en DB, se hace el INSERT
                             if [ "$daleDale" == "" ]; then
                                     let _i+=1;
                                     sqlStm="INSERT INTO customer_user (login, email, customer_id, pw, title, first_name, last_name, valid_id, create_time, create_by, change_time, change_by)  VALUES ('$uid', 'informaticapgjmich@gmail.com',$lastCustomer, '', 'Importado por openLdap', '$nombre', '$apellido', 1, '2012-03-23 20:00:00', 1, '2012-03-23 20:00:00', 1)";
                                     #echo "$sqlStm"
                                     mysql -u $dbUser -p$dbPasswd --database=$dbName --execute="$sqlStm";
                                     echo "*** Importado ($_i): $uid"
                             fi

                     fi
                     apellido="";
                     nombre="";
             fi
     done
)