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

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
        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 ADManager Plus?

        New to ADSelfService Plus?


              • 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",       ...