Reporting - Requests Assigned to a Team

[Stephen.whittle]

Trying to get an accurate report for ALL requests that at some stage in a request lifecycle have been assigned to a team. Not fully confident that requests included in reports that just have the filter "team is" as I am not sure if this factors requests that have touched that team at some stage in their life cycle or if its only including requests that have: Been logged directly against that team initially as per BPM. 

Teams have interactions with requests but are not the team that the request was initially assigned to in the BPM or they are indeed the team that resolve the request but it is usuaful to capture ALL requests that at some stage have been assigned to a given team for input so I can record that interaction and understand the demand on the teams. 

 

Is anyone able to calrify or help? 

[Guest Mary]

@Stephen.whittle

Your report can include the  h_itsm_request_team_assignment table which stores the current and previous teams on a request as well as timestamps of when the assignment took place. 

[Simon Sharkey]

I've been asked to report on calls assigned to teams / team members. I've got supportworks and cannot find a way to get a maximum value if it has been passed to differing teamsin the management report suite. However I was able to find a way in the MYSQL using the Database searches option.

My code looks a simple as this: Please note you probably wont have the call priority of 'P5 2YR SR' that we developed for projects.

In our supportworks version, we don't appear to have the table h_itsm_request_team_assignment

SELECT opencall.callref,
MAX(updatedb.updatetimex),
(updatedb.udindex)+1,
updatedb.updatetxt,
updatedb.updatetimex,
opencall.suppgroup,
opencall.owner,
opencall.status
FROM updatedb JOIN opencall ON opencall.callref = updatedb.callref
WHERE updatedb.updatetxt LIKE '%Assigned%'
AND opencall.status NOT IN (6, 16, 17, 18)
AND opencall.priority <> 'P5 2YR SR' 
GROUP BY updatedb.callref
ORDER By opencall.suppgroup

 

This works well enough and can be saved to excel for analysis

Hope this is helpful.

 

Mary, I wonder if you know of a way to derive this from the management reports, I cannot see a way to obtain the maximun value in there.

Thanks, Si.

 

[Guest Mary]

@Simon Sharkey, the table is in Hornbill ( Service Manager) and not Supportworks. Please post under the Supportworks Reporting Forum and I'll provide details of how you can obtain this information. 

Thanks 

[Simon Sharkey]

Thanks Mary,

It is confusing trying to use the forums method to get help with Supportworks. 

Ideally, I should be able to land on Supportworks area and still have the option to look into other areas of Hornbill. As it is I land somewhere in forum and try to raise a post for help. No wonder we get confused.

Si

 

[Bob Dickinson]

Hi @Stephen.whittle 

So if assuming that EVERY request is automatically assigned to an initial team (via the BPM) - is it fair to simplify your request to say that you are looking for a report that shows any request that has been assigned more than once?

As Mary mentioned, this is all stored in a specific table. If you are having any trouble getting the data out you need - please let me know the specifics (date range, columns, grouping) you would expect to see in the report and I'll see if I can assist.

Kind Regards

Bob

[Adam Toms]

Hi all,

I have had a very similar request come through from our teams here.

My filtering and reporting knowledge is a bit green, and I'm learning more and more each day. I have managed to successfully join the requests table along with the request team assignment table.

What I am trying to achieve is an adhoc report I can run that looks at all tickets under the Information Security Service that have been assigned to our Security Operations Team at some point of the tickets life cycle.

I thought the easiest way that would be to join the requests and request team assignment table, and look at tickets, that contain information security service, that were resolved last month, that were Incidents and Service Request, as Per my filtering screenshot. I would then extract this into a CSV file, and filter the assignment queue in excel. As I could then use this for other teams, should the need arise.

This does generate data successfully, but it's inaccurate.

Taking the first Incident reference number in  the data report generates screen shot I've looked up the ticket and as can be seen from the Teams the ticket was assigned to screenshot this was only ever assigned to Service Desk and Security Operations, yet my report contains data from other teams of which the ticket never touched.

I'm baffled as to why it would contain assignment teams that the ticket never touched. Does anyone have any ideas?

Many Thanks.

Adam

 

[Bob Dickinson]

Hi @Adam Toms

This may be an issue with your JOIN statement which is why it's returning multiple rows. 

Would you be able to export your report definition and send it to me - and I can take a look for you?

Kind Regards

Bob

[Adam Toms]

Hi @Bob Dickinson,

Apologies for the delay in getting back to you operational activities have meant that I haven't been able to check back on this post until today.

So what I have done is I've added those two tables, but haven't actually joined them together. I've then all my work in the filtering part, to get the info out of the two tables. By the sounds of it a join may of been a better way however, initially the way I set this up, it looked good, until I found anomalies in the data.

As requested I've attached the report definition to this this post.

Many Thanks

Adam

tickets-that-have-been-assigned-to-sec-ops-queue-at-some-point-in-the-ticket-life-cycle.report.txt

[Bob Dickinson]

Hi @Adam Toms

Yes looking at the report there were a few issues with the JOIN (you were joining it on the Team ID, rather than the request ID) - and also you had a filter to only look for tickets that were currently assigned to Sec Ops (rather than looking for tickets that have EVER been assigned to Sec Ops). 

I have cleaned it up a bit, and hopefully it's working a bit better now - it's attached to the bottom of this post. You might see the odd duplicate row for the same request ID. This indicates that the ticket was assigned to Sec Ops more than once in it's lifecycle. To make that slightly clearer, I've also add a column showing the Date/Time the assignment to Sec Ops occured. 

Upload it as a new report and let me know if it now shows what you were expecting!

Kind Regards

Bob

wessex---tickets-that-have-been-assigned-to-sec-ops-queue-at-some-point-in-the-ticket-life-cycle---bob-update.report.txt

[Adam Toms]

Hi @Bob Dickinson

That's perfect, that's much more accurate than the results I was able to achieve.

I like the fact with what you've produced that if a ticket has been assigned to the queue multiple times, it will appear as a duplicate, but give the timestamp, so showing the each individual time it was assigned. That's incredibly useful, and not something I had thought of.

I'm still learning on my joins and filters as I have had no prior SQL knowledge, and what I have picked up has only been very recent. 

Thanks again for all your help.

Kind Regards

Adam

 

[Bob Dickinson]

Hi @Adam Toms

No problem, I'm glad it helped. 
Yes, SQL Joins are tricky if you have never done them before. It's one of the areas we are very aware of and we have some developments ongoing to make this process a lot more seemless/intuitive in the future, and prevent the need to know any SQL whatsoever. 

But in the meantime, please let us know on the forums if you have any more issues or questions and we will help wherever we can.

Kind Regards

Bob