Jump to content

Report/Widget - Top 10 'Highest impact' Problems/Known Errors


Recommended Posts

Hi @dwalby

Try creating a list of data widget with a custom SQL query.

The query will be something like this:

SELECT h_request_id As Reference, h_itsm_requests.h_summary AS Summary, COUNT(h_request_id) AS Connections from h_itsm_request_connections
inner Join h_itsm_requests on h_request_id=h_pk_reference and (h_requesttype='Problem' OR h_requesttype='Known error') and (h_status='status.open'  OR h_status='status.new')
Group by h_request_id
order by Connections desc
Limit 10


Link to comment
Share on other sites

  • 2 weeks later...

@dwalby I have just realised I put the wrong query above, the above query is for the Problems and Known Errors with the most connections. That may also be useful, but the query I meant to post was this one:

Select h_itsm_requests.h_pk_reference, count(h_pk_reference) as Linked from h_itsm_rel_requests, h_itsm_requests
where (h_fk_parentrequestid = h_itsm_requests.h_pk_reference OR h_fk_childrequestid = h_itsm_requests.h_pk_reference)
AND (h_pk_reference LIKE 'PM%' OR h_pk_reference LIKE 'KE%')
and (h_status='status.open'  OR h_status='status.new')
Group by h_pk_reference
order by Linked desc
Limit 10

  • Like 1
Link to comment
Share on other sites

Hi @dwalby

Try this: 

SELECT h_itsm_requests.h_pk_reference AS Reference, COUNT(h_itsm_requests.h_pk_reference) AS Linked FROM h_sm_relationship_entities, h_itsm_requests
WHERE (h_sm_relationship_entities.h_entity_id = h_itsm_requests.h_pk_reference)
AND (h_itsm_requests.h_pk_reference LIKE 'PM%' OR h_itsm_requests.h_pk_reference LIKE 'KE%')
AND (h_itsm_requests.h_status = 'status.open' OR h_itsm_requests.h_status = 'status.new')
GROUP BY h_itsm_requests.h_pk_reference

Looks like the underlying table has been updated, which will give us more information on the types of relationships between tickets and services, plus it makes the query a bit easier, but I wasn't aware when I posted the above query. Give this a try and let us know how you get on.

  • Thanks 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...