Helpdesk Tickets by AD "Department"

[francisswest]

Looking to make a report that gives me a total number of tickets by department (tblADusers).

I've been able to build something *sorta* like what I want. It groups the Departments together, and gives me a count of the tickets, but the count is super huge compared to what it should be.

Tickets Department


9534
71505 teama
15890 teamb
7945 teamc
63560 teamd
3178 teame/teamz
28602 teamf
82628 teamg
92162 teamh
1589 teamaTeam


I cant seem to get the calculation as to why the numbers are so huge. Is it combining the ticket numbers all together? (ticket 178 and ticket 3000 for TeamE/TeamZ, combing for 3178?)

Here is my current report (please forgive me, I cant write a report to save my flippin life.)

Select Top 1000000 Count_Big(htblticket.ticketid) As Tickets,
tblADusers.Department
From htblticket,
tblADusers
Group By tblADusers.Department

[francisswest]

Charles,

Thanks for the work you did on that query.

The report looks at the department of the agent of a ticket (so the agent must be an AD user with that department).

Is it possible to bypass this? I have users with multiple different "Department" names, and my team is not one of them (diff department). Can you think of the best way to accomplish that?

Thank you again!

[Esben_D]

You can try this report. It should show a count of ticket per team for a specified department. The report looks at the department of the agent of a ticket (so the agent must be an AD user with that department).

You can change the department I highlighted below to your own department names.

Select Top 1000000 Count(htblticket.ticketid) As NumberOfTickets,
tblADusers.Department,
htblteams.teamname
From htblticket
Inner Join htblticketteam On htblticket.ticketid = htblticketteam.ticketid
Inner Join htblteams On htblteams.teamid = htblticketteam.teamid
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Inner Join htblusers On htblusers.userid = htblagents.userid
Inner Join tblADusers On tblADusers.Username = htblusers.username And
tblADusers.Userdomain = htblusers.userdomain
Where tblADusers.Department = 'IT Department'
Group By tblADusers.Department,
htblteams.teamname

It is possible to have different departments in different columns, but this would require subqueries which is more work.

[francisswest]

Charles.X wrote:
You can try this report. It should show a count of ticket per team for a specified department. The report looks at the department of the agent of a ticket (so the agent must be an AD user with that department).

You can change the department I highlighted below to your own department names.

Select Top 1000000 Count(htblticket.ticketid) As NumberOfTickets,
tblADusers.Department,
htblteams.teamname
From htblticket
Inner Join htblticketteam On htblticket.ticketid = htblticketteam.ticketid
Inner Join htblteams On htblteams.teamid = htblticketteam.teamid
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Inner Join htblusers On htblusers.userid = htblagents.userid
Inner Join tblADusers On tblADusers.Username = htblusers.username And
tblADusers.Userdomain = htblusers.userdomain
Where tblADusers.Department = 'IT Department'
Group By tblADusers.Department,
htblteams.teamname

It is possible to have different departments in different columns, but this would require subqueries which is more work.

Charles,

Thank you for the work you have done on this. Im finding it to, sadly, not be what i'm looking for.

Any other thoughts?

Thanks