/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


K1000 Ticket Report: Tickets that were raised for a months period

03/20/2018 977 views
(1) tickets raised in a particular Queue for a specific Category
(2) both active and archived


2 Comments   [ + ] Show comments

Comments

  • Try this

    SELECT HD_TICKET.ID,
    HD_CATEGORY.NAME AS CATEGORY,
    Q.NAME AS QUEUE_NAME
    FROM HD_TICKET
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
    WHERE (HD_TICKET.HD_QUEUE_ID = 1)
    AND (HD_CATEGORY.NAME LIKE 'Software%')

    UNION ALL

    SELECT HD_ARCHIVE_TICKET.ID,
    HD_CATEGORY.NAME AS CATEGORY,
    Q.NAME AS QUEUE_NAME
    FROM HD_ARCHIVE_TICKET
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_ARCHIVE_TICKET.HD_CATEGORY_ID)
    JOIN HD_QUEUE Q ON Q.ID = HD_ARCHIVE_TICKET.HD_QUEUE_ID
    WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID = 1)
    AND (HD_CATEGORY.NAME LIKE 'Software%')
  • i tried the entire script and both separately....and no results
    • Did you adjust the queue ID value and category for your environment? I just ran the query on my appliance using queue 2 and it worked (that queue has a Software category, so I left it).
      • 'General Queries' is the label for my queue and i do have a category labelled 'Software'.

        i used this:

        SELECT HD_TICKET.ID,
        HD_CATEGORY.NAME AS CATEGORY,
        Q.NAME AS QUEUE_NAME
        FROM HD_TICKET
        JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
        JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
        WHERE (HD_TICKET.HD_QUEUE_ID LIKE 'General%')
        AND (HD_CATEGORY.NAME LIKE 'Software%')

        UNION ALL

        SELECT HD_ARCHIVE_TICKET.ID,
        HD_CATEGORY.NAME AS CATEGORY,
        Q.NAME AS QUEUE_NAME
        FROM HD_ARCHIVE_TICKET
        JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_ARCHIVE_TICKET.HD_CATEGORY_ID)
        JOIN HD_QUEUE Q ON Q.ID = HD_ARCHIVE_TICKET.HD_QUEUE_ID
        WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID LIKE 'General%')
        AND (HD_CATEGORY.NAME LIKE 'Software%')
      • If you want to use the queue name, then you need to change HD_TICKET.HD_QUEUE_ID LIKE 'General%' and HD_ARCHIVE_TICKET.HD_QUEUE_ID LIKE 'General%' to Q.NAME like 'General%', the query is expecting an ID, which is a number.

        You also need to change the HD_CATEGORY.NAME LIKE 'Software%' to match the category that you want your report to reflect. Make sure you change it in both places.
      • this worked thank you, ill try to amend the other columns i need on the report

Be the first to answer this question

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ