Here is my script to capture yesterdays ticket updates. It can be
changed as needed to meet your needs. The main thing was to get the
query to capture transactions for tickets and the query is below for
that.This is 2 files, the script and the email header. Script is at
the top and header at the bottom.
Hope this helps!
Joe
#!/bin/bash
# Set variables
HOME="/organization/scripts/ticketing"
DB=databasename
DBHOST=databasehostname
DBUSER=databaseuser
# Prepare for line breaks in results
newline='
'
OIFS=$IFS
IFS=$newline
#Capture yesterdays date
YESTERDAY=`date +%Y-%m-%d -d yesterday`
DAYOFWEEK=`date +%A -d yesterday`
YEAR=`date +%Y -d yesterday`
MONTH=`date +%m -d yesterday`
DAY=`date +%d -d yesterday`
# Create file to be emailed and replace template data with date info
touch "$HOME/tickets.$YESTERDAY"
cat "$HOME/tickets.header" |sed -e "s/<YESTERDAY>/$YESTERDAY/g" |sed
-e "s/<DAYOFWEEK>/$DAYOFWEEK/g" >$HOME/tickets.$YESTERDAY
#Get User ID's from technical operations users (GROUPID variable is
the group you want to capture)
GROUPID=140
USERS=`psql -A -t -c "select a.id,a.emailaddress from users a,groups
b,groupmembers c where a.id=c.memberid and b.id=c.groupid and
b.id=$GROUPID" -U $DBUSER -h $DBHOST $DB`
# Loop through users query and search for yesterdays ticket updates
for user in $USERS
do
USERID=`echo $user |cut -d\| -f1`
EMAIL=`echo $user |cut -d\| -f2`
# Add/Append data to the email body file for each user in the group
created above
echo "$newline" >>$HOME/tickets.$YESTERDAY
echo "Begin tickets updated by $EMAIL" >>$HOME/tickets.$YESTERDAY
TICKET=`psql -A -t -c "select distinct b.id from users a,tickets
b,groups c,groupmembers d, transactions e where a.id=e.creator and
b.id=e.objectid and a.id=d.memberid and c.id=d.groupid and c.id=140
and a.id=$USERID and date_part('year',e.created) = '$YEAR' and
date_part('month',e.created) = '$MONTH' and date_part('day',e.created)
= '$DAY'"-U $DBUSER -h $DBHOST $DB`
# Grab subject and create link to ticket and add to email body file
for ticket in $TICKET
do
OIFS=$IFS
IFS=$newline
SUBJECT=`psql -A -t -c "select distinct subject from tickets where
id=$ticket" -U $DBUSER -h $DBHOST $DB`
echo "$SUBJECT" >>$HOME/tickets.$YESTERDAY
echo "http://ticketing.organization.com/Ticket/Display.html?id=$ticket";
>>$HOME/tickets.$YESTERDAY
echo "----------------------------------------------------------------------"
>>$HOME/tickets.$YESTERDAY
done
echo "$newline End tickets updated by $EMAIL" >>$HOME/tickets.$YESTERDAY
echo "$newline" >>$HOME/tickets.$YESTERDAY
done
#Wrap it all up and send the email
/usr/sbin/sendmail -t < "$HOME/tickets.$YESTERDAY"
# End of script
#Beginning of header file
To: net_ale...@organization.com
From: Net Alerts <net_ale...@organization.com>
X-TLS: Technical Operations Ticket updates for <DAYOFWEEK> <YESTERDAY>
Subject: Technical Operations Ticket updates for <DAYOFWEEK> <YESTERDAY>
Below are the departmental ticket updates for <DAYOFWEEK>.
# End of header file
