The company I work for uses Manage Engines Service Desk Plus for it’s ticketing system. It is  great product and has some good inbuilt reporting features, however for my needs I use a suite of SQL queries to pull data out into another reporting tool to create a dashboard.

I though I would post my queries in case they help someone else out.

Query 1 – Inbound requests by priority.

Shows: A list of priorities and the number of requests raised against them.

Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 2 – Closed and resolved requests

Shows: Returns the number of requests closed or resolved.

Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 3 -Inbound requests by mode.

Shows: A list of modes and the number of requests raised against them.

Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 4 – Closed and resolved requests by technicians

Shows: Returns a list of technicians and the number of requests closed or resolved.

Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 5 – Top departments raising requests

Shows: Returns the top 12 departments and the number of requests raised. Sums all other departments into “All Other Departments”

Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 6 – Number of requests raised and total time spent by category

Shows: Returns the top 12 categories and the number of requests raised and the total time spent. Sums all other departments into “All Other Categories”

Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 7 – Pivot of Mode vs Priority

Shows: Returns a pivot of the mode vs priority i.e. how many priority 3 requests were emailed rather than phoned.

Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 8 – Queue overview

Shows: Returns a pivot of the number of open or on hold requests f each group.

Filtered by: Only shows requests which are not resolved or closed. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 9 – Technician overview

Shows: Returns the number of resolved or closed requests by technician

Filtered by: Only shows requests which are resolved or closed. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 10 – Oldest requests for a specified group

Shows: Returns the top oldest requests for a specified group

Filtered by: Only shows requests which are not resolved or closed. Only shows requests raised in the group “ServiceDesk”

Queue 11 – Survey results by group

Shows: Returns the average survey requests by group

Filtered by: Only shows survey requests created between the start_date and end_date. Only shows survey results raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”

Query 12 – Survey results by technician

Shows: Returns the average survey requests by technician

Filtered by: Only shows survey requests created between the start_date and end_date.