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 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 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 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", ...