Query report ServiceDesk Plus and Asset Explorer

Query report ServiceDesk Plus / Asset Explorer

El siguiente query para reportes de ServiceDesk Plus / Asset Explorer muestra los siguientes campos de todos los dispositivos registrados (Sin filtros):

Gestor de base de datos: MSSQL.
  1. Device Name.
  2. Device Assigned to User.
  3. Device IP Address.
  4. Device Domain.
  5. Device Manufacturer.
  6. Device Model.
  7. Devie Operate System (OS).
  8. Device Region.
  9. Device Site.
  10. Device Location.
  11. Device Date Last Scanned (dd/MM/yyyy hh:mm).
  12. Device Last Logged User.
  13. Software Name.
  14. Software Type.
  15. Software Category.
  16. Software Manufacturer.
  17. Software Version.
  18. Software Scanned License Key.
  19. Software Allocated License Key.
  20. Software Installed On (dd/MM/yyyy hh:mm).

SELECT MAX(workstation.WORKSTATIONNAME) AS "Machine Name", MAX(aaaUser.FIRST_NAME) AS "User", 
max(net.ipaddress) "Ipaddress",
MAX(dominf.DOMAINNAME) AS "Domain Name", MAX(workstation.MANUFACTURER) AS "Manufacturer", MAX(workstation.MODEL) AS "Model", MAX(osInfo.OSNAME) AS "OS", MAX(regionDef.REGIONNAME) AS "Region", MAX(aaov.NAME) AS "Site", MAX(resLocation.LOCATION) AS "Location", MAX(workstation.LOGGEDUSER) AS "Last Logged In User",
LONGTODATE(max(AUDITHISTORY.AUDITTIME)) "Last Scan Date",
max(AuditHistory.AUDITSTATUS) "Aduit Status",
(SoftwareList.SOFTWARENAME) "Software_Name",
MAX(swmfg.NAME) "Software Manufacturer",
 longtodate(max(SoftwareInfo.FILECREATEDTIME)) "Installed On",
MAX(SoftwareType.SOFTWARETYPE) "Software_Type",
MAX(SoftwareCategory.SOFTWARECATEGORY) "Software_Category",
MAX(SoftwareInfo.FILEVERSION) "File Version",
MAX(SWInstallationKeyInfo.PRODUCTID) "Software scanned License Key",
MAX(SWInstallationKeyInfo.PRODUCTKEY) "Software Allocated License Key" FROM SystemInfo workstation LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID 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 ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID LEFT JOIN RegionDefinition regionDef ON resLocation.REGIONID=regionDef.REGIONID LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID LEFT JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID LEFT JOIN OsInfo osInfo ON workstation.WORKSTATIONID=osInfo.WORKSTATIONID LEFT JOIN SystemInfoDomain sysInfod ON workstation.WORKSTATIONID=sysInfod.WORKSTATIONID LEFT JOIN DomainInfo dominf ON sysInfod.DOMAINID=dominf.DOMAINID left join networkinfo net on workstation.WORKSTATIONID=net.WORKSTATIONID 
LEFT JOIN LastAuditInfo ON resource.RESOURCEID=LastAuditInfo.WORKSTATIONID
LEFT JOIN AuditHistory LASTSUCCESSAUDIT ON LastAuditInfo.LAST_SUCCESS_AUDITID=LASTSUCCESSAUDIT.AUDITID
LEFT JOIN AuditHistory ON LastAuditInfo.LAST_AUDITID=AuditHistory.AUDITID
LEFT JOIN SoftwareInfo ON workstation.WORKSTATIONID=SoftwareInfo.WORKSTATIONID    
LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID 
LEFT JOIN SoftwareManufacturer swmfg ON SoftwareList.SWMANUFACTURERID=swmfg.SWMANUFACTURERID 
LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID 
LEFT JOIN WmiSWUsageInfo ON SoftwareInfo.SOFTWAREINFOID=WmiSWUsageInfo.SOFTWAREINFOID 
LEFT JOIN SWInstallationKeyInfo ON SoftwareInfo.SOFTWAREINFOID=SWInstallationKeyInfo.SOFTWAREINFOID 
LEFT JOIN SoftwareCategory ON SoftwareList.SOFTWARECATEGORYID=SoftwareCategory.SOFTWARECATEGORYID 
GROUP BY workstation.WORKSTATIONID,SoftwareList.SOFTWARENAME  
order by 1

Este query puede ser ejecutado desde la consola ServiceDesk Plus / Asset Explorer, en la sección Reports > New Query Report.

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

Genotech
Administración de Servicios de TI