Database View -Incident, Change request, task_ci - where clause

[Raja keerthana]

Hello experts, 

I need help creating dbv between Incident, change_request, task_ci and cmdb_ci_outage. 

Objective - To have a list of all affected ci's of change request, incidents mapped to that ci's, and outage window of that ci altogether in one report. I can get the outage window with out any issue. Need advice on linking Incident, change request, and task_ci.

Note: I know we can dot walk from task_ci table to get the task details. But I want the report to be filtered like this.

Conditions: opened(incident)

Report:

[ronprice · Accepted solution]

Here is an example of how those tables can be linked.  Note that the join to the Outage record is a Left join.  This is because it is possible that you may not have any Outage records (yet) for the impacted CI, but you would still want to have rows returned for Change and Incident.

Please mark as correct if this answered your question.

[Raja keerthana]

Thank you so much for your solution..!

I understand it. Anyhow now my report created out of that DBV is not loading because of huge data and so I restricted data from change req to have only 4 assignment groups. while I add 3 of them in where clause , Report is working fine. When the fourth one is added which has more records, reporting is loading for a long time (not at all loading even though after an hour wait), 

Here is my dbv - modified where clause of change req and incident, as I need all CI's affected (affected CI's ) from change request (not only main configuration item of change req) 

change req Where clause: 

affci_task=chg_sys_id &&(chg_assignment_group='4eafff27979909508..................'||chg_assignment_group='64bfb367979909508..............'||chg_assignment_group='c496bc2f97ad419..................'||chg_assignment_group='7776f42f97ad41908..................')

DBV:

Report : 

Any solution for this?

Thanks, 

Keerthana