Query to show Asset details with Warranty Expiry Date (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", productType.COMPONENTTYPENAME AS "Product Type", state.DISPLAYSTATE AS "Asset State", rtype.TYPE AS "Asset Type", aao.NAME AS "Vendor Name", LONGTODATE(resource.EXPIRYDATE) AS "Expiry Date",LONGTODATE(resource.warrantyexpiry) "Warranty Expiry Date", resource.SERIALNO AS "Org Serial Number" FROM Resources resource 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 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 ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID ORDER BY 1
New to ADSelfService Plus?
Related Articles
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) ...Query to show all asset details ( MSSQL )
Tested in build MSSQL (14306) SELECT MAX("resource"."RESOURCENAME") AS "Asset Name", MAX("resource"."ASSETTAG") AS "Asset Tag",MAX(resource.SERIALNO) AS "Org Serial Number", MAX("product"."COMPONENTNAME") AS "Product", ...Query to show workstation details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Below query will show Only workstations and its details. SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", ...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()) + ...