/build/static/layout/Breadcrumb_cap_w.png

Dell K1000 - Service Desk - Asset data to ticket fields

Hello,

at the service desk, we have a process that create a parent ticket, in this parent ticket we fill the asset field with a room asset.
A ticket rule change after creating, the title from the parent ticket to the name from the room asset. Is it possible to fill custom ticket fields in the child ticket as a kind of checklist with the name from the associated assets from the room asset? (extra: when every checkbox is active, the child and the parent ticket are closed)

Or is it possible to create a ticket for every asset thats associated with the asset in the parent ticket (in this case; the room asset)?

Hope you can help, thanks!

Asked 8 years ago 1473 views
0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: svmay 8 years ago
Red Belt
2

Top Answer

Solution:

I've created for the child ticket custom fields:

ticket-layoutfields:
> Name:    CUSTOM_1
> Label:     Move (01)
> Permissions: uservisible

> Name:    CUSTOM_2
> Label:    Move (01) finished
> Permissions: usermodify                (note: usermodify - otherwise, the checkbox is not displayed in the userview)

custom fields:
> Name:    CUSTOM_1
> Fieldtyp: easyselection
> Selectionfield:
query:SELECT NAME FROM ORG1.ASSET WHERE ASSET_TYPE_ID = 'id for pc'
OR ASSET_TYPE_ID = 'id for monitor'
OR ASSET_TYPE_ID 0 'id for printer'
(note: with this query you can change the select in the ticket, maybe because an asset don't move or another asset are moving to the same room too)

So that the order in the ticket are right - change the position from 'DUE_DATE' between 'OWNER' and 'CUSTOM_1' so after that - on the left side from the ticket is 'Move (01)' and next to it, on the right side from the ticket is 'Move (01) finished'.

Ticketrule for update the customfield in the childticket

SQL-select:
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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        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,
                        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_STATUS.NAME = 'Status1') and HD_TICKET.HD_QUEUE_ID = 12 )
In this case I search for status 'Status1' (created for the step between, after the update another ticketrule set it to status 'planned') and for the queue.

SQL-update:
 update HD_TICKET as T
  join HD_STATUS as T5
    on T.HD_QUEUE_ID = T5.HD_QUEUE_ID
  left join HD_TICKET as PARENT
    on PARENT.ID = T.PARENT_ID
  left join ORG1.ASSET as A
    on A.ID = PARENT.ASSET_ID
    set T.CUSTOM_FIELD_VALUE0 = (SELECT NAME  FROM ORG1.ASSET
WHERE ASSET.ID =
(
SELECT ASSET_ID FROM ORG1.ASSET_ASSOCIATION
WHERE ASSET_FIELD_ID = 'id for pc'
AND ASSOCIATED_ASSET_ID = A.ID
OR ASSET_FIELD_ID = 'id for monitor'
AND ASSOCIATED_ASSET_ID = A.ID
OR ASSET_FIELD_ID = 'id for printer'
AND ASSOCIATED_ASSET_ID = A.ID
LIMIT 1
))
where
  T.ID in (<TICKET_IDS>)


With this update, I take the asset (from parent ticket) and get the associated assets (from the asset in the parent ticket) and filled my custom field.

See my other post;
http://www.itninja.com/question/dell-k1000-service-desk-select-command-set

Hope it helps someone


 
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