How to configure Database View to get Incidents without Outages

ujjwalpundi · yesterday

How to build Database View to report Incidents without Outages with specific conditions.

Could someone please guide me:

How to configure the Database View with a LEFT join so I can capture incidents without outages.
How to apply conditions on Incident fields in the same report.

Thanks in Advance

Nitish Saini · yesterday

Create a Database View for Incidents without Outages

Create Database View
- Name: u_incident_without_outage_view
- Label: MIM Incident Without Outage
- Plural: MIM Incidents Without Outage
Add Tables to the DB View
- First Table:
  - Table: incident
  - Prefix: inc
  - Where clause: leave blank
  - Left join: false
- Second Table:
  - Table: cmdb_ci_outage
  - Prefix: out
  - Where clause: inc.number = out.task_number
  - Left join: true
Save the Record
- After saving, verify it by clicking the “Try It” link in the related links.
- This will display incidents, and if there is no outage, the outage fields (out.*) will remain empty.
Create a Report
- Navigate to Reports > Create New.
- Select the table with the label name you defined (e.g., MIM Incident Without Outage).
- From here, you can:
  - Select columns from both tables (incident → inc.*, outage → out.*).
  - Apply filters on Incident fields,
    - Or any other custom conditions from both the tables as per requirement.

This way, the report will show all Incidents without outages, and you can still apply any Incident-level filters you need.

Please accept my answer and mark as helpful/correct if it resolves your query.

Regards,
Nitish Saini

View solution in original post

Ankur Bawiskar · yesterday

@ujjwalpundi

no database view required.

You can use normal report on incident table with Related list condition on Outages table and use the Equal to = 0

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

M Iftikhar · yesterday

Hi @ujjwalpundi,

You can try the following steps:

1. Create the Database View

Navigate to System Definition > Database Views.
Click New.
Fill out the form:
- Name: Give it a unique, meaningful name (e.g., u_incident_without_outages).
- Label: Provide a user-friendly label (e.g., Incidents without Outages).

2. Add the View Tables

The Database View will consist of two tables: incident and cmdb_ci_outage.

Add the Incident table:
- Click New in the "View Tables" related list.
- Table: Incident
- Variable prefix: inc
- Order: 100 (The lowest order, making it the "left" table in the join).
Add the Outage table:
- Click New in the "View Tables" related list.
- Table: Outage (or cmdb_ci_outage)
- Variable prefix: outage
- Order: 200
- Where clause:
  - inc.number = outage.task_number

3. Apply Conditions in Your Report

Once the Database View is created, you can use it to build a report. This is where you apply the conditions to find incidents that do not have an outage.

Navigate to Reports > Create New.
Table: Select your new Database View (e.g., u_incident_without_outages).
In the filter builder, you can now apply conditions on fields from both the incident and outage tables. To find incidents without a linked outage, you would add a filter like:
- Task Number is empty
You can then add any other conditions on the incident record itself, for example:
- inc.State is not one of Closed, Resolved
- inc.Category is not one of Hardware, Software
Run the report, and you'll get a list of all incidents that meet your criteria and do not have an associated outage.

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution so others can benefit as well.