KACE Product Support : Pulling user details in ticket

Pulling user details in ticket

we have two entries for each user in USER table. One user row with AD details other user row with email id details, I want to pull both entries in USER_NAME column based on the FULL_NAME given in ticketing page. since both entries will be having same entries in FULL_NAME.

7 Comments [ + ] Show comments

select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
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((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,
U2.WORK_PHONE as SUBMITTER_PHONE,
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

LEFT JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER SUBMITTER2 on SUBMITTER2.FULL_NAME = SUBMITTER.FULL_NAME

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 (( HD_TICKET.CUSTOM_FIELD_VALUE14 = 'Set on Save') and HD_TICKET.HD_QUEUE_ID = 16 );

is this correct ? - rahimpal 8 years ago
- Those are the correct join statements. If you want to use any of the column names in an email then you need to also add those to the select portion of the statement. The last column selected is Q.NAME as QUEUE_NAME. After that statement you would add the other columns you want to select, like SUBMITTER.FULL_NAME, SUBMITTER2.EMAIL, etc. The list is separated by commas. - chucksteel 8 years ago
what should be the Update query to pull the user details having two entries for each user but Full_name is common for both entries .???

update HD_TICKET as T
set T.CUSTOM_FIELD_VALUE14 = (select USER_NAME FROM USER where SUBMITTER_NAME = SUBMITTER_ID) where
T.ID = <TICKET_IDS>;

below is the two entries for same user , I would like to pull both user_name entries based on FULL_NAME in ticketing page.

ID USER_NAME EMAIL FULL_NAME

2289 JohnAL john@fx.com JOHN AL

40524 John@ABC.com John@ctx.com JOHN AL - rahimpal 8 years ago
- I'm not sure what you want to set the custom field value to equal. - chucksteel 8 years ago
i want to set Custom value field to pull both USER_NAME ,since each user has AD and email details in USER_NAME column ,

All users have two entries with common FULL_NAME --> "JOHN AL"

when user is creating ticket i want his/her AD and email details ..since these are seperated in each row one with AD and other with email ID, both row have same FULL_NAME, i would like to pull two diff entries of same user using his FULL_NAME value - rahimpal 8 years ago
thanks alot for all ur help - rahimpal 7 years ago
04/28/2016 00:47:30> Starting: 04/28/2016 00:47:30 04/28/2016 00:47:30> Executing Select Query... 04/28/2016 00:47:30> selected 6 rows 04/28/2016 00:47:30> Executing Update Query... 04/28/2016 00:47:30> mysqli error: [1054: Unknown column 'HD_TICKET.SUBMITTER_ID' in 'on clause'] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE7 = (SELECT GROUP_CONCAT(USER.USER_NAME) FROM USER JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME GROUP BY USER.FULL_NAME) WHERE HD_TICKET.ID in (119,120,121,119,120,121)") 04/28/2016 00:47:30> Ending: 04/28/2016 00:47:30 - rahimpal 7 years ago
04/28/2016 00:47:30> Starting: 04/28/2016 00:47:30 04/28/2016 00:47:30> Executing Select Query... 04/28/2016 00:47:30> selected 6 rows 04/28/2016 00:47:30> Executing Update Query... 04/28/2016 00:47:30> mysqli error: [1054: Unknown column 'HD_TICKET.SUBMITTER_ID' in 'on clause'] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE7 = (SELECT GROUP_CONCAT(USER.USER_NAME) FROM USER JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME GROUP BY USER.FULL_NAME) WHERE HD_TICKET.ID in (119,120,121,119,120,121)") 04/28/2016 00:47:30> Ending: 04/28/2016 00:47:30 - rahimpal 7 years ago
I forgot that you can't reference the table you are updating in a sub select statement that way. I'll have to put some more thought into how this can be done. - chucksteel 7 years ago

Answers (2)

Posted by: chucksteel 8 years ago

This will require two joins to the USER table. Assuming that you are looking for information on the submitter the first join relates the submitter's ID to the USER table like this:

JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

Note that we are creating an alias for the USER table called SUBMITTER. We can now make a second relationship with the USER table based on the information in the table. I'm going to call this SUBMITTER2:

JOIN USER SUBMITTER2 on SUBMITTER2.FULL_NAME = SUBMITTER.FULL_NAME

Once you have these joins in your statement you can select fields based on those table names.

Comments:

Hi Chuck,

Need help.. I want to pull all the USER_NAME details from USER table in ticketing page

based on the SUBMITTER_FULLNAME... where Submitter name in ticketing page is similar to SUBMITTER_FULLNAME in USER table - rahimpal 8 years ago

Posted by: chucksteel 8 years ago

I've been trying to think about the best way to do this and I think this would work.

UPDATE HD_TICKET
SET CUSTOM_FIELD_VALUE14 = 
(SELECT GROUP_CONCAT(USER_NAME) FROM USER 
JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID
WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME
GROUP BY USER.FULL_NAME)
WHERE HD_TICKET.ID in <TICKET_IDS>

This uses a sub-select statement that should return a comma separated list of user names that have the full name of the ticket submitter.

Comments:

USER table doesnt have any column named SUBMITTER - rahimpal 8 years ago
- Correct. I'm creating an alias to the user table in the join statement. This allows you to refer to the table as SUBMITTER. - chucksteel 8 years ago
  - 04/26/2016 00:36:52> Starting: 04/26/2016 00:36:52 04/26/2016 00:36:52> Executing Select Query... 04/26/2016 00:36:52> selected 28 rows 04/26/2016 00:36:52> Executing Update Query... 04/26/2016 00:36:52> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '89,90,92,93,94,95,98,99,100,101,105,106,111,110,91,89,90,92,93,94,95,98,99,100,1' at line 7] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE18= (SELECT GROUP_CONCAT(USER_NAME) FROM USER JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME GROUP BY USER.FULL_NAME) WHERE HD_TICKET.ID in 89,90,92,93,94,95,98,99,100,101,105,106,111,110,91,89,90,92,93,94,95,98,99,100,101,105,106,111") 04/26/2016 00:36:52> Ending: 04/26/2016 00:36:52
    
    here is the query im using
    
    select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
    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((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,
    U2.WORK_PHONE as SUBMITTER_PHONE,
    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
    
    LEFT JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER SUBMITTER2 on SUBMITTER2.FULL_NAME = SUBMITTER.FULL_NAME
    
    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 (( HD_TICKET.CUSTOM_FIELD_VALUE18 = 'Set on Save') and HD_TICKET.HD_QUEUE_ID = 16 );
    
    UPDATE Query
    
    UPDATE HD_TICKET
    SET CUSTOM_FIELD_VALUE18=
    (SELECT GROUP_CONCAT(USER_NAME) FROM USER
    JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID
    WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME
    GROUP BY USER.FULL_NAME)
    WHERE HD_TICKET.ID in <TICKET_IDS> - rahimpal 8 years ago
Sorry, I think there should be parentheses around the <TICKET_IDS>.
WHERE HD_TICKET.ID in (<TICKET_IDS>) - chucksteel 8 years ago
- same error
  
  04/26/2016 07:48:07> Starting: 04/26/2016 07:48:07 04/26/2016 07:48:07> Executing Select Query... 04/26/2016 07:48:07> selected 36 rows 04/26/2016 07:48:07> Executing Update Query... 04/26/2016 07:48:07> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT USER_NAME FROM USER JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITT' at line 3] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE18= SELECT USER_NAME FROM USER JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = HD_TICKET.SUBMITTER_ID WHERE HD_TICKET.ID in (,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,)") 04/26/2016 07:48:07> Ending: 04/26/2016 07:48:07 - rahimpal 8 years ago
  - The update statement isn't correct. You are missing a set of parenthesis around the sub select and the where clause in the sub select is wrong. The full update statement should be:
    UPDATE HD_TICKET
    SET CUSTOM_FIELD_VALUE14 =
    (SELECT GROUP_CONCAT(USER_NAME) FROM USER
    JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID
    WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME
    GROUP BY USER.FULL_NAME)
    WHERE HD_TICKET.ID in (<TICKET_IDS>)
    
    Also, the HD_TICKET table only contains custom fields up to number 15 (CUSTOM_FIELD_VALUE14) and you are using custom field 19 (CUSTOM_FIELD_VALUE18). This update query won't work for fields that high. - chucksteel 8 years ago
  - thanks I have changed the custom field
    
    04/27/2016 01:11:06> Starting: 04/27/2016 01:11:06 04/27/2016 01:11:06> Executing Select Query... 04/27/2016 01:11:06> selected 6 rows 04/27/2016 01:11:06> Executing Update Query... 04/27/2016 01:11:06> mysqli error: [1052: Column 'USER_NAME' in field list is ambiguous] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE7 = (SELECT GROUP_CONCAT(USER_NAME) FROM USER JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME GROUP BY USER.FULL_NAME) WHERE HD_TICKET.ID in (119,120,121,119,120,121)") 04/27/2016 01:11:06> Ending: 04/27/2016 01:11:06 - rahimpal 7 years ago
  - Alright, I think we're getting closer. Try changing GROUP_CONCAT(USER_NAME) to GROUP_CONCAT(USER.USER_NAME)
    
    Also, the join statement is wrong:
    JOIN USER SUBMITER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID - chucksteel 7 years ago

Answer this question

Posted by:

View more:

KACE Product Support Questions

question