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) ...How do I convert the database from MSSQL to PGSQL ?
Kindly plan to perform this process ONLY during Non Operational hours. You can do this process in the same server that you have SDP MSP installed. Step 1: Stop the application and Take a backup of the existing data in the application. Navigate to ...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 workstation's hard disk details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", Max(osinfo.osname) "OS", MAX(memInfo.TOTALMEMORY/(1024*1024*1024)) AS ...