How to: generate list of all used categories and subcategories
Environment TOPdesk version SaaS / on-premises Answer There are several options to get data about categories. The first is the most simple and gives all categories currently available in the environment per module.
Environment TOPdesk version SaaS / on-premises Answer There are several options to get data about categories. The first is the most simple and gives all categories currently available in the environment per module. The second requires a single API call. The third is more advanced and allows to get more information about the categories, for example how often a category is used. ⒈Incident card/Context menu:Subcategory/find option ⒉Retrieve a list using the API ⒊Use a SQL Query on the database. 1. Incident card/Context menu ※ Please note that an active category filter will influence the result of the shown list. This overview shows all categories that are available in the Incident management module. You can repeat these steps for the categories that are available in the Change management module, Problem management module, et cetera. 2. Use the API - /tas/api/categories You can use an API tool and run a GET request using the URL your_topdesk_url/tas/api/categories In order to retrieve this, you will need an Operator account with API permissions and an application password, see KI 9632 - API & WebDAV: how to use application passwords (This endpoint can also be used in an action sequence as a step to retrieve categories). See our Developers site for more information about the API. 3. Use a query It is also possible to export this data directly from the TOPdesk database by using an SQL query. TOPdesk SaaS customers can download the database via WebDAV or via Settings => Upload/download files => Database backups. For a list of all categories and subcategories, the following query should be used: SELECT p."naam" AS parent, c."naam" AS child FROM "classificatie" p JOIN "classificatie" c ON p."unid" = c."parentid" ORDER BY parent, child If you also want to know how often a category has been used, the following query can be used: SELECT * FROM (SELECT c.naam AS category, s.naam AS subcategory, COUNT(1) AS number_of_uses FROM incident i LEFT JOIN classificatie c ON i.incident_domeinid = c.unid LEFT JOIN classificatie s ON i.incident_specid = s.unid GROUP BY s.naam, c.naam UNION SELECT c.naam AS category, s.naam AS subcategory, '0' AS number_of_uses FROM classificatie s LEFT JOIN classificatie c ON s.parentid = c.unid WHERE s.parentid IS NOT NULL AND s.unid NOT IN (SELECT incident_specid FROM incident WHERE incident_specid IS NOT NULL)) AS RESULT ORDER BY category ASC, number_of_uses ASC