I was tasked with creating a script that would select 10 random tickets and would email the submitter of those tickets a link to a custom 3rd party customer satisfaction survey that they could take. I just wanted to share this script with the community.
I based the script on python and to run the script as a scheduled task on a Windows Server every 30 days.
#Start of script
#
#Import Libraries into script.
import MySQLdb #MySQL needed for connection to K1000
import smtplib #SMTPLIB needed to send email to submitter of tickets
import datetime #datetime needed for log file
#
#Create connection to K1000
db = MySQLdb.connect(
host="192.168.1.120", # IP of K1000
user="R1", # K1000 Username. Located in Settings->General Settings->Report Username
passwd="k1000password", # Password for K1000 user Located in Settings->General Settings->Report User Password
db="ORG1" # Name of the database on K1000 Located in Settings->General Settings->Database Name
)
#
#Create cursor object. Needed for Database creation
cur = db.cursor()
#
#SQL Query that grabs 10 tickets at random that have been closed in the past 30 days.
cur.execute("""
SELECT USER.FULL_NAME, USER.EMAIL, HD_TICKET.ID, HD_TICKET.TITLE
FROM USER
INNER JOIN HD_TICKET ON USER.ID=HD_TICKET.SUBMITTER_ID
WHERE HD_TICKET.TIME_CLOSED > date_sub(NOW(), INTERVAL 30 DAY)
Order by RAND()
Limit 10
""")
#
#Create today date for log file
today = datetime.date.today()
#
#Write log file of what is in the cursor object
logfile = open("C:\Customer_Survey_" + str(today) + ".log","a")
#For each row in the cursor object, add an entire in a log file and email the submitter of the ticket
for row in cur.fetchall():
Email = str(row[1])
Ticket_ID = str(row[2])
logfile_entries = (Email,Ticket_ID)
logfile.write(str(logfile_entries))
logfile.write("\n")
Name = str(row[0])
Email = str(row[1])
Ticket_ID = str(row[2])
Ticket_Title = str(row[3])
TO = Email
SUBJECT = "Customer Survey"
TEXT = """
Your ticket "%s" was closed within the past 30 days.
You may review your ticket here http://itsupport/userui/ticket?ID=%s
Please take some time to complete our customer satisfaction survey.
www.survey.com
Thank you,
Your IT Team
""" % (Ticket_Title, Ticket_ID)
#SMTP Credentials
SMTP_sender = "john.doe@email.com"
#Create Connection with mail relay server
server = smtplib.SMTP()
server.connect("mail.relayserver.com",587)
#
BODY = '\r\n'.join([
'To: %s' % TO,
'From: %s' % SMTP_sender,
'Subject: %s ' % SUBJECT,
'',
TEXT
])
#
server.sendmail(SMTP_sender, [TO], BODY)
#
server.quit()
logfile.close()
I hope this helps someone in the furture when it comes to sending out random customer surveys.
Comments