/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Mysql Query help

04/21/2016 649 views
Need help on Mysql Query

Any query to pull SUBMITTER name appear in Helpdesk page  into this below query 
  select USER_NAME  from USER where FULL_NAME like '%SUBMITTER%'


If User name is ALEX  .  I want to replace the  query as below 

  select USER_NAME  from USER where FULL_NAME like 'ALEX'
0 Comments   [ + ] Show comments

Comments


All Answers

0
Your question is missing some parts to it. Are you looking for any tickets opened by someone with the name Alex as their full name? This will get you all tickets submitted by anyone with the name alex. If you need something else, please explain your question in more detail. 

Select
  HD_TICKET.TITLE,
  USER.FULL_NAME
From
  HD_TICKET Inner Join
  USER
    On USER.ID = HD_TICKET.SUBMITTER_ID 
  WHERE User.full_name rlike 'alex'  


Answered 04/21/2016 by: nshah
Red Belt

  • thanks, nshah... We have two entries for each user in USER Table.. One row has AD name and other has Lotus notes email ID, only way I can pull AD/Email id details is by using Full Name of the user..Both row has same Full name entries

    I want to pull user details in ticketing page from USER table when SUBMITTER name in ticketing page and FULL NAME in USER table matches

    USER table entries

    ID USER_NAME EMAIL FULL_NAME


    2289 mathewj mathewj@gccx.COM Mathew JOSE


    40524 mathewjose@FR.COM mathewjose@FR.COM Mathew JOSE

    screen shot of table here
    http://cubeupload.com/im/ABK0JB.jpg
    • So you want fields in a ticket to be changed based on the submitter? That sounds like a job for a custom ticket rule.
      • yes,any idea? any rule you have to pull this?
0
I still don't exactly understand what you want to do, but here is some code to get you started. Replace "TICKET_FIELD" and "USER_FIELD" with the appropriate column names for your environment.

!!!WARNING!!! Custom Ticket Rules are DANGEROUS: use a test environment, or at least in a test queue with test tickets, before putting anything in production.

Select:
HD_TICKET.ID AS ID, 
USER.ID AS USER_ID
FROM HD_TICKET 
JOIN USER ON (USER.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.[TICKET_FIELD] = '' 
NOTE: you may not need that final line (or you may want to include more conditions), 
depending on whether or not the field will ever be filled by a submitter or owner

Update:
UPDATE HD_TICKET
JOIN USER ON (USER.ID = T.SUBMITTER_ID)
SET T.[TICKET_FIELD] = U.[USER_FIELD]
WHERE T.ID IN (<TICKET_IDS>)

Frequency: "On Ticket Save"
Answered 07/01/2016 by: JasonEgg
Red Belt

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