Query to show list of softwares with last scanned date and license key

Query to show list of softwares with last scanned date and license key

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 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
          • Related Articles

          • Query to show workstations that has a scan status

            PGSQ & 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 ...
          • Query to show software license details

            PGSQL & MSSQL: SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", LONGTODATE(Max(resources.acquisitiondate)) "Acquisition Date",LONGTODATE(Max(resources.expirydate)) "Expiry Date", ...
          • Query to show ticket audit report

            MSSQL: Report 1: Requester, Request ID, Updated by (Technician), Updated Time : select a.first_name as "Requester",wh.workorderid as "Request ID",aa.first_name as "Updated by",longtodate(wh.OPERATIONTIME) as Updated Time from workorderhistory wh LEFT ...
          • Query to show the last worklog added in a ticket

            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",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Query to show last updated field.

            Kindly go to Reports-New Query Report and execute the below query.  PGSQL: SELECT wo.WORKORDERID AS "Request ID",  max(wo.TITLE) AS "Subject",  max(ti.FIRST_NAME) AS "Assigned Technician",  max(aau.FIRST_NAME) AS "Requester",  ...