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 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 extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...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", ...Query to show Problem - Task Report with Problem Details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Attached the sample query output. Query: SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title",catadef.CATEGORYNAME AS "Category", longtodate(prob.CLOSEDTIME) AS "Closed Date", ...