Searching for all tickets updated yesterday by a particular user

RT Users
1

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

2

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

3

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.On Mar 16, 2012, at 5:14 PM, 20/20 Lab lab@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

4

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/g” |sed
-e “s//$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 “Organization.com”

$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 fileTo: net_alerts@organization.com
From: Net Alerts net_alerts@organization.com
X-TLS: Technical Operations Ticket updates for
Subject: Technical Operations Ticket updates for

Below are the departmental ticket updates for .

End of header file

5

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/g” |sed
-e “s//$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 “Organization.com”

$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@organization.com
From: Net Alerts net_alerts@organization.com
X-TLS: Technical Operations Ticket updates for
Subject: Technical Operations Ticket updates for

Below are the departmental ticket updates for .

End of header file