/build/static/layout/Breadcrumb_cap_w.png
11/14/2018 113 views

Does anyone know how to modify the below SQL code to include submitter's first name and last name as separate variables? I tried to modify it, but it came up with errors.

-- 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


Full KACE article: https://support.quest.com/kace-systems-management-appliance/kb/111222/how-to-notify-any-user-or-group-by-email-when-a-new-ticket-is-created

Any help will be highly appreciated.

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

As long as all your "FULL_NAME" fields are structured like "FIRST_NAME LAST_NAME" then this will work:

select Substring_Index(FULL_NAME, ' ',1) as Name,

           Substring_Index(FULL_NAME, ' ',-1) as Surname

From USER

Answered 11/16/2018 by: jleitsch
Purple Belt

  • Thank you for suggestion. Unfortunately, our "FULL_NAME" fields are structured like "LastName, FirstName,". If we use "Dear $submitter_fname" in the email template, it will be converted to "Dear DOE, JOHN," and that's the reason why we want to separate them into 2 variables. Any thoughts?
    • Swap the -negative number and change the delimiter
      Substring_Index(FULL_NAME, ',',-1) as Name,
      Substring_Index(FULL_NAME, ',',1) as Surname
      • Thank you very very much jleitsch!!!!! It works!!!!! In case anyone needs to do the same thing. Please see the actual code below.

        Substring_Index(SUBMITTER.FULL_NAME, ',',1) as SUBMITTER_LASTNAME, -- $submitter_lastname

        Substring_Index(SUBMITTER.FULL_NAME, ',',-1) as SUBMITTER_FIRSTNAME, -- $submitter_firstname

All Answers

0

There is no field which keeps first name and last name as separate fields. The data comes from user record which doesn't keep them separate. 

Answered 11/15/2018 by: AbhayR
Red Belt