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) "Last Scanned Date" ,
sink.productkey "Scanned License Key"
from audithistory
left join systeminfo on systeminfo.workstationid=audithistory.workstationid
LEFT JOIN SoftwareInfo ON SystemInfo.WORKSTATIONID=SoftwareInfo.WORKSTATIONID
LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID
LEFT JOIN SWinstallationkeyinfo sink ON SoftwareInfo.SoftwareInfoid=sink.SoftwareInfoid
where audithistory.auditstatus='SUCCESS'
and audithistory.auditid in (Select max(audithistory.auditid) from audithistory group by workstationid) ORDER BY 1
New to ADSelfService Plus?
Related Articles
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 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 show software license details (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", LONGTODATE(Max(resources.acquisitiondate)) "Acquisition ...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", ...