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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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.

Answered 08/02/2013 by: grayematter
Fourth Degree Black Belt

  • 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?
    • 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.
  • 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>))
Please log in to comment
Answer this question or Comment on this question for clarity

Share