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",
Max(aaauser.first_name) "User" FROM resources resource
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 departmentdefinition deptDef
ON rOwner.deptid = deptDef.deptid
LEFT JOIN resourcelocation resLocation
ON resource.resourceid = resLocation.resourceid
LEFT JOIN networkinfo net
ON resource.resourceid = 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 ComponentDefinition product
ON resource.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType
ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
where productType.COMPONENTTYPENAME !='software'
GROUP BY resource.resourcename
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 to show Last Status Changed Time and Time spent in previous status ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group", std.STATUSNAME "Request Status", ...Query to take report of all techinician details with last login time (V10.6) (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query is compatiable with version 10.6 and above: Query: Select au.FIRST_NAME "Technicianname" , ar.NAME "Role",al.name "Login Name",aci.emailid "EmailID",SDUSER.STATUS ...Query to show problem time spent along with last activity on a problem (MSSQL)
Tested in MSSQL build (14306) SELECT "prob"."PROBLEMID" AS "Problem ID", "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status", "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...Query to show last conversation and last notification in a request ( MSSQL )
Tested in Build MSSQL (14306) Query SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", (select conversationdescription.description from ...