Admin Instance and Example Widgets - Bounce Count

[Ben Maddams]

Hello,

I've recently been attempting to produce reports and widgets against the number of assignment bounces on incidents and SRs, and found the thread below: 

In this thread, there is reference to an admin instance we can use to look into example widgets, but the credentials dont appear to be working. Can you please clarify if
this instance is still accessible and what login we should be using to look at it?

At the moment I'm trying to create a report into our monthly most bounced tickets, and it would really help to look at some working examples.

Thanks,

Ben

[Jim]

The crooks of your query would be this

select Count(h_request_id) as amount, h_request_id, h_assignment_by, h_previous_team_id, h_team_id from h_itsm_request_team_assignment
Group by h_request_id
order by amount desc

Then apply to whatever it is you need i.e a widget, or a measure, report etc. If you have database direct access you could put this straight in, or look a the available data in h_itsm_request_team_assignment to help customise your query

[Jim]

You my wish to expand further and ignore cancelled requests, and maybe if its assigned to the same team it doesn't count as a bounce like this 

 

select Count(h_request_id) as amount, h_request_id, h_assignment_by, h_previous_team_id, h_team_id from h_itsm_request_team_assignment as tableA
Left Join h_itsm_requests as tableB on h_pk_reference = h_request_id
WHERE h_team_id != h_previous_team_id
AND h_status != "status.cancelled"
Group by h_request_id
order by amount desc

or maybe the first assignment doesn't count so like this 

select Count(h_request_id) as amount, h_request_id, h_assignment_by, h_previous_team_id, h_team_id from h_itsm_request_team_assignment as tableA
Left Join h_itsm_requests as tableB on h_pk_reference = h_request_id
WHERE h_team_id != h_previous_team_id
AND h_previous_team_id != "No Previous team"
AND h_status != "status.cancelled"
Group by h_request_id
order by amount desc

Granted in my examples the extra fields show only 1 record against each request due to it being a count, but just included for demo mainly

[Conor]

@Jim That is superb  @Ben Maddams Jims answer above is bang on and an excellent reference

To answer your other question though that instance is still available, but the admin part of the URL doesn't exist anymore as the admin portal has moved into the main user app - this is the URL you want, same credentials - https://live.hornbill.com/demo/admin/app/com.hornbill.servicemanager/advanced-analytics/dashboards/15/

[Ben Maddams]

@Jim Thanks so much for your assistance with this! Was exactly what I was looking for and you've been incredibly helpful!