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

[dwalby]

Hi all,

Any suggestions on how to create a widget that displays the top 10 'most active' Problems and Known Errors? By 'Highest Impact' I mean those that have the most linked incidents to them.

Thanks in advance

[Conor]

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

 

[dwalby]

@conorh - thanks I'll give this a try

[Conor]

@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

[dwalby]

@conorh - Thanks for this, but the widget isn't showing any results despite me having incidents linked to known errors/problems.

Any suggestions?

[Conor]

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.

[dwalby]

@conorh - Thanks this works!