I want to create a report that shows all tickets modified (not owned) by a specfic individual for a specific time in the K1000 Reports
First, I am not an SQL guru at all. I can muddle through. When you view ticket history you get comment, modified by and date. These are obviously separate fields joined together in the History Tab report.
I would like to be able to search for tickets modified by a specific tech, but that data does not seem to be stored in the Comment field.
Any suggestions are welcome and appreciated, whether it be the table/field where that data is stored or a query that will get me this data. I would prefer to have this available in KACE Reports.
Answers (1)
Top Answer
Hi,
Here is a quick query you can build on to get the details you want. It joins the HD_Ticket and HD_Ticket_Change tables (and user table a few times) so you can see if user JDOE is the owner, submitter, or commenter on a ticket:
Select
HD_TICKET.ID As Ticket,
HD_TICKET.TITLE As Title,
USER.USER_NAME As Owner,
USER1.USER_NAME As `Commented By`,
USER2.USER_NAME As Submitter,
HD_TICKET_CHANGE.COMMENT As Comment
From
HD_TICKET Inner Join
HD_TICKET_CHANGE On HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID Inner Join
USER On HD_TICKET.OWNER_ID = USER.ID Inner Join
USER USER1 On HD_TICKET_CHANGE.USER_ID = USER1.ID Inner Join
USER USER2 On HD_TICKET.SUBMITTER_ID = USER2.ID
Where
(USER.USER_NAME = 'jdoe') Or
(USER1.USER_NAME = 'jdoe') Or
(USER2.USER_NAME = 'jdoe')
There is an query designer called FlySpeed Query that is great for designing the queries in a GUI where they give you the correct SQL syntax for the DB you are using. I only use MYSql for my KACE stuff so it comes in very handy to make sure I have the right syntax. Paste this query in there and build out the fields you want to include.
Comments:
-
Thanks Jordan!
The HD_Ticket_Change is what I was missing, you don't get access to that table using the Wizard. This helps tremendously.
I probably need to reopen the database access in the future to be able to reference these other tables. - wmwooten 3 years ago
TicketID, Technician, Created, Date Closed.
Once I know the field reference for the modifying tech I should be able to sort the rest. - wmwooten 3 years ago