Field for ticket 'Assigned to'

[Joyce]

Hi @Bob Dickinson

I want to create a report for tickets which have been re-assigned more than one. Which table and which field have ticket assignment  details?

 

Thanks.

Regards,

Joyce

[Joyce]

Hi 

I want to create a report for tickets which have been re-assigned more than one. Which table and which field have ticket assignment  details?

 

Thanks.

Regards,

Joyce

[Guest]

Hi @Joyce

We have a table called h_itsm_request_team_assignment which records an entry every time a ticket is:

a) Assigned to a new team
b  ) Assigned an owner or new owner

From this you should be able to work out which requests have been assigned multiple times. If you need any assistance, please let me know the type of Report/Chart you are looking to create, any criteria (e.g. Type of request? Assigned between teams or assigned between owners?) and over what time period

Kind Regards

Bob

[Joyce]

Thanks @Bob Dickinson,

I have create a measure and widget for it, for count of tickets which has been assigned more than once. (please see Measure: Incident reassigned more than twice- Monthly) and Widget : Incidents reassigned more than twice monthly. However, the count is count every row, so if one incident has been re-assigned several times, it will count it each time.

I am looking to count one incident only once, as long as it has been re-assigned more than once. How can i filter that?

Thanks.

[Joyce]

Hi @Bob Dickinson

I just want to know, how many tickets in a month, have been reassigned more than twice. My measure above includes all ticket which have been re-assigned from an original owner. But i am interested on the one which got re-assigned again to a third owner

[Guest]

Hi @Joyce

Ok, from the above I assume you are referring to an assignment as any time a request has a New Owner

You are right in saying that it creates a row for every single time its assigned to a team or an assignee - so for example:

1) Team: 1st Line   Owner: None
2) Team 1st Line   Owner: Bob
3) Team 2nd Line   Owner: None
4) Team 2nd Line   Owner: Joyce
5) Team 2nd Line   Owner: Jim
6) Team 3rd Line   Owner: None
7) Team 3rd Line   Owner: Tom

I would assume that you consider this as the request having been assigned 4 times - and you are looking for your report to count any request that has been assigned 3 times or more

If this is the case, please try the following in the criteria of your measure - and see how this goes:

h_pk_reference 
IN(SELECT h_request_id FROM h_itsm_request_team_assignment WHERE h_user_id != 'No owner specified' GROUP BY h_request_id HAVING COUNT(h_request_id) > 2)

 

Kind Regards

Bob

[Joyce]

17 hours ago, Bob Dickinson said:

h_pk_reference 
IN(SELECT h_request_id FROM h_itsm_request_team_assignment WHERE h_user_id != 'No owner specified' GROUP BY h_request_id HAVING COUNT(h_request_id) > 2)

The Measure is against h_itsm_request_team_assignment  not sure how I can use h_pk_reference from a different table. Plus I need the 'Time stamp' for the assignment to group assignment monthly.

 

[Guest]

Hi @Joyce

I'm not sure I understand the last part -  "Plus I need the 'Time stamp' for the assignment to group assignment monthly"

My suggestion would be to create the measure based against the h_itsm_request table - and base the samples on the resolution date of the request (so you are measuring against the FINAL count of reassignments). 
The Assignments table is going to hold multiple rows (dates) for the same request, so I am not sure how that could be used as an accurate sample. 

Kind Regards

Bob

[Joyce]

On 9/24/2017 at 9:49 PM, Bob Dickinson said:

h_pk_reference 
IN(SELECT h_request_id FROM h_itsm_request_team_assignment WHERE h_user_id != 'No owner specified' GROUP BY h_request_id HAVING COUNT(h_request_id) > 2)

 

Thanks @Bob Dickinson the above worked for count. How can I do a percentage? So get percentage of tickets reassigned more than twice over the total tickets. What sub query should I put? 'Percentage Sub-Query'

Regards,

Joyce