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",
MAX("productType"."COMPONENTTYPENAME") AS "Product Type",
MAX("state"."DISPLAYSTATE") AS "Asset State", MAX("rtype"."TYPE") AS "Asset Type",
MAX("aaaUser"."FIRST_NAME") AS "User",Max(sduser.employeeid) "Employee ID",
MAX("deptDef"."DEPTNAME") AS "Department", MAX("aaov"."NAME") AS "Site",
MAX("resToCost"."TOTALCOST") AS "Total Cost", dateadd(ss,convert(int,left(min(rsh.starttime),10)) ,'1970-01-01') "Asset added date",MAX("ad"."ORG_NAME") AS "Account" FROM "Resources" "resource"
LEFT JOIN "ComponentDefinition" "product" ON "resource"."COMPONENTID"="product"."COMPONENTID"
LEFT JOIN "ComponentType" "productType" ON "product"."COMPONENTTYPEID"="productType"."COMPONENTTYPEID"
LEFT JOIN "ResourceType" "rtype" ON "productType"."RESOURCETYPEID"="rtype"."RESOURCETYPEID"
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 "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 "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 "ResourceToCost" "resToCost" ON "resource"."RESOURCEID"="resToCost"."RESOURCEID"
INNER JOIN "AccountSiteMapping" "asm" ON "resource"."SITEID"="asm"."SITEID"
INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID"
LEFT JOIN resourcestatehistory rsh on rsh.resourceid=resource.resourceid GROUP BY "resource"."RESOURCEID"
With more columns:
SELECT MAX("workstation"."WORKSTATIONNAME") AS "Machine Name",
resource.resourcename "Asset Name", citype.TYPENAME AS "CI Type",
MAX(net.ipaddress) "IP Address",
MAX("workstation"."MANUFACTURER") AS "Manufacturer",
Max(aaauser.first_name) "User",
Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",
Longtodate(Max(audithistory.audittime)) "Last Scan Date", Max(audithistory.auditstatus) "Audit Status",
MAX("workstation"."MODEL") AS "Model",
MAX("workstation"."SERVICETAG") AS "Service Tag",
MAX("workstation"."LOGGEDUSER") AS "Last Logged In User",
MAX("state"."DISPLAYSTATE") AS "Asset State",
MAX("resource"."WARRANTYEXPIRY") AS "Warranty Expiry",
MAX("osInfo"."OSNAME") AS "OS", MAX("aaov"."NAME") AS "Site",
MAX("resLocation"."LOCATION") AS "Location",
MAX("resFields"."UDF_CHAR1") AS "Asset Support",
MAX("ad"."ORG_NAME") AS "Account",
MAX("resFields"."UDF_CHAR2") AS "BU Number & Name",
MAX("deptDef"."DEPTNAME") AS "Department" FROM "SystemInfo" "workstation"
LEFT JOIN "Resource_Fields" "resFields" ON "workstation"."WORKSTATIONID"="resFields"."RESOURCEID"
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 lastauditinfo ON resource.resourceid = lastauditinfo.workstationid
LEFT JOIN audithistory LASTSUCCESSAUDIT ON lastauditinfo.last_success_auditid=LASTSUCCESSAUDIT.auditid
LEFT JOIN networkinfo net ON resource.resourceid = net.workstationid
LEFT JOIN audithistory ON lastauditinfo.last_auditid = audithistory.auditid
LEFT JOIN "ResourceAssociation" "rToAsset" ON "rOwner"."RESOURCEOWNERID"="rToAsset"."RESOURCEOWNERID"
LEFT JOIN CI ci on resource.ciid=ci.ciid LEFT JOIN CIType citype ON ci.CITYPEID=citype.TYPEID
LEFT JOIN 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 "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"
INNER JOIN "AccountSiteMapping" "asm" ON "resource"."SITEID"="asm"."SITEID"
INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID"
LEFT JOIN "OsInfo" "osInfo" ON "workstation"."WORKSTATIONID"="osInfo"."WORKSTATIONID"
GROUP BY "workstation"."WORKSTATIONID", CIType.TYPENAME, resource.resourcename, net.ipaddress ORDER BY 2