Morning all,

 

I have been using the below query to report out a list of all IT/Non-IT assets including the cost.  However I need to edit it to show the different sub-categories of assets.

 

Eg. Show whether a workstation is a Laptop or Desktop.  Show what is a server, what is a router etc.  Then when I export the report to excel, I can filter via category for ease of use.

 

Database is: MySQL

 

Any help appreciated!

 

Query below (Which i grabbed off one of the many reporting threads anyway :) )

 

- Gemma

 

SELECT resource.RESOURCENAME 'Resource Name',product.COMPONENTNAME 'Product',aao.NAME 'Vendor Name',state.DISPLAYSTATE 'Resource State',rt.type 'Resource Type',rc.category 'Resource Category',date_format(from_unixtime(resource.ACQUISITIONDATE/1000),'%d-%m-%Y %k:%i') 'Acquisition Date',date_format(from_unixtime(resource.EXPIRYDATE/1000),'%d-%m-%Y %k:%i') 'Expiry Date',resource.SERIALNO 'Serial Number',aaaUser.FIRST_NAME 'User',resource.BARCODE 'Bar Code',cost.cost 'COST',sys.Servicetag 'Service Tag' FROM Resources resource LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID 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 SDUser sdUser ON rOwner.USERID=sdUser.USERID LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID left join resourcecost cost on cost.resourceid=resource.resourceid left join systeminfo sys on sys.workstationid=resource.resourceid left join componenttype ct on ct.componenttypeid=product.COMPONENTTYPEID left join resourcetype rt on rt.resourcetypeid=ct.resourcetypeid left join resourcecategory rc on rc.resourcecategoryid=ct.resourcecategoryid where rt.type != 'Software'