Need a report for assets
ServiceDesk Plus v 9.0_9017 - MSSQL database
We need a report for assest for users that displays the following information:
Product Type, Product, Asset Name, Asset Tag, Service Tag , Serial Number .
The report creator was able to create most of what we needed but we are not able to get the Service Tag Column as an option. This is the SQL that the report generated:
SELECT MAX(aaaUser.FIRST_NAME) "User",MAX(productType.COMPONENTTYPENAME) "Product Type",MAX(product.COMPONENTNAME) "Product",MAX(resource.RESOURCENAME) "Asset Name",MAX(resource.ASSETTAG) "Asset Tag",MAX(resource.SERIALNO) "Serial Number",MAX(aaaUser.FIRST_NAME) "User",MAX(deptDef.DEPTNAME) "Department" FROM Resources resource LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID 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 DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID WHERE ((deptDef.DEPTNAME = N'Retail' COLLATE SQL_Latin1_General_CP1_CI_AS) AND (((((((((((aaaUser.FIRST_NAME LIKE N'R0%' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (aaaUser.FIRST_NAME LIKE N'R1%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R2%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R3%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R4%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R5%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R6%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R7%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R8%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R9%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R10%' COLLATE SQL_Latin1_General_CP1_CI_AS))) GROUP BY resource.RESOURCEID ORDER BY 1, MAX(resource.RESOURCENAME), MAX(resource.ASSETTAG), MAX(resource.SERIALNO)
How would we add the service Tag column between asset and Serial number?
Thanks,
Robert Owen
We need a report for assest for users that displays the following information:
Product Type, Product, Asset Name, Asset Tag, Service Tag , Serial Number .
The report creator was able to create most of what we needed but we are not able to get the Service Tag Column as an option. This is the SQL that the report generated:
SELECT MAX(aaaUser.FIRST_NAME) "User",MAX(productType.COMPONENTTYPENAME) "Product Type",MAX(product.COMPONENTNAME) "Product",MAX(resource.RESOURCENAME) "Asset Name",MAX(resource.ASSETTAG) "Asset Tag",MAX(resource.SERIALNO) "Serial Number",MAX(aaaUser.FIRST_NAME) "User",MAX(deptDef.DEPTNAME) "Department" FROM Resources resource LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID 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 DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID WHERE ((deptDef.DEPTNAME = N'Retail' COLLATE SQL_Latin1_General_CP1_CI_AS) AND (((((((((((aaaUser.FIRST_NAME LIKE N'R0%' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (aaaUser.FIRST_NAME LIKE N'R1%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R2%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R3%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R4%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R5%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R6%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R7%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R8%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R9%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (aaaUser.FIRST_NAME LIKE N'R10%' COLLATE SQL_Latin1_General_CP1_CI_AS))) GROUP BY resource.RESOURCEID ORDER BY 1, MAX(resource.RESOURCENAME), MAX(resource.ASSETTAG), MAX(resource.SERIALNO)
How would we add the service Tag column between asset and Serial number?
Thanks,
Robert Owen