Request Management - query to fetch information on service catalog additional fields

Requests with service catalog additional fields.

Service catalog additional fields can be a common field or category specific fields. These fields are stored in a dynamic table, hence we need to join the specific template to get the resultant.

Run the below query to get table name and column name of the field you need in the report.

select * from columnaliases where aliasname='NAME OF THE ADDITIONAL FIELD';

Replace table name and column name in the below query.

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       qd.QUEUENAME AS "Group",
       aau.FIRST_NAME AS "Requester",
       ti.FIRST_NAME AS "Technician",
       std.STATUSNAME AS "Request Status",
       sereq.UDF_CHAR1 "Location",
       sereq.UDF_CHAR2 "Floor",
       sereq.UDF_CHAR3 "City" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN ServiceReq_301 sereq ON wo.workorderid=sereq.workorderid
WHERE (wo.ISPARENT='1')