/build/static/layout/Breadcrumb_cap_w.png

Create Customer Survey Random Script

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

This post is locked

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ