Query to show all asset details ( MSSQL )

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",
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

                  New to ADSelfService Plus?