/bundles/itninjaweb/img/Breadcrumb_cap_w.png

We need to create a simple report that shows the submitter of a ticket and how many tickets they have submitted. Our management wants to be able to distinguish our potential problem users.

Thanks

0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

Answers

0

Jay - give this a shot:

 

SELECT S.FULL_NAME AS SUBMITTER_NAME, COUNT(S.FULL_NAME) as COUNTED FROM HD_TICKET  LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)  group by S.FULL_NAME ORDER BY COUNT(S.FULL_NAME) desc

Here is what it would like like in any mysql tool:

 

 

You may want to filter out "admin" you can do this via this query:

 

SELECT S.FULL_NAME AS SUBMITTER_NAME, COUNT(S.FULL_NAME) as COUNTED FROM HD_TICKET  LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)  where S.FULL_NAME != "admin" group by S.FULL_NAME ORDER BY COUNT(S.FULL_NAME) desc
Answered 01/24/2014 by: Jbr32
Tenth Degree Black Belt

  • I should point out the data above has been altered. We have developed a custom dashboard so the data shown above is anonymous from our system. From your system it should show the full name; e.g. Barker,Bob
  • If you wanted to see tickets created within the last 30 days by submitter you could use this:


    SELECT S.FULL_NAME AS SUBMITTER_NAME, COUNT( S.FULL_NAME ) AS COUNTED
    FROM HD_TICKET
    LEFT JOIN USER S ON ( S.ID = HD_TICKET.SUBMITTER_ID )
    WHERE S.FULL_NAME != "admin"
    AND HD_TICKET.CREATED >= ( CURDATE( ) - INTERVAL 30
    DAY )
    GROUP BY S.FULL_NAME
    ORDER BY COUNT( S.FULL_NAME ) DESC
    • Thanks! This is exactly what we were looking for. Having the interval will definitely help.
      Thanks again,

      Jay
Please log in to comment

Share