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",
Max(resource.assettag) "Asset Tag",
MAX(nt.macaddress) "MAC Address",
Max(aaauser.first_name) "Assigned User",
MAX(adef.org_name) "Account",
Max(aaov.NAME) "Site",
LONGTODATE(audithistory.audittime) "Last Scanned Date" ,
audithistory.auditstatus "Last Scan Status"
FROM systeminfo workstation
LEFT JOIN resources resource
ON workstation.workstationid = resource.resourceid
LEFT JOIN resourcestate state
ON resource.resourcestateid = state.resourcestateid
LEFT JOIN resourceowner rOwner
ON resource.resourceid = rOwner.resourceid
LEFT JOIN resourceassociation rToAsset
ON rOwner.resourceownerid = rToAsset.resourceownerid
LEFT JOIN departmentdefinition deptDef
ON rOwner.deptid = deptDef.deptid
LEFT JOIN sduser sdUser
ON rOwner.userid = sduser.userid
LEFT JOIN aaauser aaaUser
ON sduser.userid = aaauser.user_id
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
LEFT JOIN accountsitemapping asm on asm.siteid=sitedef.siteid
LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
LEFT JOIN osinfo osInfo
ON workstation.workstationid = osinfo.workstationid
INNER JOIN lastauditinfo
ON workstation.workstationid = lastauditinfo.workstationid
LEFT JOIN ProcessorInfo procInfo ON
workstation.WORKSTATIONID = procInfo.WORKSTATIONID
LEFT JOIN MemoryInfo memInfo ON workstation.WORKSTATIONID=memInfo.WORKSTATIONID
left join harddiskinfo hd on workstation.WORKSTATIONID=hd.WORKSTATIONID
LEFT JOIN Networkinfo nt on nt.workstationid=workstation.workstationid
left join audithistory on workstation.workstationid=audithistory.workstationid
WHERE audithistory.auditid in (Select max(audithistory.auditid) from audithistory group by workstationid) AND adef.org_name='Sample Account'
GROUP BY workstation.workstationid, audithistory.audittime, audithistory.auditstatus
PS : Replace Sample Account with needed account name
New to ADSelfService Plus?
Related Articles
Query to show all asset details ( MSSQL )
Tested in build MSSQL (14306) SELECT MAX("resource"."RESOURCENAME") AS "Asset Name", MAX("resource"."ASSETTAG") AS "Asset Tag",MAX(resource.SERIALNO) AS "Org Serial Number", MAX("product"."COMPONENTNAME") AS "Product", ...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 get Asset contract and its details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) SELECT mcdt.Contractid "URL Contract ID", mcdt.customcontractid "Custom contractid in details page", mcdt.CONTRACTNAME "Contract Name", aao.NAME "Maintenance Vendor Name", LONGTODATE(mcdt.FROMDATE) ...Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...