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",
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",
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 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
Including CI Type with workstation
SELECT MAX("workstation"."WORKSTATIONNAME") AS "Machine Name", citype.TYPENAME AS "CI Type", MAX("workstation"."MANUFACTURER") AS "Manufacturer", MAX("workstation"."MODEL") AS "Model", MAX("workstation"."SERVICETAG") AS "Service Tag", MAX("workstation"."LOGGEDUSER") AS "Last Logged In User", MAX("state"."DISPLAYSTATE") AS "Asset State", MAX("resource"."WARRANTYEXPIRY") AS "Warranty Expiry", MAX("osInfo"."OSNAME") AS "OS", MAX("aaov"."NAME") AS "Site", MAX("resLocation"."LOCATION") AS "Location", MAX("resFields"."UDF_CHAR1") AS "Asset Support", MAX("ad"."ORG_NAME") AS "Account", MAX("resFields"."UDF_CHAR2") AS "BU Number & Name", MAX("deptDef"."DEPTNAME") AS "Department" FROM "SystemInfo" "workstation" LEFT JOIN "Resource_Fields" "resFields" ON "workstation"."WORKSTATIONID"="resFields"."RESOURCEID" 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 CI ci on resource.ciid=ci.ciid LEFT JOIN CIType citype ON ci.CITYPEID=citype.TYPEID LEFT JOIN "DepartmentDefinition" "deptDef" ON "rOwner"."DEPTID"="deptDef"."DEPTID" 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" INNER JOIN "AccountSiteMapping" "asm" ON "resource"."SITEID"="asm"."SITEID" INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" LEFT JOIN "OsInfo" "osInfo" ON "workstation"."WORKSTATIONID"="osInfo"."WORKSTATIONID" GROUP BY "workstation"."WORKSTATIONID", CIType.TYPENAME
New to ADSelfService Plus?
Related Articles
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 ...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 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", ...Query to show to Assets with their IP and MAC Address (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name", networkinfo.IPADDRESS "IP Address", networkinfo.macaddress "MAC Address", product.COMPONENTNAME AS ...Query to show Contract details with assets associated (PGSQL)
Tested in PGSQL build (14300) SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ...