help Reporting on Problems and their linked requests

[Jeremy]

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?

[Steve Giller]

Is this the kind of thing you're thinking of?

 

[Steven Boardman]

@Jeremy I've attached a report def file which might get you started and you can then tweak to your specific needs.

The output is currently like this

Hope that helps

 

 

problems-with-linked-incidents.report.txt

[Jeremy]

@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

[Met]

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

[Jeremy]

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

[Met]

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

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.

[Met]

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