Forum GLPI-Project

linker3000

Member

Registered: 2010-03-04

Posts: 56

Here's a script to check tickets for missing automated logs etc

Hi,

In a previous forum post I asked whether GLPI had a feature where it could check for a situation where an expected ticket (submitted via email) had NOT been received - this would be ideal to check for missing automated server status reports etc.

As I got a total of 0 replies about this (!), I have developed my own script that I will run at 8am every day on the GLPI server (via a crontab entry) to check whether a ticket has been created using the specified text in the title/description. If not, the script sends an email to either a specified person, or to GLPI, to highlight the missing expected ticket.

I've tested the script against the database for GLPI 0.83.91

Here it is for comments/improvements etc... Hope you find it useful - it would be great if something like this was adopted as a 'automatic action'.

EDIT: V1.01 Modified to not include deleted tickets

#!/bin/bash
#
# check_glpi_tickets.sh
#
# V1.01 N. Kendrick  11-Sep-2013
# 
# This script parses the specified input file into mysql commands to check for the presence of tickets that match the specified criteria. If NO tickets
# are found with matching criteria, a new ticket is emailed in as an alert using the text specified.
#
# This script should be called with an input file as follows:
#
# check_glpi_tickets.sh < input_file.txt
#
# The input file can contain multiple lines of things to check. The format of the input file should be as follows:
#
#  Title!Description!Interval!Alert message!Optional GLPI triggers
#
# Example: 
#
#   %Test alert email%!%[success]%!1 day!Test alert email not received!assign=sam
#   Logwatch for server03 (Linux)!%!1 day!Logwatch for Server 03 not received!
#   Weekly report for server01!7 day!Server 01 weekly report is late!assign=sam
#
# The first line in this sequence will check whether a ticket (email) has been received with the following criteria:
#
# Subject: Test alert email (NB not case sensitive)
# Body contains: [success]
# Received within the last day
#
# If the critera are not met (no matching ticket found), a new ticket will be generated via email using the specified
# string as the message subject/ticket title. If a string is specified for the GLPI triggers, this is added to the message body to help with rules processing in GLPI 
#
# Note that the criteria strings are used for mysql 'like' queries and so use the wildcard '%' as a suffix and prefix if you want to check whether the ticket title and/or
# description contain the desire test anywhere in them. The % can be omitted if exact matches are desired. If
# there is no need to check either the subject or body of the ticket, use ia single "%" - for example: %Test alert email%!%!1 day
#
# ** Leading spaces on the input lines are processed so DO NOT INDENT unless you want to check for spaces **
#
# The day/date criteria is in mysql interval format - for example, valid strings include: 1 day  4 hour  Note that fractions are rounded
#
# For valid unit values, see: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
#
# Lines starting with the hash/pound symbol ('#') in the input file will be ignored as comments. Blank lines will be ignored
#
# Start of Code
#

# Make the field separator an exclamation mark as per the input file in order to avoid string truncation..
IFS='!'
#
# Config area
#
# Setup variables
#
DBNAME=glpi
DBUSER=YourmySQLusername
DBPASS=YourmySQLPassword
DBSERVER=127.0.0.1 (127.0.0.1 if running this script on your GLPI server)
DBTABLE=glpi_tickets
EMAILADDR=wheretosendemail@address.here
#
# End of config area
#
# Subroutine to generate an email
#
function go_mail
{
  echo -e "No ticket found in GLPI matching search criteria\n\nSubject containing: [$1]\n\nBody containing: [$2]\n\nTimespan: [$4]\n\n--------------------\nInternal use:\n\n$5" | mail -s "[EXP] Expected Email not received: $3" $EMAILADDR
}

# Subroutine to run query 
#
function go_check 
{
   result=`mysql $DBNAME -u $DBUSER --password=$DBPASS -s -e "select count(*) from $DBNAME.$DBTABLE where (name like '$1') and (content like '$2') and (is_deleted = 0) and (date > DATE_SUB(now(), interval $3));"`
  return $result
}

# Script execution starts here...

if [ -t 0 ] && [ $# -eq 0 ] ; then  
  echo ""
  echo "I need some input!"
  echo ""
  echo "Run me with an input file - eg: $0 < thisfile.txt"
  echo ""
  exit 1
fi

# Read in lines and skip blanks and ones that start with a # (comment)...
while read subject description timespan message assignee
do
  if [ ! "$subject" = ""  ] && [ ! "x${subject:0:1}" = "x#" ] ; then 
    if [ ! "$description"  = "" ] && [ ! "$timespan" = "" ] && [ ! " $message" = "" ]; then
      go_check $subject $description $timespan
      if [ $? -eq 0 ] ; then 
        go_mail $subject $description $message $timespan $assignee 
      fi
    else 
      echo "GLPI ticket check script: Missing parameters or syntax error in line starting '$subject'"
    fi
  fi 
done

# Reset field separator as we leave..
unset IFS

Last edited by linker3000 (2013-09-11 17:18:23)