Jump to content

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


dwalby
 Share

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
ORDER BY Linked DESC
LIMIT 10

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
 Share

×
×
  • Create New...