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 ComponentDefinition.COMPONENTTYPEID=ComponentType.COMPONENTTYPEID LEFT JOIN ResourceType ON ComponentType.RESOURCETYPEID=ResourceType.RESOURCETYPEID LEFT JOIN ResourceCategory ON ComponentType.RESOURCECATEGORYID=ResourceCategory.RESOURCECATEGORYID LEFT JOIN ResourceState ON Resources.RESOURCESTATEID=ResourceState.RESOURCESTATEID WHERE (((ResourceCategory.CATEGORY like 'IT') AND (ResourceType.TYPE like 'Asset')) AND (ResourceState.RESOURCESTATEID NOT IN (4,5)))
New to ADSelfService Plus?
Related Articles
Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...
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" ...
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 project tasks status wise count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT projectdet.title AS "Project name", count(case when (taskstatus.statusid='1') THEN 1 ELSE NULL END) "Open Tasks", count(case when (taskstatus.statusid='6') THEN 6 ELSE NULL END) "In ...
Query to show count of tickets in each module per technician (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. SELECT 'Request' "Module",aaau.first_name "Technician",count(wo.WORKORDERID) "Request count" FROM WorkOrder wo left join workorderstates wos ON ...