/build/static/layout/Breadcrumb_cap_w.png

K1000: VIP Ticket Rule

I have a ticket rule to set high priority for submitters with a label 'VIP'. This triggers the escalation rule which emails once an hour about the ticket until it is addressed. I tried to make it only change the priority if the day is Monday through Friday. Problem is I get an error: [1305: FUNCTION ORG1.DATEPART does not exist]

Here's the select query:

Select 'charlie.frey@wacoisd.org' as CHARLIE,
      -- ticket fields
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
    
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox.wacoisd.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
    
    
      -- about the updater
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
    
      -- about the owner
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
      OWNER_LABEL.LABEL_ID,
    
      -- about the submitter
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY, -- $priority
    
      -- about status
      S.NAME AS STATUS,   -- $status
    
      -- about impact
      I.NAME AS IMPACT,   -- $impact
    
      -- about category
      CAT.NAME AS CATEGORY, -- $category
    
      -- other fields
      HD_TICKET.CUSTOM_FIELD_VALUE0 AS LOCATION, -- $location
      HD_TICKET.CUSTOM_FIELD_VALUE1 AS ROOM, -- $room
      HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisd_num
      HD_TICKET.CUSTOM_FIELD_VALUE7 AS EQUIPMENT, -- $equipment
      HD_TICKET.CUSTOM_FIELD_VALUE5 AS DESCRIPTION, -- $description
      HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email

      -- -- example of static distribution list
      'helpdesk@wacoisd.org' AS NEWTICKETEMAIL -- $newticketemail
    
    
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                            /*AND C.ID=<CHANGE_ID>*/
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* labels ***********/ RIGHT JOIN USER_LABEL_JT OWNER_LABEL ON OWNER_LABEL.USER_ID = OWNER.ID
     /* labels ***********/ RIGHT JOIN USER_LABEL_JT SUBMITTER_LABEL ON SUBMITTER_LABEL.USER_ID = HD_TICKET.SUBMITTER_ID


     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

     WHERE S.NAME='New' AND SUBMITTER_LABEL.LABEL_ID = 252
AND DATEPART() = (2,3,4,5,6)
      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1


2 Comments   [ + ] Show comments
  • OK. I've been working on fixing this and at least figured out the syntax for the DATEPART function was wrong.

    Tried simplifying the query to isolate and get the syntax correct:

    Select DATEPART(Dw,HD_TICKET.CREATED) as DAYOFWEEK
    From HD_TICKET
    WHERE DAYOFWEEK = (2,3,4,5,6)

    I get this error "Error Code: 1305. FUNCTION ORG1.DATEPART does not exist"

    So, it seems like it doesn't even find the DATEPART fucntion. Should I use a different function like DATENAME? That returns the same error. Help! - lmland 10 years ago
  • You may want to take a look at this article and see if that won't get you on the right track. http://www.kace.com/support/resources/kb/article/How-to-Limit-Ticket-Rules-to-Run - ShawnCarson 10 years ago

Answers (1)

Answer Summary:
Posted by: lmland 10 years ago
10th Degree Black Belt
0

Nothing in that article can give you the day of the week for a date field. I figured out finally that in MySQL the function is DAYOFWEEK(). So I changed the where clause to the following and it works.

DAYOFWEEK(HD_TICKET.CREATED) between 2 and 6

Nothing in that article can give you the day of the week for a date field. I figured out finally that in MySQL the function is DAYOFWEEK(). So I changed the where clause to the following and it works.

DAYOFWEEK(HD_TICKET.CREATED) between 2 and 6 - See more at: http://www.itninja.com/question/k1000-vip-ticket-rule#sthash.4AH9iXZm.dpuf

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