[rt-users] Searching for all tickets updated yesterday by a particular user

[Thomas Smith]

Thank you Joe! I'll give it a shot!

On Mar 16, 2012, at 5:51 PM, Joe Harris wrote:

> 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_alerts at organization.com
> From: Net Alerts <net_alerts at 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