Has anyone been able to tie inventory to HD ticket?
Has anyone figured out how to automatically update the ticket machine by referencing the inventory machine user logged?
Something like this but I'm having trouble getting the fields in the update logic to actually pull...
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((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.LOCATION as SUBMITTER_LOCATION,
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 HD_TICKET.HD_QUEUE_ID = 21
update HD_TICKET, MACHINE M1
set HD_TICKET.MACHINE_ID = M1.ID
where
M1.USER_NAME = HD_TICKET.SUBMITTER_ID
AND
(HD_TICKET.ID in (<TICKET_IDS>))
Answers (1)
The M1.USER_NAME = HD_TICKET.SUBMITTER_ID criterion is invalid. M1.USER_NAME is text ("grayematter"), while HD_TICKET.SUBMITTER_ID is a number (123456). You will need to link the two through the USER table, something like:
update HD_TICKET, MACHINE M1, USER U2
set HD_TICKET.MACHINE_ID = M1.ID
where
M1.USER_NAME = U2.USER_NAME
AND
U2.ID= HD_TICKET.SUBMITTER_ID
AND
(HD_TICKET.ID in (<TICKET_IDS>))
Just be aware that you may want to account for situations that return nulls or multiple records.
First, is the user being reported as logged in to a machine? If you search in Inventory on the user, is a machine listed? If not, this will set the HD_TICKET.MACHINE_ID to null. So, if someone else has logged into the machine (for example during service), this could adversely impact what you are trying to accomplish if the technician were to update the ticket and the rule is run on ticket save.
Second, is the user logged in to only ONE machine? The update could cause a problem if the user is reported as logged in to multiple machines. You should adjust the update query to ensure that only one machine is returned.
Comments:
-
I tried using the U2 reference as well, still can't seem to get it to return any actual results. Its strange, the logic seems good to me, and when I run my test submitters against an inventory search the data pulls there. Is that M1 searching the actual inventory machines or is that searching at the point of update just the machine already associated with the ticket? - tholmes 11 years ago
-
It is pulling against M1 in real time from the current full inventory. Try running the following in an SQL application (TOAD, MySQL Workbench, etc):
select
HD_TICKET.ID as 'ticket',
HD_TICKET.MACHINE_ID as 'ticket machine',
U2.USER_NAME as 'ticket username',
M1.USER_NAME as 'm username',
M1.ID as 'm id',
M1.NAME as 'm name'
from
HD_TICKET,
MACHINE M1,
USER U2
where
M1.USER_NAME = U2.USER_NAME
and U2.ID = HD_TICKET.SUBMITTER_ID
and HD_TICKET.ID = (a valid ticket number)
How many rows are returned for that ticket? If there is only one row returned, it should work for that ticket. In my environment (university with classrooms and labs), we cannot use the last logged in user as a user can be logged into multiple systems. If I run this query against a ticket in my name, I get two rows as I am listed as last logged on user for two records in our inventory. This scenario would cause the update statement above to fail. - grayematter 11 years ago
-
Was able to get it going once you pointed out the multiple returns will bomb it out. This seems to be working for the majority of our cases, might be able to add label filters etc to home in on the employee only machines if turns out to be a problem. Maybe a label filter for public machines for student submitters and a non-public machines filter for the employees.
Thanks for the assistance!
UPDATE HD_TICKET, MACHINE M1, USER U2
SET HD_TICKET.MACHINE_ID = (SELECT MAX(M1.ID) FROM MACHINE WHERE U2.ID = HD_TICKET.SUBMITTER_ID)
WHERE
M1.USER_FULLNAME like U2.FULL_NAME
AND
U2.ID = HD_TICKET.SUBMITTER_ID
AND
(HD_TICKET.ID in (<TICKET_IDS>)) - tholmes 11 years ago
