Query to show asset additional attributes. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Go to Reports-New Query Report and execute this query.
SELECT ci.CINAME AS "CI Name", citype.TYPENAME AS "CI Type", ci.DESCRIPTION AS "Description",aao.NAME AS "Vendor Name",state.DISPLAYSTATE AS "Asset State",LONGTODATE(res.EXPIRYDATE) AS "Expiry Date",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes Value" FROM BaseElement baseci LEFT JOIN CI ci ON baseci.CIID=ci.CIID LEFT JOIN CIType citype ON ci.CITYPEID=citype.TYPEID LEFT JOIN resources res on res.ciid=ci.ciid LEFT JOIN VendorDefinition resourceVendor ON res.VENDORID=resourceVendor.VENDORID LEFT JOIN SDOrganization aao ON resourceVendor.VENDORID=aao.ORG_ID LEFT JOIN ResourceState state ON res.RESOURCESTATEID=state.RESOURCESTATEID LEFT JOIN instanceattributes ia on ia.ciid=ci.ciid
New to ADSelfService Plus?
Related Articles
Query to show support groups and its individual custom attributes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT ci.CINAME AS "Support Group Name", su.first_name "Owned By", ci.DESCRIPTION AS "Description",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes ...Query to display additional field associations with templates (MSSQL)
Tested in build MSSQL (14306) Use case The reports shows in which templates the created additional fields are associated Query select sd.name "Service Catalog Name", rt.templatename "Template name", fc.field_name "Fields" from requesttemplate_list rt ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show Asset state history ( MSSQL)
Tested in MSSQL build (14306) SELECT res.resourcename 'Asset Name', res.assettag 'Asset Tag',res.serialno 'Asset Serial No.', rs2.statedesc 'Previous State',rs1.statedesc 'Current State',DATEADD(s,DATEDIFF(s,GETUTCDATE() ,getdate()) + ...Query to get Asset contract and its details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) SELECT mcdt.Contractid "URL Contract ID", mcdt.customcontractid "Custom contractid in details page", mcdt.CONTRACTNAME "Contract Name", aao.NAME "Maintenance Vendor Name", LONGTODATE(mcdt.FROMDATE) ...