Query to get the asset details , mac address , last scanned, status alone with user details
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?
Resources
Related Articles
Query to show all asset details_ MSSQL
MSSQL: 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", MAX("productType"."COMPONENTTYPENAME") AS "Product ...Last Login Report - All Users
SELECT AaaUser.FIRST_NAME "Users", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP Address", longtodate(MAX(AaaAccSession.OPENTIME)) "Logged In Time" , longtodate(MAX(AaaAccSession.CLOSETIME)) ...When will the Assets get duplicated ?
Please find the possible scenarios when the machines are renamed with _old Scenario 1: When the workstations are swapped in the network. Consider two workstations, MACHINE-A with service tag ST-1 and MACHINE-B with ST-2. Both the workstations are ...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 get Asset contract and its details
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) "From Date", ...