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",
audithistory.auditstatus "Scan status"
from audithistory
left join systeminfo on systeminfo.workstationid=audithistory.workstationid
where audithistory.auditid in (Select max(audithistory.auditid) from audithistory group by workstationid)
Below query shows only failed scan status.
select systeminfo.workstationname "Workstation Name",
LONGTODATE(audithistory.audittime) "Last Scanned on",
audithistory.auditstatus "Scan status"
from audithistory
left join systeminfo on systeminfo.workstationid=audithistory.workstationid
where audithistory.auditstatus!='SUCCESS'
and audithistory.auditid in (Select max(audithistory.auditid) from audithistory group by workstationid)
New to ADSelfService Plus?
Related Articles
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 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", ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Last scanned time
SELECT resource.resourcename "Asset Name", Max(net.ipaddress) "IP Address", Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date", Longtodate(Max(audithistory.audittime)) "Last Scan Date", Max(audithistory.auditstatus) "Audit Status", ...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", ...