Query to get list of assets consuming license per account. (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306)
Query:
SELECT ad.ORG_NAME AS "Account",
Resources.Resourcename "Asset name",
productType.COMPONENTTYPENAME AS "Product Type",
product.COMPONENTNAME AS "Product", state.displaystate AS "Asset State" FROM Resources
LEFT JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN ResourceType ON productType.RESOURCETYPEID=ResourceType.RESOURCETYPEID
LEFT JOIN ResourceCategory rCategory ON productType.RESOURCECATEGORYID=rCategory.RESOURCECATEGORYID
LEFT JOIN ResourceState state ON resources.RESOURCESTATEID=state.RESOURCESTATEID
INNER JOIN AccountSiteMapping asm ON resources.SITEID=asm.SITEID
INNER JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
WHERE (((rCategory.CATEGORY like 'IT') AND (ResourceType.TYPE like 'Asset')) AND (state.RESOURCESTATEID NOT IN (4,5))) ORDER BY 1
DB: MSSQL & PGSQL
New to ADSelfService Plus?
Related Articles
Query to show count of IT assets or count of assets that consumed license (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT count(Resources.RESOURCEID) "Count Of Used Assets" FROM Resources LEFT JOIN ComponentDefinition ON Resources.COMPONENTID=ComponentDefinition.COMPONENTID LEFT JOIN ComponentType ON ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...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 Count of survey sent and received per account (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT adef.org_name "Account",COUNT(srm.surveyid) "Total surveys sent", COUNT (sra.responseid) "Total surveys received" FROM SurveyResponseRequestMapping srrm INNER JOIN Survey_Response_Main srm ON ...Query to show number of tickets created per template with their status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT reqtl.TEMPLATENAME "Request Template", count(wo.WORKORDERID) "Request Count", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Completed", count(case when std.ispending='1' THEN 1 ELSE ...