Query to get list of assets consuming license per account. (MSSQL & PGSQL)

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?