I am trying to run a report, or do a search, so I can find all Kace tickets opened by specific users.  We have a helpdesk, with multiple staff. and we have to assign tickets to other technicians at times.  I would like to run a report, or be able to view what tickets a specific person has opened, tickets that are assigned to them, or other people.  Is this possible?  I'm not having any luck with what I've been trying.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2

The way I would do your search is to combine these items into an advanced search:

  • username
  • timestamp (or date)
  • status
for multiple statuses use this one line:
status  matches regex    status1|status2|status3|etc
 
for multiple users use this one line. unfortunately you can't use user labels in advanced search yet here:
owner username matches regex  ^(user1|user2|user3|etc)$
 
note: i put stricter criteria here because user_names often overlap (bob_jones and bob_jones_jr)
 
for a timestamp you can use something like. unfortunately it has to be a static date. 
created > 2013-04-01
 
In 5.5 you may be able to do more dynamic things in a report to further help creating this type of report using:
  • relative timestamps
  • states (like "opened") instead of all the opened status values
  • user labels
 
Answered 04/18/2013 by: GillySpy
Seventh Degree Black Belt

Please log in to comment

Answers

1

YOu can always do a search by the submitter name using Advanced Search, same with owner. it depends on if you need it all in one report or each one separately. What you are asking the reporting wizard should be able to provide to you. 

If you need adhoc type you might have to use the advanced search as you need it. 

You can also create a custom view and have a  view for each of your techs to see what they are working on but you can also see what is in their queue as well from the drop down on the right by owner. 

Answered 04/18/2013 by: nshah
Red Belt

  • nshah's answer is way simpler than mine. Although I have found the SQL query very useful for creating ticket rules in the Help Desk.
Please log in to comment
1

It sounds like you are wanting multiple reports. Do you want ALL tickets including closed tickets or only tickets in an open state? This is a basic query that pulls all the information for a ticket. Use the fields after the select statement to change the where statement to what you are looking for. The query as is will look for EVERYTHING submitted by a single user. I should give credit. Someone on ITNinja gave me this query, but I can't remember who. Probably.

Select
      -- ticket fields
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
    
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox.wacoisd.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
    
    
      -- about the updater
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
    
      -- about the owner
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
      OWNER_LABEL.LABEL_ID,
    
      -- about the submitter
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY, -- $priority
    
      -- about status
      S.NAME AS STATUS,   -- $status
    
      -- about impact
      I.NAME AS IMPACT,   -- $impact
    
      -- about category
      CAT.NAME AS CATEGORY, -- $category
    
    
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

     WHERE
SUBMITTER_UNAME = 'jsmith' /*Submitter you are looking for goes here*/

      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1

To include the status as a variable you would change the line after WHERE to something like:

(S.NAME = 'New' AND SUBMITTER_UNAME = 'jsmith')

Answered 04/18/2013 by: lmland
Tenth Degree Black Belt

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment
1

I'm not locating an "owner" or "submitter" option in our advanced search options.  We open a ticket, with the caller as the requestor, then assign it to a technician.  Sometimes that technician is the person opening the ticket, and sometimes not.  The requestor is almost never the person who is entering the ticket at our helpdesk.  Would we still be able to pull that data, who was the person to open the ticket (helpdesk staff)?  Would that fall under as an owner, or is the owner considered the requestor?  Is the Submitter the same thing, requestor, not the person entering the ticket info?  Sorry for all the questions.  Thank you for any feedback.

Answered 04/18/2013 by: jpanich
White Belt

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

Share