Re: [rt-users] Searching for all tickets updated yesterday by a particular user
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_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
Re: [rt-users] Searching for all tickets updated yesterday by a particular user
On 03/16/2012 9:35 AM, Thomas Smith wrote: Hi, I would like to search for all tickets updated yesterday by a particular user--I see the option to search Last updated by, but this will only show if a given user was the last to touch a ticket. Is there a way to search for all tickets that were updated by a given user on a given day? ~ Tom Click on new search. You can select all your options on the left if your more comfy. LastUpdatedBy = 'user' AND Updated = '2012-03-15' However I dont think it will work once it has been updated by someone else. ie, Ticket1: Thing1 updated it yesterday because of an invoice received, Thing2 updated it today because of the items received. Ticket one will then be excluded because it was updated today by a different user. -Matt
Re: [rt-users] Searching for all tickets updated yesterday by a particular user
I wrote a bash script to accomplish this exact task. The search queries within rt only support queries against the tickets table while transactions hold all relevant info on daily updates by a particular user. When I get a chance, I'll log into my office network, sanitize the script and post it. The one I wrote grabs all users in a group of my choosing and emails the entire list to all users in that group. Sent from my mobile device. On Mar 16, 2012, at 5:14 PM, 20/20 Lab l...@pacbell.net wrote: On 03/16/2012 9:35 AM, Thomas Smith wrote: Hi, I would like to search for all tickets updated yesterday by a particular user--I see the option to search Last updated by, but this will only show if a given user was the last to touch a ticket. Is there a way to search for all tickets that were updated by a given user on a given day? ~ Tom Click on new search. You can select all your options on the left if your more comfy. LastUpdatedBy = 'user' AND Updated = '2012-03-15' However I dont think it will work once it has been updated by someone else. ie, Ticket1: Thing1 updated it yesterday because of an invoice received, Thing2 updated it today because of the items received. Ticket one will then be excluded because it was updated today by a different user. -Matt
Re: [rt-users] Searching for all tickets updated yesterday by a particular user
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
