Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306)
Query report to get the successfully scanned assets with audit status, state , Serial no and site.
SELECT max(resource.resourcename) "Asset Name",
max(resource.SERIALNO) AS "Org Serial Number",
max(state.DISPLAYSTATE) AS "Asset State",
LongtoDate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",
(Max(audithistory.auditstatus)) "Aduit Status",
max(aaov.NAME) AS "Site"
FROM
SystemInfo workstation
LEFT JOIN resources resource on workstation.workstationid = resource.resourceid
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 ResourceState state ON resource.RESOURCESTATEID = state.RESOURCESTATEID
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
max(state.DISPLAYSTATE) AS "Asset State",
LongtoDate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",
(Max(audithistory.auditstatus)) "Aduit Status",
max(aaov.NAME) AS "Site"
FROM
SystemInfo workstation
LEFT JOIN resources resource on workstation.workstationid = resource.resourceid
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 ResourceState state ON resource.RESOURCESTATEID = state.RESOURCESTATEID
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
New to ADSelfService Plus?
Related Articles
Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query to show list of softwares with last scanned date and license key (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) select systeminfo.workstationname "Workstation Name", SoftwareList.SOFTWARENAME "Software Name", LONGTODATE(SoftwareInfo.filecreatedtime) "Software Installed Date", LONGTODATE(audithistory.audittime) ...Query to show workstations that has a scan status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: Below query that will show the Assets that has a scan status. select systeminfo.workstationname "Workstation Name", LONGTODATE(audithistory.audittime) "Last Scanned on", ...Query to get the asset details , mac address , last scanned, status alone with user details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Query to get the asset details , mac address , last scanned, status alone with user details SELECT Max(workstation.workstationname) "Asset Name", Max(workstation.servicetag) "Service Tag", ...