Version : 13000

DB :MSSQL

OUTPUT :

SELECT MAX("workstation"."WORKSTATIONNAME") AS "Machine Name", MAX("ad"."ORG_NAME") AS "Account", MAX("resource"."ACQUISITIONDATE") AS "Acquisition Date", MAX("resFields"."UDF_CHAR6") AS "Asset Description", MAX("state"."DISPLAYSTATE") AS "Asset State", MAX("rToAssetResource"."RESOURCENAME") AS "Associated To", MAX("barcodes"."BARCODE") AS "Barcode", MAX("osInfo"."BUILDNUMBER") AS "Build Number", MAX("chassisType"."NAME") AS "Chassis Type", MAX("resFields"."UDF_CHAR5") AS "CI Business Unit", MAX("compGrps"."NAME") AS "Computer Group", MAX("resToCost"."CURRENTCOST") AS "Current Book Value", MAX("deptDef"."DEPTNAME") AS "Department", MAX("dominf"."DOMAINNAME") AS "Domain Name", MAX("resFields"."UDF_CHAR9") AS "DR Required", MAX("resFields"."UDF_CHAR9") AS "DR Required", MAX("resource"."EXPIRYDATE") AS "Expiry Date", MAX("resFields"."UDF_CHAR8") AS "Financially Required -", MAX("resFields"."UDF_CHAR3") AS "IMEI", MAX("resFields"."UDF_CHAR7") AS "IT Fixed Assets", MAX("kbInfo"."MANUFACTURER") AS "Keyboard Manufacturer", MAX("kbInfo"."KEYBOARDSERIALNUMBER") AS "Keyboard Serial Number", MAX("kbInfo"."KEYBOARDTYPE") AS "Keyboard Type", MAX("workstation"."LOGGEDUSER") AS "Last Logged In User", MAX("resLease"."ENDTIME") AS "Loan End", MAX("resLease"."STARTTIME") AS "Loan Start", MAX("resLocation"."LOCATION") AS "Location", MAX("workstation"."LOGICALCPUCOUNT") AS "Logical Processors", MAX("resFields"."UDF_CHAR10") AS "Mac Address", MAX("resFields"."UDF_CHAR1") AS "Manufacture Date", MAX("workstation"."MANUFACTURER") AS "Manufacturer", MAX("workstation"."MODEL") AS "Model", MAX("moniInfo"."MANUFACTURER") AS "Monitor Manufacturer", MAX("moniInfo"."MAXRESOLUTION") AS "Monitor MaxResolution", MAX("moniInfo"."SERIALNUMBER") AS "Monitor Serial Number", MAX("moniInfo"."MONITORTYPE") AS "Monitor Type", MAX("mouseInfo"."MOUSEBUTTONS") AS "Mouse Buttons", MAX("mouseInfo"."MOUSEMANUFACTURER") AS "Mouse Manufacturer", MAX("mouseInfo"."MOUSESERIALNUMBER") AS "Mouse Serial Number", MAX("mouseInfo"."MOUSETYPE") AS "Mouse Type", MAX("resFields"."UDF_CHAR4") AS "Notes", MAX("resToCost"."OPERATIONALCOST") AS "Operational Cost", MAX("resource"."SERIALNO") AS "Org Serial Number", MAX("osInfo"."OSNAME") AS "OS", MAX("procInfo"."PROCESSORCOUNT") AS "Processor Count", MAX("procInfo"."CPUNAME") AS "Processor Name", MAX("procInfo"."SPEED") AS "Processor Speed (in MHz)", MAX("procInfo"."VENDOR") AS "Processor Vendor", MAX("product"."COMPONENTNAME") AS "Product", MAX("osInfo"."PRODUCTID") AS "Product ID", MAX("resToCost"."PURCHASECOST") AS "Purchase Cost", MAX("resFields"."UDF_CHAR2") AS "Purchase Order", MAX("regionDef"."REGIONNAME") AS "Region", MAX("osInfo"."SERVICEPACK") AS "Service Pack", MAX("workstation"."SERVICETAG") AS "Service Tag", MAX("aaov"."NAME") AS "Site", MAX("scInfo"."SOUNDCARDNAME") AS "Sound Card Name", MAX("osInfo"."SYSTEMTYPE") AS "System Type", MAX("resToCost"."TOTALCOST") AS "Total Cost", MAX("memInfo"."TOTALMEMORY") AS "Total Memory", MAX("aaaUser"."FIRST_NAME") AS "User", MAX("aao"."NAME") AS "Vendor Name", MAX("osInfo"."VERSION") AS "Version", MAX("mmInfo"."VIDEOCARDBIOSVERSION") AS "Video Card BiosVersion", MAX("mmInfo"."VIDOECARDCHIPSET") AS "Video Card Chipset", MAX("mmInfo"."VIDEOCARDMEMORY") AS "Video Card Memory", MAX("mmInfo"."VIDOECARDNAME") AS "Video Card Name", MAX("memInfo"."VIRTUALMEMORY") AS "Virtual Memory", MAX("resource"."WARRANTYEXPIRY") AS "Warranty Expiry", dinfo.DRIVENAME AS "DRIVER NAME",dinfo.DRIVETYPE AS "DRIVE TYPE",(dinfo.CAPACITY/1024/1024/1024) AS "Capacity (GB)",(dinfo.FREESPACE/1024/1024/1024) AS "FREESPACE (GB)" FROM "SystemInfo" "workstation" LEFT JOIN "Resources" "resource" ON "workstation"."WORKSTATIONID"="resource"."RESOURCEID" LEFT JOIN "ComponentDefinition" "product" ON "resource"."COMPONENTID"="product"."COMPONENTID" LEFT JOIN "ComponentDefinitionComputer" "compDefComputer" ON "product"."COMPONENTID"="compDefComputer"."COMPONENTID" LEFT JOIN "ComputerGroups" "compGrps" ON "compDefComputer"."COMPUTERGROUPID"="compGrps"."ID" LEFT JOIN "VendorDefinition" "resourceVendor" ON "resource"."VENDORID"="resourceVendor"."VENDORID" LEFT JOIN "SDOrganization" "aao" ON "resourceVendor"."VENDORID"="aao"."ORG_ID" 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 "Resources" "rToAssetResource" ON "rToAsset"."ASSTTORESOURCEID"="rToAssetResource"."RESOURCEID" LEFT JOIN "SDUser" "sdUser" ON "rOwner"."USERID"="sdUser"."USERID" LEFT JOIN "AaaUser" "aaaUser" ON "sdUser"."USERID"="aaaUser"."USER_ID" LEFT JOIN "DepartmentDefinition" "deptDef" ON "rOwner"."DEPTID"="deptDef"."DEPTID" LEFT JOIN "ResourceLocation" "resLocation" ON "resource"."RESOURCEID"="resLocation"."RESOURCEID" LEFT JOIN "RegionDefinition" "regionDef" ON "resLocation"."REGIONID"="regionDef"."REGIONID" LEFT JOIN "SiteDefinition" "siteDef" ON "resLocation"."SITEID"="siteDef"."SITEID" LEFT JOIN "SDOrganization" "aaov" ON "siteDef"."SITEID"="aaov"."ORG_ID" LEFT JOIN "ResourceLease" "resLease" ON "resource"."RESOURCEID"="resLease"."RESOURCEID" LEFT JOIN "Barcodes" "barcodes" ON "resource"."BARCODEID"="barcodes"."BARCODEID" LEFT JOIN "ResourceToCost" "resToCost" ON "resource"."RESOURCEID"="resToCost"."RESOURCEID" INNER JOIN "AccountSiteMapping" "asm" ON "resource"."SITEID"="asm"."SITEID" INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" LEFT JOIN "ChassisTypes" "chassisType" ON "resource"."CHASSISTYPEID"="chassisType"."ID" LEFT JOIN "MemoryInfo" "memInfo" ON "workstation"."WORKSTATIONID"="memInfo"."WORKSTATIONID" LEFT JOIN "OsInfo" "osInfo" ON "workstation"."WORKSTATIONID"="osInfo"."WORKSTATIONID" LEFT JOIN "ProcessorInfo" "procInfo" ON "workstation"."WORKSTATIONID"="procInfo"."WORKSTATIONID" LEFT JOIN "KeyboardInfo" "kbInfo" ON "workstation"."WORKSTATIONID"="kbInfo"."WORKSTATIONID" LEFT JOIN "MonitorInfo" "moniInfo" ON "workstation"."WORKSTATIONID"="moniInfo"."WORKSTATIONID" LEFT JOIN "MouseInfo" "mouseInfo" ON "workstation"."WORKSTATIONID"="mouseInfo"."WORKSTATIONID" LEFT JOIN "SystemInfoDomain" "sysInfod" ON "workstation"."WORKSTATIONID"="sysInfod"."WORKSTATIONID" LEFT JOIN "DomainInfo" "dominf" ON "sysInfod"."DOMAINID"="dominf"."DOMAINID" LEFT JOIN "MultiMediaInfo" "mmInfo" ON "workstation"."WORKSTATIONID"="mmInfo"."WORKSTATIONID" LEFT JOIN "SoundCardInfo" "scInfo" ON "workstation"."WORKSTATIONID"="scInfo"."WORKSTATIONID" LEFT JOIN "Resource_Fields" "resFields" ON "workstation"."WORKSTATIONID"="resFields"."RESOURCEID" LEFT JOIN "Driveinfo" "dinfo" ON "workstation"."WORKSTATIONID"="dinfo"."WORKSTATIONID" GROUP BY "workstation"."WORKSTATIONID",dinfo.DRIVENAME,dinfo.DRIVETYPE,dinfo.CAPACITY,dinfo.FREESPACE