Assets possessed by user

Assets possessed by user

Hi

Please use the below query to get a report on list of assets possessed by an user, with more details on workstations
Go to Reports--> New Query Report and use the below query

SELECT MAX(productType.COMPONENTTYPENAME) "Product Type",MAX(resource.RESOURCENAME) "Asset Name",LONGTODATE(resource.ACQUISITIONDATE) "Acquisition Date",MAX(product.COMPONENTNAME) "Product",MAX(aao.NAME) "Vendor Name",MAX(state.DISPLAYSTATE) "Asset State",MAX(resource.SCANSERIALNUMBER) "Serial Number",MAX(aaaUser.FIRST_NAME) "User",MAX(workstation.LOGGEDUSER) "Last Logged In User",MAX((((memInfo.TOTALMEMORY/1024)/1024)/1024)) "Total Memory GB",MAX((((memInfo.VIRTUALMEMORY/1024)/1024)/1024)) "Virtual Memory GB",MAX(osInfo.OSNAME) "OS",MAX(osInfo.VERSION) "Version",MAX(procInfo.CPUNAME) "Processor Name",MAX(procInfo.PROCESSORCOUNT) "Processor Count",MAX(procInfo.SPEED) "Processor Speed (in MHz)",MAX(dominf.DOMAINNAME) "Domain Name",MAX(CASE WHEN CAST(compDefLaptop.ISLAPTOP AS CHAR(5)) IS NULL THEN 0 WHEN CAST(compDefLaptop.ISLAPTOP AS CHAR(5)) = '0' THEN 0 ELSE 1 END) "Is Laptop",MAX(deptDef.DEPTNAME) "Department",MAX(resFields.UDF_CHAR1) "Company",MAX(resFields.UDF_CHAR2) "Description" FROM Resources resource LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID LEFT JOIN SystemInfo workstation ON workstation.WORKSTATIONID=resource.RESOURCEID LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID LEFT JOIN ComponentDefinitionLaptop compDefLaptop ON product.COMPONENTID=compDefLaptop.COMPONENTID LEFT JOIN MemoryInfo memInfo ON workstation.WORKSTATIONID=memInfo.WORKSTATIONID LEFT JOIN OsInfo osInfo ON workstation.WORKSTATIONID=osInfo.WORKSTATIONID 
LEFT JOIN ProcessorInfo procInfo ON workstation.WORKSTATIONID=procInfo.WORKSTATIONID LEFT JOIN VendorDefinition resourceVendor ON resource.VENDORID=resourceVendor.VENDORID LEFT JOIN SDOrganization aao ON resourceVendor.VENDORID=aao.ORG_ID 
LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID  LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID 
LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID LEFT JOIN DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID LEFT JOIN SystemInfoDomain sysInfod ON workstation.WORKSTATIONID=sysInfod.WORKSTATIONID 
LEFT JOIN DomainInfo dominf ON sysInfod.DOMAINID=dominf.DOMAINID LEFT JOIN Resource_Fields resFields ON resource.RESOURCEID=resFields.RESOURCEID   GROUP BY resource.RESOURCEID ORDER BY 8

Regards
Bharatha Selvan.



              New to ADManager Plus?

                New to ADSelfService Plus?