2 questions about pulling a report in KACE.

1st question:

I am trying pull a SQL report on a partner company of ours for Monthly billing but having trouble with the code.  I did some searching and found the line to include in the report but when placed in the report below it has no success.

 JOIN LABEL L ON L.ID=ML.LABEL_ID and L.NAME='Subsidiary Company Company Name'

Added to:

 

SELECT HD_TICKET.TITLE,HD_CATEGORY.NAME AS CATEGORY,
HD_STATUS.NAME AS STATUS,
HD_TICKET.ID,O.FULL_NAME AS OWNER_NAME,
S.FULL_NAME AS SUBMITTER_NAME,
HD_TICKET.TIME_OPENED,
HD_TICKET.TIME_CLOSED FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),
INTERVAL 1 MONTH))) AND (HD_CATEGORY.NAME like '%OSI Acct Unlock%')) ORDER BY ID desc,SUBMITTER_NAME

Please let me know if you have a better SQL code to pull reports on specific Labels for Users.

2nd question:

How do we include other queues in the report mentioned above.  For example:  I would like to include the Help Desk, System Admins, Operations, and Business Analyst queues on one report.  I know that you have to specify the queue id (HD_TICKET.HD_QUEUE_ID = 2) but how do you have muliple queues in a single report?

Big Thanks!!!

TX

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Try the query below, it should do what you want.

The join to the LABEL table requires using a join table (USER_LABEL_JT), which I joined to the ticket submitter.  If that's not what you want and want it focused on the owner instead, change JOIN USER_LABEL_JT UL ON (UL.USER_ID = S.ID) to use O.ID instead of S.ID.

To include multiple matches, use REGEX (rlike) instead of '=' or 'like', so for example if you wanted to include queues 1 and 2, you would change the first WHERE statement to WHERE T.HD_QUEUE_ID rlike '1|2'.  For more info on SQL queries, this blog might be useful:

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

Hope that helps!

John

____________________________

SELECT T.TITLE, C.NAME AS CATEGORY, ST.NAME AS STATUS,
T.ID, O.FULL_NAME AS OWNER_NAME, S.FULL_NAME AS SUBMITTER_NAME,
T.TIME_OPENED, T.TIME_CLOSED
FROM HD_TICKET T
JOIN HD_CATEGORY C ON (C.ID = T.HD_CATEGORY_ID)
JOIN HD_STATUS ST ON (ST.ID = T.HD_STATUS_ID)
LEFT JOIN USER O ON (O.ID = T.OWNER_ID)
LEFT JOIN USER S ON (S.ID = T.SUBMITTER_ID)
JOIN USER_LABEL_JT UL ON (UL.USER_ID = S.ID)
JOIN LABEL L ON (L.ID = UL.LABEL_ID)
WHERE T.HD_QUEUE_ID rlike '1'
AND TIMESTAMP(T.TIME_CLOSED) <= NOW()
AND TIMESTAMP(T.TIME_CLOSED) > DATE_SUB(NOW(), INTERVAL 1 MONTH)
AND C.NAME rlike 'OSI Acct Unlock'
AND L.NAME rlike 'Subsidiary Company Company Name'
ORDER BY ID desc, SUBMITTER_NAME

Answered 02/13/2013 by: jverbosk
Red Belt

  • One final comment - the JOIN statement for the LABEL table you included uses the MACHINE join table. I assumed you wanted to use the USER join table to target user labels, but if you want to target MACHINE labels instead, just change these two lines from this:

    JOIN USER_LABEL_JT UL ON (UL.USER_ID = S.ID)
    JOIN LABEL L ON (L.ID = UL.LABEL_ID)

    To this:

    JOIN MACHINE M ON (M.ID = T.MACHINE_ID)
    JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
    JOIN LABEL L ON (L.ID = ML.LABEL_ID)

    John
Please log in to comment
0

Thank you very much John!!!

Answered 02/22/2013 by: TXgroup
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity