Query to show number of requests raised per Asset (MSSQL)

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