Need help with Monthly Closed Report on a Specific K1000 Label
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
Answers (2)
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
Comments:
-
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 - jverbosk 11 years ago
