Report of Incidents created by the Service Desk for other ITIL users

[mark_weitzel]

We need to drive ITIL users to create their own incidents (and service request) rather than calling the Service Desk.  I am looking to create a report where, for incidents, the affected user has an ITIL role, but it was opened by a member of the group "Service Desk", The members of the group have the default group set to "Service Desk".

I can get a list of all incidents opened by the Service desk, but I want to filter it to "affected user has role ITIL"

I think a database view might work, but I've never created one and I'm not sure how to set it up.

Thanks in advance for any help.

[jMarshal · Accepted solution]

You do want to use a database view, where the join is on the user. 

The user you are joining with looks at "caller id" on the incident table and "user" on the "sys_user_has_role" table. This will create a db view which lists all records in the incident table, with the roles that user has - this creates a very large table so filter your "sys_user_has_role" to only records that are itil, as that's all you're interested in.

You then report on that data source (join table/view) where "created by != caller id"...this should show you all the incidents where the affected user (caller id) has the ITIL role, but didn't create the INC record themselves.

You can further refine this to list only those where "created by" is members of the Service Desk by joining the previous data set with the "sys_user_grmember" table, which stores group membership, filtering on the Service Desk group. Join on the user record again (with "created by" on the previous dataset and "name" on the newly added grmember table).

Chances are, the unfiltered result of this data set will be what you're looking for.

Here's a link which provides details about creating the data set: Create Reports based on Multiple Datasets - Report Designer Interface - Support and Troubleshooting ...

[mark_weitzel]

Thanks so much! I will try this out.