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 "Total Memory (GB) ",
MAX(procInfo.PROCESSORCOUNT) AS "Processor Count",
max(hd.capacity/(1024*1024*1024) ) as "HDD",
dri.DRIVENAME "Drive Name",
Dri.CAPACITY/1024/1024/1024 "Drive Capacity",
(dri.FREESPACE/1024)/1024/1024 "Drive Free Space",
case when Dri.CAPACITY=0 then null else ((Dri.CAPACITY-Dri.FREESPACE)/1024/1024/1024 ) end "Usage",
Max(deptDef.deptname) "Department",
Max(aaov.NAME) "Site",
MAX(adef.org_name) "Account",
Max(aaauser.first_name) "User",
Max(state.displaystate) "Asset State"
FROM systeminfo workstation
LEFT JOIN resources resource
ON workstation.workstationid = resource.resourceid
LEFT JOIN resourcestate state
ON resource.resourcestateid = state.resourcestateid
LEFT JOIN DriveInfo dri ON workstation.WORKSTATIONID=dri.WORKSTATIONID
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
WHERE ( lastauditinfo.last_success_auditid IS NOT NULL )
GROUP BY workstation.workstationid, dri.DRIVENAME, Dri.CAPACITY, dri.FREESPACE
New to ADSelfService Plus?
Related Articles
Query to show workstation details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Below query will show Only workstations and its details. SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", ...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 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 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", ...Query to show workstation additional fields (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT workstation.WORKSTATIONNAME AS "Machine Name", workstation.MANUFACTURER AS "Manufacturer", workstation.SERVICETAG AS "Service Tag", workstation.MODEL AS "Model", aao.NAME AS "Vendor Name", ...