Query to fetch Checklist Information
DB Compatibility : PGSQL & MSSQL
Build Compatibility : Builds above 11107
PGSQL :
SELECT wo.WORKORDERID AS "Request ID",
aau.FIRST_NAME AS "Requester",
wo.TITLE AS "Subject",
cd.CATEGORYNAME AS "Category",
scd.NAME AS "Subcategory",
icd.NAME AS "Item",
pd.PRIORITYNAME AS "Priority",
qd.QUEUENAME AS "Group",
std.STATUSNAME AS "Request Status",
dpt.DEPTNAME AS "Department",
ti.FIRST_NAME AS "Technician",
longtodate(wo.CREATEDTIME) AS "Created Time",
longtodate(wo.COMPLETEDTIME) AS "Completed Time",
rrs.RESOLUTION AS "Resolution",
rtl.templatename as "Template Name",
mc.title as "Checklist name",
mc.status as "Checklist verified or not",
(SELECT string_agg(concat(mcl.fieldname , ' : ' , (case mcl.field_type when 'Numeric' then mcl.cl_long::varchar when 'Boolean' then mcl.cl_bool::varchar else mcl.cl_char end ) ), ' , ' ) FROM workordertochecklist wcl
left join modulechecklist mc1 on wcl.checklistid=mc1.checklistid
left join modulechecklistitem mcl on mc1.checklistid=mcl.checklistid
where wcl.workorderid=wo.workorderid and mc1.checklistid = mc.checklistid) as "ChecklistData" FROM workorder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
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 SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN RequestResolution rrs ON wo.WORKORDERID=rrs.REQUESTID
LEFT JOIN requesttemplate_list rtl ON wo.templateid = rtl.templateid
LEFT JOIN workordertochecklist wcl ON wo.WORKORDERID = wcl.WORKORDERID
left join modulechecklist mc on wcl.checklistid=mc.checklistid
where wo.ISPARENT='1' and wo.createdtime >= <from_thisweek> and wo.createdtime <= <to_thisweek>
MSSQL :
SELECT wo.WORKORDERID AS "Request ID",
aau.FIRST_NAME AS "Requester",
wo.TITLE AS "Subject",
cd.CATEGORYNAME AS "Category",
scd.NAME AS "Subcategory",
icd.NAME AS "Item",
pd.PRIORITYNAME AS "Priority",
qd.QUEUENAME AS "Group",
std.STATUSNAME AS "Request Status",
dpt.DEPTNAME AS "Department",
ti.FIRST_NAME AS "Technician",
longtodate(wo.CREATEDTIME) AS "Created Time",
longtodate(wo.COMPLETEDTIME) AS "Completed Time",
rrs.RESOLUTION AS "Resolution",
rtl.templatename as "Template Name",
mc.title as "Checklist name",
mc.status as "Checlist verified or not",
cast(STUFF(( SELECT ',' + mcl.fieldname + ':' + (case mcl.field_type when 'Numeric' then cast(mcl.cl_long as varchar) when 'Boolean' then cast(mcl.cl_bool as varchar) else mcl.cl_char end ) FROM workordertochecklist wcl
left join modulechecklist mc1 on wcl.checklistid=mc1.checklistid
left join modulechecklistitem mcl on mc1.checklistid=mcl.checklistid
where wcl.workorderid=wo.workorderid and mc1.checklistid = mc.checklistid
FOR XML PATH('')),1,1,'') as varchar(150)) as "ChecklistData" FROM workorder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
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 SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN RequestResolution rrs ON wo.WORKORDERID=rrs.REQUESTID
LEFT JOIN requesttemplate_list rtl ON wo.templateid = rtl.templateid
LEFT JOIN workordertochecklist wcl ON wo.WORKORDERID = wcl.WORKORDERID
left join modulechecklist mc on wcl.checklistid=mc.checklistid
where wo.ISPARENT='1' and wo.createdtime >= <from_thisweek> and wo.createdtime <= <to_thisweek>
In the above query, you can replace the <from_thisweek> and <to_thisweek> with the desired date filter. You can look for the available list of date filters here - Date Filter
New to ADSelfService Plus?
Related Articles
Query to fetch the fields in the templates (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", ...Query to show change planning details with total worklog hours
Working on Build: 14500 MSSQL: SELECT cdt.changeid "Change ID", cr.rolloutplan "Roll Out Plan", cr.backoutplan "Back Out Plan", cr.checklist "Check List", ...Query Executor Tool for PostGres
This tool is designed to execute queries in the customer environment by connecting the database by reading the database configuration file. We need to enter the query that we require to execute in queryToExecute.txt file. We can enter multiple ...How to check System Info using WBEMTEST
There are issues where the Service Tag , Serial Number or other system information are not correct in the scan results.We can get BIOS & other setting from the wbemtest tool (Inbuild Microsoft WMI tool) and compare it with the results ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...