Report of "All Assets" with the "IP Address" field in ServiceDesk Plus / AssetExplorer

Report of "All Assets" with the "IP Address" field in ServiceDesk Plus / AssetExplorer

The following query to generate reports in ServiceDesk Plus / AssetExplorer presents all registered devices, including the following fields:
  1. Asset Name
  2. Asset State
  3. Asset Type
  4. Associated To
  5. User
  6. Department
  7. Org Serial Number
  8. IP Address
  9. Manufacturer Serial Number

MSSQL

SELECT MAX("resource"."RESOURCENAME") AS "Asset Name", MAX("state"."DISPLAYSTATE") AS "Asset State", MAX("rtype"."TYPE") AS "Asset Type", MAX("productType"."COMPONENTTYPENAME") AS "Product Type", MAX("rToAssetResource"."RESOURCENAME") AS "Associated To", MAX("aaaUser"."FIRST_NAME") AS "User", MAX("deptDef"."DEPTNAME") AS "Department", MAX("resource"."SERIALNO") AS "Org Serial Number", MAX("resource"."IPADDRESSES") AS "IP Address", MAX("resource"."SCANSERIALNUMBER") AS "Manufacturer Serial No" 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 "Resources" "rToAssetResource" ON "rToAsset"."ASSTTORESOURCEID" = "rToAssetResource"."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" GROUP BY "resource"."RESOURCEID"

PGSQL

SELECT MAX(resource.RESOURCENAME) "Asset Name", MAX(state.DISPLAYSTATE) "Asset State", MAX(rtype.TYPE) "Asset Type", MAX(productType.COMPONENTTYPENAME) "Product Type", MAX(rToAssetResource.RESOURCENAME) "Associated To", MAX(aaaUser.FIRST_NAME) "User", MAX(deptDef.DEPTNAME) "Department", MAX(resource.SERIALNO) "Org Serial Number", MAX(resource.IPADDRESSES) "IP Address", MAX(resource.SCANSERIALNUMBER) AS "Manufacturer Serial No" 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 Resources rToAssetResource ON rToAsset.ASSTTORESOURCEID = rToAssetResource.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 GROUP BY resource.RESOURCEID

This query can be executed from the ServiceDesk Plus / Asset Explorer console, in the Reports > New report > Query Report section.

Do you have questions or need advice? ¡Contct us!

Genotech
Business Technology Solutions