/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


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

06/16/2020 94 views

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.


Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Do you want just a list of tickets modified or should the report include what the technician changed and their comment?
    • At this point I just need a list of tickets, preferably sorted by modifying tech.
      TicketID, Technician, Created, Date Closed.
      Once I know the field reference for the modifying tech I should be able to sort the rest.

Answer Chosen by the Author

1

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.

Answered 06/19/2020 by: JordanNolan
8th Degree Black Belt

  • 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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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