Jump to content

Reporting on Problems and their linked requests


Jeremy
 Share

Recommended Posts

I am trying to report on Problems and their linked requests, but I cannot find a way of doing this.

I have the details that I need in the report e.g. Problem Title, Summary, Logged by etc but we also want to include in the report a total of the linked requests per problem that appear in the report. Is this possible?

Link to comment
Share on other sites

@Steven Boardman so what we are trying to achieve is to report on all open problems that we have in the system, the details that we need to be included in the report:

Request ID of Problem
Summary of Problem
Description of Problem
Team assigned to Problem
Priority of the Problem
Workaround of the Problem
Root cause of the Problem
Last related Incident Ref to the Problem
Total incidents attached to the Problem
Service Owner Name or Service Owner ID

Link to comment
Share on other sites

Hi @Jeremy

I've had a go for you but couldn't think of a simple way of achieving this without using Database Direct.

SELECT 
  problems.h_entity_id as 'Problem ID', 
  requests.h_summary as 'Problem Summary', 
  requests.h_description as 'Problem Description', 
  requests.h_fk_team_name as 'Team', 
  requests.h_fk_priorityname as 'Priority', 
  (
    SELECT 
      h_root_cause 
    FROM 
      h_itsm_problems 
    WHERE 
      h_fk_reference = problems.h_entity_id
  ) as 'Root Cause', 
  (
    SELECT 
      count(*) 
    FROM 
      h_sm_relationship_entities 
    WHERE 
      h_entity_id = problems.h_entity_id
  ) as 'Total Incidents', 
  problems.h_pk_reference as 'Latest Incident', 
  problems.h_datelogged as 'Date Logged' 
FROM 
  (
    SELECT 
      a.h_datelogged, 
      a.h_entity_id, 
      a.h_pk_reference 
    FROM 
      (
        SELECT 
          reqs.h_pk_reference, 
          reqs.h_datelogged, 
          ents.h_entity_id 
        FROM 
          h_itsm_requests reqs 
          JOIN h_sm_relationship_entities ents ON ents.h_linked_entity_id = reqs.h_pk_reference 
        where 
          ents.h_entity_id LIKE 'pr%' 
          and ents.h_linked_entity_id like 'in%'
      ) a 
      LEFT JOIN (
        SELECT 
          reqs.h_pk_reference, 
          reqs.h_datelogged, 
          ents.h_entity_id 
        FROM 
          h_itsm_requests reqs 
          JOIN h_sm_relationship_entities ents ON ents.h_linked_entity_id = reqs.h_pk_reference 
        where 
          ents.h_entity_id LIKE 'pr%' 
          and ents.h_linked_entity_id like 'in%'
      ) b ON a.h_entity_id = b.h_entity_id 
      AND a.h_datelogged < b.h_datelogged 
    WHERE 
      b.h_datelogged is null
  ) problems 
  JOIN h_itsm_requests requests ON problems.h_entity_id = requests.h_pk_reference 
WHERE 
  requests.h_status NOT IN (
    'status.closed', 'status.cancelled'
  )

 

Rather than querying h_itsm_problems it instead creates a table (problems) of each problem request and the latest incident (by date logged) linked to it.  We then pull information from h_itsm_requests for each of the PRs returned in our dataset where the PR is not closed or cancelled.

It would be worth doing some checks to make sure it is returning what you'd expect - not had a chance to completely test it.

Let me know if it's not working or you wanted something else.

Cheers

Met

Link to comment
Share on other sites

@Met thanks for your work. It doesn't work for me in Database Direct, but the main reason for getting this set up in Reporting was so that we can schedule the report to be sent at specific points.

I managed to get all the information in the report apart from 'Total incidents attached to the Problem' which you would have thought this information would have been stored in somewhere in the problem table.

Link to comment
Share on other sites

@Jeremy That's odd - it seems to work in our instance. Does take a good 10-15 seconds to display results though.

image.png.0b8303d4f9dd7d8538ccf9adac07ad1d.png

Does it give an error or just not display anything?

But yeah if you want it to be a scheduled report then bit of a moot point anyway - don't think it's possible to return the total incidents attached to each problem in the normal reporting area as its own column. Would require Hornbill to let people define their own custom/calculated columns rather than only what exists in a table.

Link to comment
Share on other sites

@JeremyJust realised what the issue might be - if your problem tickets are prefixed with something different. Seen Steve uses PM in which case it would be:

SELECT 
  problems.h_entity_id as 'Problem ID', 
  requests.h_summary as 'Problem Summary', 
  requests.h_description as 'Problem Description', 
  requests.h_fk_team_name as 'Team', 
  requests.h_fk_priorityname as 'Priority', 
  (
    SELECT 
      h_root_cause 
    FROM 
      h_itsm_problems 
    WHERE 
      h_fk_reference = problems.h_entity_id
  ) as 'Root Cause', 
  (
    SELECT 
      count(*) 
    FROM 
      h_sm_relationship_entities 
    WHERE 
      h_entity_id = problems.h_entity_id
  ) as 'Total Incidents', 
  problems.h_pk_reference as 'Latest Incident', 
  problems.h_datelogged as 'Date Logged' 
FROM 
  (
    SELECT 
      a.h_datelogged, 
      a.h_entity_id, 
      a.h_pk_reference 
    FROM 
      (
        SELECT 
          reqs.h_pk_reference, 
          reqs.h_datelogged, 
          ents.h_entity_id 
        FROM 
          h_itsm_requests reqs 
          JOIN h_sm_relationship_entities ents ON ents.h_linked_entity_id = reqs.h_pk_reference 
        where 
          ents.h_entity_id LIKE 'pm%' 
          and ents.h_linked_entity_id like 'in%'
      ) a 
      LEFT JOIN (
        SELECT 
          reqs.h_pk_reference, 
          reqs.h_datelogged, 
          ents.h_entity_id 
        FROM 
          h_itsm_requests reqs 
          JOIN h_sm_relationship_entities ents ON ents.h_linked_entity_id = reqs.h_pk_reference 
        where 
          ents.h_entity_id LIKE 'pm%' 
          and ents.h_linked_entity_id like 'in%'
      ) b ON a.h_entity_id = b.h_entity_id 
      AND a.h_datelogged < b.h_datelogged 
    WHERE 
      b.h_datelogged is null
  ) problems 
  JOIN h_itsm_requests requests ON problems.h_entity_id = requests.h_pk_reference 
WHERE 
  requests.h_status NOT IN (
    'status.closed', 'status.cancelled'
  )

Just need to change the PM% above to whatever prefix you use? Same for incidents too.

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...