Asset Management - report to get the list of inventoried workstations/servers along with their details
SELECT Max(workstation.workstationname) "Workstation",
Max(workstation.model) "Model",
Max(workstation.servicetag) "Service Tag",
Max(state.displaystate) "Asset State",
Max(osinfo.osname) "OS",
Max(aaauser.first_name) "User",
Max(deptDef.deptname) "Department",
Max(aaov.NAME) "Site" 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 osinfo osInfo
ON workstation.workstationid = osinfo.workstationid
INNER JOIN lastauditinfo
ON workstation.workstationid = lastauditinfo.workstationid
WHERE ( lastauditinfo.last_success_auditid IS NOT NULL )
GROUP BY workstation.workstationid
Click this link to navigate to the next report.