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 Non IT assets or the assets that does not consume license (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT MAX(resource.RESOURCENAME) AS "Asset Name", MAX(resource.ASSETTAG) AS "Asset Tag", MAX(product.COMPONENTNAME) AS "Product", MAX(productType.COMPONENTTYPENAME) AS "Product Type", ...Query to show workstations that has a scan status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: Below query that will show the Assets that has a scan status. select systeminfo.workstationname "Workstation Name", LONGTODATE(audithistory.audittime) "Last Scanned on", ...