Reporte de "Todos los activos" con "Dirección IP" en ServiceDesk Plus / AssetExplorer

Reporte de "Todos los activos" con el campo "Dirección IP" en ServiceDesk Plus / AssetExplorer

El siguiente query para generar informes en ServiceDesk Plus / AssetExplorer presenta todos los dispositivos registrados, incluyendo los campos siguientes:
  1. Nombre del activo
  2. Estado del activo
  3. Tipo de activo
  4. Tipo de producto
  5. Asociado a
  6. Usuario asignado
  7. Departamento asignado
  8. Número de serie de la organización
  9. Dirección IP
  10. Número de serie del fabricante

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

Este query puede ser ejecutado desde la consola ServiceDesk Plus / Asset Explorer, en la sección Informes  > Nuevo informe > Informe de consulta.

¿Tiene consultas o necesita asesoría? ¡Contáctenos!

Genotech
Soluciones Tecnológicas Empresariales