How To Import HelpDesk Categories from a Spreadsheet

How To Import HelpDesk Categories from a Spreadsheet WARNING: Always review "Precautions when creating ticket rules" before writing any rules

Time to complete: 3-5 hours

 

Overview

The Helpdesk does not support the import of helpdesk categories out of the box. It does support the creation of new categories via the webUI but you must then add them one-by-one. In order to import a list of Categories you must have access to the database, so, until now the import of helpdesk categories must be performed by Kace Professional Services. The technique outlined in this article will help you import Helpdesk categories on your own.

This technique relies upon the purchase of the asset module. The concept here is that you are allowed to import data into assets. The assts become a temporary storage location for your helpdesk categories. By importing your helpdesk categories as assets a ticket rule can then copy these categories into the helpdesk.

 

Caveats and Notes

• You will need to have the helpdesk module and the asset module for this technique to work
• If you have existing categories in your helpdesk this will not import categories with the same name. So either rename them or delete them. Unique entries will be imported.
• The way that this rule runs it does not matter what queue you create the rule in. It will import all categories for all queues that you have specified correctly in your spreadsheet.

 

Step 1: Backup Your Existing Helpdesk Categories

In case anything goes wrong with the import we want to make sure that we have a backup. Separately from any backup you will do because of taking the correct precautions it is a good idea to backup the helpdesk categories because if something goes wrong we can restore just the categories without restoring everything.

To do this simply run the following SQL as a report

select * from HD_CATEGORY ORDER BY HD_QUEUE_ID, NAME

Detailed Steps:

• Click on the Reporting tab.
• From the "choose action..." menu select "Add New SQL Report". e.g.
  
• Fill out the SQL report as follows:

• • Title: Backup of HD Categories
  • Report Category: Helpdesk
  • Output File Name:
  • Description: Backup of HD Categories
  • Output Types: CSV-checked, XLS-checked
  • SQL Select Statement:
  • Break on Columns:
  • Auto-generate Layout: checked
• Click "Save"
• Find the report "Backup of HD Categories" and click on the CSV link or XLS file to download it. This is your backup.

 

Step 2: Prepare the Asset

Now we have to create an asset to represent the Helpdesk Categories. It will look almost identical to the category listing in the queue configuration.

Details Steps:

• Go to the "Asset" tab
• Click on the "Asset Types" subtab
• From the "choose action..." menu select "Add New Item". e.g.
  
• Create a new type called "Helpdesk Categories" adding new fields as in this screenshot
  
  • Notes:
    • the "select values" list for "user settable" is

      , true

    • The type of the "Default Owner" field is NOT of type user. This is because the asset module does not support import of user ids. Simply choose "Number" for now.
• Click "Save"

 

Step 3: Prepare your own spreadsheet

You will need to prepare your own csv file for the categories you want to import. Regardless of whether the categories you have are in another helpdesk application or just ideas in your head you will need to get them into a csv file format so that you can import it into the asset module. Attached is a sample csv file. The file will look like the following.

Download the spreadsheet here: https://support.kace.com/packages/kb_attachments/94/Sample%20Heldpdesk%20Entries.csv

 

Notes

• "Category Name" is the same way that it appears in the helpdesk configuration. Child categories are denoted by listing the parent(s) separated by double colons. E.g. Parent::Child::GrandChild
• The "Default Owner" column is 0 for unassigned. Otherwise, it is the id number of the user that you want to make a default owner. These id values come from the user table in your K1000 appliance helpdesk. If you are not sure then use 0. You can edit these entries using valid usernames after you finish the entire import process.
• The "CC List" field is a command separated list of email addresses. These do not have to match any users in your helpdesk system.
• The valid values for the "User Settable" column are "" or "True". In other words, if it is false, then leave it blank.
• "Queue Name" will depend upon your setup. The default helpdesk queue is called "The K1000 Help Desk" but you may need to enter another name -- you must enter it exactly or you will have errors.
• Make sure that your spreadsheet is sorted in the way that you want the data to appear inside the helpdesk. It is easier to change the sort order now rather than later.

 

Step 4: Import your spreadsheet into the Asset Module

Detail Steps:

• Click on the "Asset" tab
• Click on the "Asset Import" sub-tab
• Browse to the csv file you created from step 3 above. If relevant, or if you used the provided spreadsheet then, check the box denoting that headers are in the csv file.
  
• Click "Next"
• Choose Asset Type=Helpdesk Categories.
• Click "Next".
• Choose the mapping so that the Asset fields match the CSV fields. The PK column is the "Category Name". The asset Name is also the "Category Name". See screenshot
  
• Click "Preview" to verify that everything matches your spreadsheet and to see how many records will be inserted.
• If you are satisfied then click "import data".
• Click "Done".
• Go to the Asset\Assets tab.
• From the "View by..." menu choose "heldpesk categories" and you will see your data there like this screenshot
  

 

Step 5: Create the Ticket Rule to move the data into the Helpdesk

This is the tricky part. Every K1000 appliance is going to have a different query for this next part. This is because you created the asset "Helpdesk Categories" and the underlying database tables that represent this table are unique to your K1000 appliance. In order to complete these step you will need to do some sleuthing and modify the query using these instructions.

 

Step 5.1: Find out the table name and column anems for your "Helpdesk Categories" asset

Similar to step 1 run this exact query to determine the hidden table and column names for your asset

select ASSET_TYPE.ID AS DDDDDD, 
CONCAT('ASSET_DATA_',ASSET_TYPE.ID) CCCCCC,
FIELD_NAME, 
CONCAT('FIELD_',AFD.ID) COLUMN_NAME,
CASE WHEN FIELD_NAME = 'Category Name' THEN 'XXXXXX'
WHEN FIELD_NAME = 'Default Owner' THEN 'YYYYYY'
WHEN FIELD_NAME = 'CC List' THEN 'ZZZZZZ'
WHEN FIELD_NAME = 'User Settable' THEN 'AAAAAA'
WHEN FIELD_NAME = 'Queue Name' THEN 'BBBBBB'
ELSE 'Unknown -- your field name doesn\'t match the FAQ' END SUB_IN_QUERY

from ASSET_TYPE JOIN ASSET_FIELD_DEFINITION AFD ON AFD.ASSET_TYPE_ID=ASSET_TYPE.ID
WHERE NAME= 'Helpdesk Categories'

Note that in our example we get the following results (yours will differ):

So, our case the column name for the category name is "FIELD_76"

 

Step 5.2: Rewrite the Query For Importing Categories

Detailed Steps

• Open any text editor (e.g. notepad.exe)
• Replace the value XXXXXX with the column name for the field "Category Name" (in our case it would be "FIELD_76")
• Replace the value YYYYYY with the column name for the field "Default Owner" (in our case it would be "FIELD_77")
• Replace the value ZZZZZZ with the column name for the field "CC List" (in our case it would be "FIELD_78")
• Replace the value AAAAAA with the column name for the field "User Settable" (in our case it would be "FIELD_79")
• Replace the value BBBBBB with the column name for the field "Queue Name" (in our case it would be "FIELD_80")
• Replace the value CCCCCC with the table name for the data table (in our case it would be "ASSET_DATA_20")
• Replace the value DDDDDD with the asset id (in our case it would be 20 - as in ASSET_DATA_20)

The final query for me would be as in this screenshot.

Here is the SQL that you will start with.

INSERT INTO HD_CATEGORY(HD_QUEUE_ID, NAME,ORDINAL,DEFAULT_OWNER_ID, CC_LIST,USER_SETTABLE)
SELECT
   HD_QUEUE.ID HD_QUEUE_ID_, /* queue _id */
   XXXXXX NAME, /*name*/
   ((select MAX(ID) from HD_CATEGORY WHERE HD_QUEUE_ID=HD_QUEUE.ID)+
   (SELECT count(ID) CID FROM CCCCCC AS x WHERE x.ID<= HelpD.ID  )) ORDINAL,
   IF(YYYYYY='Unassigned',0,YYYYYY) DEFAULT_OWNER_ID, /*default owner */
   IF(ZZZZZZ='none','',ZZZZZZ) CC_LIST, /*cc list */
   IF(AAAAAA='true',1,0) USER_SETTABLE /* user settable */

FROM
   ASSET
   JOIN CCCCCC HelpD ON ASSET.ASSET_DATA_ID=HelpD.ID and ASSET_TYPE_ID=DDDDDD
   JOIN HD_QUEUE ON BBBBBB=HD_QUEUE.NAME
   LEFT JOIN HD_CATEGORY ON HD_QUEUE_ID=HD_QUEUE.ID AND HD_CATEGORY.NAME=XXXXXX
WHERE
   HD_CATEGORY.NAME IS NULL

Step 5.3: Create the Ticket rule

Detailed Steps

• Go to Helpdesk tab
• Click on Confguration
• Choose the queue that you want to modify
• Scroll to the bottom and click on "Ticket Rules: [Customize]"
• From the "Choose Action..." menu select "Add Ticket Rule"
  
• In the "define ticket rule" click "next" and "Done". You will now be on an "edit detail" page that looks like this screenshot
• Replace all the fields as follows:
  • Title: Import Helpdesk Categories
  • Order: 900
  • Queue:
  • Notes: Created by FAQ from KACE
  • Frequency: Monthly -- any value that is not "On Ticket Save"
  • Next Run: any value -- it doesn't matter
  • Enabled: Unchecked
  • Select Query: Empty the field and replace it with the following:

    select 1

  • Send query results to somone = unchecked
  • Results are tickets, add a comment to each one = unchecked
  • Send an email for each result row = unchecked
  • Run an update query, using the results from the one above = checked
  • Update Query: Empty the field and replace it with your SQL from step 5.2 above
  • You should now have something that looks like this screenshot
    
• Click "Save"
• Reopen the Rule
• Click on "Run Now"
• Reopen the Rule and view the "Run Log" for the results. You'll see something like this screenshot
  

Step 6: Cleanup

This step is not required but it is a good idea to go back and delete the imported assets as well as resaved the queue configuration. The import will choose higher values than necessary for a safe import but this should be corrected for forward compatibility with future versions of the K1000 appliance.

 

Steps

• Go to Helpdesk tab
• Click on Confguration
• Choose the queue that you want to modify
• Click on Ticket Defaults "Customize these values"
• Scroll to the bottom of this screen and click "save"

 

Notes and Resources

• Instead of running the queries as reports you could also run them through a tool like the MySQL Query Browser: See this FAQ
• This FAQ only imports new categories -- If you have existing categories that you want to overwrite you will have to delete them first. In order to delete a category you need to remove all associations from tickets to that category by deleting or modifying any associated tickets
• This import process will not allow duplicate categories-- if you already have a category in the helpdesk of the same name then the old category will remain and the new one will not be inserted.