Query to show number of requests raised per Asset (MSSQL)
Tested in Build MSSQL (14306)
SELECT ci.ciname 'Asset Name', cd.COMPONENTNAME 'Product', cty.COMPONENTTYPENAME 'Product Type',COUNT(wo.ciid) 'Total Requests raised for this asset' FROM workorder wo left join CI ON ci.ciid=wo.ciid LEFT JOIN RESOURCES res ON ci.ciid=res.ciid LEFT JOIN ComponentDefinition cd ON res.COMPONENTID=cd.COMPONENTID LEFT JOIN ComponentType cty ON cd.COMPONENTTYPEID=cty.COMPONENTTYPEID WHERE wo.ISPARENT=1 AND wo.ciid IS NOT NULL GROUP BY ci.ciname, cd.COMPONENTNAME, cty.COMPONENTTYPENAME, wo.ciid ORDER BY 1
Number of requests by workstation:
SELECT Max(workstation.workstationname) "Workstation",
Max(workstation.model) "Model",
Max(workstation.servicetag) "Service Tag",
Max(state.displaystate) "Asset State",
Max(workstation.loggeduser) "Last Logged in User",
MAX(adef.org_name) "Account",
Max(aaov.NAME) "Site",
COUNT(wo.ciid) 'Total Requests raised for this asset'
FROM systeminfo workstation
LEFT JOIN resources resource
ON workstation.workstationid = resource.resourceid
LEFT JOIN resourcestate state
ON resource.resourcestateid = state.resourcestateid
LEFT JOIN resourceowner rOwner
ON resource.resourceid = rOwner.resourceid
LEFT JOIN resourceassociation rToAsset
ON rOwner.resourceownerid = rToAsset.resourceownerid
LEFT JOIN departmentdefinition deptDef
ON rOwner.deptid = deptDef.deptid
LEFT JOIN sduser sdUser
ON rOwner.userid = sduser.userid
LEFT JOIN aaauser aaaUser
ON sduser.userid = aaauser.user_id
LEFT JOIN resourcelocation resLocation
ON resource.resourceid = resLocation.resourceid
LEFT JOIN sitedefinition siteDef
ON resLocation.siteid = siteDef.siteid
LEFT JOIN sdorganization aaov
ON siteDef.siteid = aaov.org_id
LEFT JOIN accountsitemapping asm on asm.siteid=sitedef.siteid
LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
LEFT JOIN osinfo osInfo
ON workstation.workstationid = osinfo.workstationid
INNER JOIN lastauditinfo
ON workstation.workstationid = lastauditinfo.workstationid
LEFT JOIN ProcessorInfo procInfo ON
workstation.WORKSTATIONID = procInfo.WORKSTATIONID
LEFT JOIN MemoryInfo memInfo ON workstation.WORKSTATIONID=memInfo.WORKSTATIONID
left join harddiskinfo hd on workstation.WORKSTATIONID=hd.WORKSTATIONID
LEFT JOIN CI on resource.ciid=ci.ciid
LEFT JOIN workorder wo ON ci.ciid=wo.ciid
WHERE ( lastauditinfo.last_success_auditid IS NOT NULL )
GROUP BY workstation.workstationid
New to ADSelfService Plus?
Related Articles
Query to show workstation additional fields (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT workstation.WORKSTATIONNAME AS "Machine Name", workstation.MANUFACTURER AS "Manufacturer", workstation.SERVICETAG AS "Service Tag", workstation.MODEL AS "Model", aao.NAME AS "Vendor Name", ...Query to show all asset details ( MSSQL )
Tested in build MSSQL (14306) SELECT MAX("resource"."RESOURCENAME") AS "Asset Name", MAX("resource"."ASSETTAG") AS "Asset Tag",MAX(resource.SERIALNO) AS "Org Serial Number", MAX("product"."COMPONENTNAME") AS "Product", ...Query to show Approved tickets per Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL ...Query to show Asset state history ( MSSQL)
Tested in MSSQL build (14306) SELECT res.resourcename 'Asset Name', res.assettag 'Asset Tag',res.serialno 'Asset Serial No.', rs2.statedesc 'Previous State',rs1.statedesc 'Current State',DATEADD(s,DATEDIFF(s,GETUTCDATE() ,getdate()) + ...Query report to show Open requests without open tasks
PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...