Query to show workstation details. PGSQL & MSSQL

Query to show workstation details. PGSQL & MSSQL

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?

        Resources

            • Related Articles

            • 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 extract workstation name with network details

              Sample Table For Postgres SELECT ad.org_name "Account", MAX("workstation"."WORKSTATIONNAME") AS "Machine Name", MAX(net.ipaddress) "IP Address", MAX(net.macaddress) "MAC Address", MAX(net.nicname) "NIC", CASE WHEN (MAX(net.isdhcp)='true') THEN 'YES' ...
            • Query to show workstation's hard disk details

              MSSQL: SELECT Max(workstation.workstationname) "Workstation",         Max(workstation.servicetag)      "Service Tag",        Max(workstation.model)           "Model",         Max(osinfo.osname)               "OS",   ...
            • Query to show Software license details with additional fields, allocated workstations and its users with email address

              PGSQL & MSSQL: SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Managed Software", slt.licensetype "License Type", Max(softwarelicenses.licensekey) "License Key", softwarelicenses.installationscount "No of CALs", ...
            • 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 ...