I am fairly new to writing KACE rules and have had no formal SQL training, but have been able to write some simple rules.  Has anyone written a rule that pulls data based on a user selected in a custom field, and writes that data into another custom field? 

These custom ticket fields have been created in a Queue:
      A user field (HD_TICKET.CUSTOM_FIELD_VALUE39)
      A text field for that user's email address (HD_TICKET.CUSTOM_FIELD_VALUE40)
      A second user field (HD_TICKET.CUSTOM_FIELD_VALUE41)
      A text field for the second user's email address (HD_TICKET.CUSTOM_FIELD_VALUE42)

I am trying to write a rule that will do the following, on Ticket Save, in that Queue only (HD_TICKET.HD_QUEUE_ID = 43):
           If HD_TICKET.CUSTOM_FIELD_VALUE39 has an entry,
                    Find the email address for that user and load it into HD_TICKET.CUSTOM_FIELD_VALUE40
           If HD_TICKET.CUSTOM_FIELD_VALUE41 has an entry,
                    Find the email address for that user and load it into HD_TICKET.CUSTOM_FIELD_VALUE42


Other rules then use these email addresses for various notifications.

Any guidance is appreciated!


Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Thanks, UntchV for all of your help! You made my day!

    I will use two rules for each user/email field combination. This Update code worked:

    UPDATE HD_TICKET
    join USER ON (HD_TICKET.CUSTOM_FIELD_VALUE39 = USER.ID)
    SET CUSTOM_FIELD_VALUE40 = CASE
    WHEN CUSTOM_FIELD_VALUE39 <> '' THEN USER.EMAIL
    ELSE CUSTOM_FIELD_VALUE40
    END
    WHERE (HD_TICKET.ID in (<TICKET_IDS>))
Please log in to comment

Answer Chosen by the Author

0
Thanks, UntchV for all of your help! You made my day!

I will use a separate rule for each user/email field combination.  In addition to the standard 'wizard' select code, this is what I ended up using:

Last Lines of Select SQL:

                     …andHD_TICKET.CUSTOM_FIELD_VALUE39 <> ''

                        andHD_TICKET.HD_QUEUE_ID = 43


Update SQL:
UPDATE HD_TICKET 
join USER ON (HD_TICKET.CUSTOM_FIELD_VALUE39 = USER.ID)
SET CUSTOM_FIELD_VALUE40 = CASE 
WHEN CUSTOM_FIELD_VALUE39 <> '' THEN USER.EMAIL
ELSE CUSTOM_FIELD_VALUE40 
END

WHERE (HD_TICKET.IDin (<TICKET_IDS>))


-------------------------------------------------------------------
I also had to add another rule for each user/email field (that runs after the rule shown above) to clear the address if the User field was changed to Unassigned:

Last Lines of Select SQL:

                        andCUSTOM_FIELD_VALUE39 = 0

                        andHD_TICKET.HD_QUEUE_ID = 43

 

 

UPDATE SQL:

UPDATE HD_TICKET

SETCUSTOM_FIELD_VALUE40 = ''

WHERE (HD_TICKET.IDin (<TICKET_IDS>))


Answered 04/28/2016 by: NancyC
White Belt

Please log in to comment

Answers

1

The rule you are looking for would be this one:


SELECT:

select HD_TICKET.*,
                        HD_STATUS.NAME AS STATUS_NAME,
                        HD_STATUS.ORDINAL as STATUS_ORDINAL,
                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                        STATE,
                        if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                        case upper(STATE)
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                        U1.FULL_NAME as OWNER_FULLNAME,
                        U1.EMAIL as OWNER_EMAIL,
                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                        U2.FULL_NAME as SUBMITTER_FULLNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                        Q.NAME as QUEUE_NAME
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                        where HD_PRIORITY.ID = HD_PRIORITY_ID
                        and HD_STATUS.ID = HD_STATUS_ID
                        and HD_IMPACT.ID = HD_IMPACT_ID
                        and HD_CATEGORY.ID = HD_CATEGORY_ID
                        and concat(HD_TICKET.CUSTOM_FIELD_VALUE39, HD_TICKET.CUSTOM_FIELD_VALUE41) <> ''
      and HD_TICKET.HD_QUEUE_ID = 43


UPDATE:

UPDATE HD_TICKET

SET CUSTOM_FIELD_VALUE40 = CASE

WHEN CUSTOM_FIELD_VALUE39 <> '' THEN CUSTOM_FIELD_VALUE39

ELSE CUSTOM_FIELD_VALUE40

END ,

CUSTOM_FIELD_VALUE42 = CASE

WHEN CUSTOM_FIELD_VALUE41 <> '' THEN CUSTOM_FIELD_VALUE41

ELSE CUSTOM_FIELD_VALUE42

END

WHERE (HD_TICKET.ID in (<TICKET_IDS>))


Answered 04/28/2016 by: UntchV
Yellow Belt

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • Thank you for your suggestion! I was receiving a syntax error, which was resolved by removing the closing ) at the end of the SELECT section. The Rule will run, but instead of returning the email address for each field, it is updating the email address fields with the user IDs (numbers) instead of the email address.
    • Try this update statement:

      UPDATE HD_TICKET join USER ON (HD_TICKET.CUSTOM_FIELD_VALUE39 = USER.ID or HD_TICKET.CUSTOM_FIELD_VALUE41 = USER.ID)
      SET CUSTOM_FIELD_VALUE40 = CASE
      WHEN CUSTOM_FIELD_VALUE39 <> '' THEN USER.EMAIL
      ELSE CUSTOM_FIELD_VALUE40
      END ,
      CUSTOM_FIELD_VALUE42 = CASE
      WHEN CUSTOM_FIELD_VALUE41 <> '' THEN USER.EMAIL
      ELSE CUSTOM_FIELD_VALUE42
      END
      WHERE (HD_TICKET.ID in (<TICKET_IDS>))
      • Getting closer! :) Now, it loads an email address:
        - If both user fields are Unassigned, nothing is loaded into either email field (this is correct).
        - If both user fields have an entry, the first user's email address goes into both email fields.
        - If either user field has an entry (but the other is Unassigned), that user's email address goes into both fields.

        Disregard my earlier correction, if you saw it... I was trying to test with the rule disabled! My first statement was correct.
Please log in to comment
Answer this question or Comment on this question for clarity