KACE Product Support

select

  HD_TICKET.ID

  ,SUBSTRING_INDEX(SUBSTRING_INDEX(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', n.n), ',', -1) as 'it_need'

  ,concat(HD_TICKET.CUSTOM_FIELD_VALUE0, ", ", HD_TICKET.CUSTOM_FIELD_VALUE1, ", ", HD_TICKET.CUSTOM_FIELD_VALUE2) as employee_name

  ,HD_TICKET.CUSTOM_FIELD_VALUE4 as supervisor_name

  ,HD_TICKET.CUSTOM_FIELD_VALUE5 as supervisor_phone

  ,ifnull(SUBMITTER.USER_NAME, "Unassigned") as submitter_name

  ,CAT.NAME as category

  ,case SUBSTRING_INDEX(SUBSTRING_INDEX(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', n.n), ',', -1)

    when "Hardware:Tablet" then "Hardware Group"

    when "Hardware:1 monitor" then "Hardware Group"

    when "Hardware:2 monitors" then "Hardware Group"

    when "Hardware:Oversize Monitor" then "Hardware Group"

    when "Hardware:Network Copier/Printer" then "Hardware Group"

    when "Hardware:Network Scanner" then "Hardware Group"

    when "Hardware:Desk Printer" then "Hardware Group"

    when "Network" then "Network Group"

    when "Other(s)" then "Other Group"

    when "Printer/Copier/Scanner" then "Printers Copiers Scanners Group"

    end as owner_name

  ,"tech.support" as EMAILCOLUMN

from

  HD_TICKET

  join HD_TICKET_CHANGE C on HD_TICKET.ID = HD_TICKET_ID and C.ID = <CHANGE_ID>

  cross join (select a.N + b.N * 10 + 1 n from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a, (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b order by n) n

  left join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID

  join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID

where

  C.DESCRIPTION like '%CREATED%'

  and n.n <= 1 + (length(HD_TICKET.CUSTOM_FIELD_VALUE7) - length(REPLACE(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', '')))

  and HD_TICKET.CUSTOM_FIELD_VALUE7 != ""