Version : 10609

DB : Postgres

OUTPUT:

SELECT MAX(workstation.WORKSTATIONNAME) AS "Machine Name",

resource.resourcename "Asset Name",

MAX(rtype.TYPE) AS "Device Type",

citype.TYPENAME AS "Product Type",

MAX(net.ipaddress) "IP Address",

MAX(workstation.MANUFACTURER) AS "Manufacturer",

Max(aaauser.first_name) "User",

Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",

Longtodate(Max(audithistory.audittime)) "Last Scan Date", Max(audithistory.auditstatus)  "Audit Status",

MAX(workstation.MODEL) AS "Product",

MAX(resource.SERIALNO) AS "Org Serial Number",

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 "Operating System",

MAX(aaov.NAME) AS "Site",

Longtodate(MAX(po.dateordered)) AS "PO Date",

po.purchaseorderid AS "PO Number",

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(resFields.UDF_CHAR15) AS "Project",

MAX(resFields.UDF_CHAR21) AS "Application Owner",

MAX(resFields.UDF_CHAR17) AS "ILO IP Address",

MAX(resFields.UDF_CHAR19) AS "Server Category",

MAX(resFields.UDF_CHAR13) AS "Asset Used By",

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 ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN ResourceType rtype ON productType.RESOURCETYPEID=rtype.RESOURCETYPEID


LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID

LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID

LEFT JOIN lastauditinfo ON resource.resourceid = lastauditinfo.workstationid

LEFT JOIN audithistory LASTSUCCESSAUDIT ON lastauditinfo.last_success_auditid=LASTSUCCESSAUDIT.auditid

LEFT JOIN networkinfo net ON resource.resourceid = net.workstationid

LEFT JOIN audithistory ON lastauditinfo.last_auditid = audithistory.auditid

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 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 PurchaseLot plot ON plot.PURCHASELOTID=resource.PURCHASELOTID
LEFT JOIN Purchaselotext pxt ON pxt.PURCHASELOTID=plot.PURCHASELOTID
LEFT JOIN Purchaseorderitem poi ON poi.PURCHASEORDERITEMID=pxt.PURCHASEORDERITEMID
LEFT JOIN PurchaseOrder po ON po.purchaseorderid=poi.purchaseorderid

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, resource.resourcename, net.ipaddress,po.purchaseorderid ORDER BY 2