KACE Product Support : Hardware category report for the past 30 days

Hardware category report for the past 30 days

Hello,

We are trying to create a report for all tickets opened under the Hardware category over the past 30 days in a specific queue.

We have run the following SQL query, but this is generating all tickets from all categories in the specific queue:

SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', S.NAME AS 'Status' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
WHERE DATEDIFF(NOW(), CREATED) < 30 AND T.HD_QUEUE_ID = 1

If anyone could provide any additional info on getting the Hardware category isolated in the report that would be great.

Thanks in advance!

Pete

0 Comments [ + ] Show comments

Answers (2)

Answer Summary:

Posted by: nshah 8 years ago

Give this a try.

Select

T.ID As 'Ticket #',

T.TITLE As 'Issue',

S.NAME As 'Status',

HD_CATEGORY.NAME As Category

From

HD_TICKET T Join

HD_STATUS S

On T.HD_STATUS_ID = S.ID,

HD_CATEGORY

Where

DateDiff(Now(), T.CREATED) < 30 And

T.HD_QUEUE_ID = 1 And

HD_CATEGORY.NAME Like 'HARDWARE'

Comments:

Hi nshah,

That query generated the following MySQL error:

[1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'T.ID As 'Ticket #', T.TITLE As "Issue', S.NAME As 'Status', HD_CATEGORY' at line 1] in EXECUTE( "T.ID AS 'Ticket #', T.TITLE As 'Issue', S.NAME As 'Status', HD_CATEGORY.NAME As Category From HD_TICKET T Join HD_STATUS S On T.HD_STATUS_ID = S.ID, HD_CATEGORY Where DateDiff(Now(), T.CREATED) < 30 And T.HD_QUEUE_ID =1 And HD_CATEGORY.NAME Like 'HARDWARE' LIMIT 0") - Peterphan 8 years ago
- I'm not uber great with SQL but this gives everything under hardware, you may just have to add the queue and the 30 days to the WHERE statement
  
  Select
  HD_TICKET.TITLE As Issue,
  HD_CATEGORY.NAME As Category,
  HD_STATUS.NAME As Status,
  HD_TICKET.ID As Ticket
  From
  HD_CATEGORY Inner Join
  HD_TICKET T
  On HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID Inner Join
  HD_STATUS
  On HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
  Where
  HD_CATEGORY.NAME = 'Hardware' - nshah 8 years ago
  - Hello again,
    
    We added the queue and 30 days to the WHERE statement and still get a MySQL error. Our SQL guru is out today so I'm just trying to figure this out myself until he's back.
    
    I found in another thread, the following queury, which gets us created, category, title, submitter name and owner name.
    
    SELECT
    HD_TICKET.CREATED,HD_CATEGORY.NAME AS CATEGORY,
    HD_TICKET.TITLE,S.FULL_NAME AS SUBMITTER_NAME,
    O.FULL_NAME AS OWNER_NAME
    FROM HD_TICKET
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
    LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY) )
    ORDER BY CREATED desc,CATEGORY
    
    For now, we can just use filter and sorting in Excel to locate all the Hardware category tickets.
    
    I'll check with him tomorrow once he's back to see if he knows why we're getting those SQL errors.
    
    Thanks!
    
    Peter - Peterphan 8 years ago

Posted by: nshah 8 years ago

Posted by:

View more:

KACE Product Support Questions

question