Simple report based on submitter
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
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
Jbr32
10 years ago
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
Comments:
-
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 - Jbr32 10 years ago
-
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 - Jbr32 10 years ago-
Thanks! This is exactly what we were looking for. Having the interval will definitely help.
Thanks again,
Jay - jay.moore 10 years ago
