Using CMDB Query Builder to show Business Critical Application Services with an Open Incident

[Thomas_Davis]

In this blog, we will take a look at creating a data source for Business Critical Application Services with an Open Incident in its hierarchy.

Application Services are not directly associated with Incidents.  An incident would be associated with something that is in an Application Service hierarchy, like a Configuration Item. There are a few different ways that you can accomplish getting this information.

1. A Database View could be created that would join Application Services (cmdb_ci_service_auto) to Incident (incident).  These would not join directly, so you would need to figure out what other tables would be needed to make the joins work to get the desired results. (requires admin role)
2. The Application Service could be added directly to the Incident table, so it would be readily available for reporting. (requires admin role)
3. Utilizing the CMDB Query Builder, you could drag the tables needed to the canvas and create the relationships needed. CMDB Query Builder allows to Convert attached nodes to patterns.  This is what allows the ability to see if an Incident is associated with anything in the Application Service hierarchy. You can also create reporting directly from the results and also create a Data Source to use in Performance Analytics. (requires cmdb_query_builder and report_user)

We will be showing #3 in this Blog. The steps to follow along in the video below are in the Blog directly below the video.

Steps:

1. Open CMDB Query Builder
2. Click Create New
3. You can name this whatever works for our Environment.  In this example, we called ours "Critical Application Services"
4. Click Create
5. Under CMDB Classes, search for "Application Service"
6. Drag to the canvas
7. Next we want to check "Convert attached nodes to pattern"
8. Click Add Columns and search for Critical, then select "Business criticality"
9. Open the Filter on the Application Service 1 (Starting Node),

• Conditions: Business criticality IS 1 - most critical

10. Click Save
11. Click Run

A new tab will open in your browser that will show the results of the query.

12. Click on the new tab and then take notice of the results.  Here we are seeing all of the Application Services that are in 1 - most critical for the Business criticality
13. Next go back to CMDB Query Builder and let's add Incident to the canvas

• Under Non-CMDB Tables, search for Incident and then drag it to the canvas

14. Click on the little square on the right side of Application Service 1 (starting node) and then click on the little square on the left side of Incident 1. This will join the two tables together
15. Click on the line that is between Application Service and Incident, then you want to check "Apply Incident 1 reference filter to all nodes in this pattern"

This looks for any relationship that is between the Application Service and an Incident.

16. In the CI reference column field, select "Configuration Item"
17. Open the Filter on Incident and set this condition:

• Conditions: State IS NOT ONE OF Resolved, Closed & Canceled

18. Click Save
19. Click Run

Notice that we are seeing the Application Service, a Configuration Item, and the Incident # that is in the Application Services hierarchy.

20. Let's add some Incident fields to be seen in the list of results

• Created
• Assignment Group
• Assigned to
• Business Duration
• Duration
• State

21. Click Save
22. Click Run

Here we can see the added columns in the results.  You can add whatever columns that you would like in your query.

23. Let's create a Pie Chart of our results. Right-click on the Application Service 1 column header, and select Pie Chart
24. Take a look at the Pie Chart that was built. Application Service 1 may not be what you want the Pie Chart to be built off of. This is just an example, build it the way that is worth most value to your Organization.
25. Let's make some cosmetic changes to the Report to make it better

• Click on the Style tab in the Report Builder
• • Click on the Title section and give your Report a better name.  Name: Critical Application service with Open Incidents
  • Next click on the General section and select "Display data labels"
  • Next click on the Legend section and select "Right" for the "Legend horizontal alignment"
  • Select "Middle" for the "Legend vertical alignment"
  • Check:
  • Show legend border
  • Left align legend text
  • Click Run

26. Let's go to the Data tab in the Report Builder and rename our Report to "Critical Application Services with Open Incidents grouped by Application Service"
27. Click Save
28. Let's create a data source based on our report

• Save as data source
• Name: Critical Application Services
• Description: This data source shows the Critical Application Services with an Open Incident in its hierarchy

29. Click Submit
30. Notice that the Table under the Data tab in Report Builder is now referencing the new data source that we created.

You can also find this saved data source under Reports Sources in your Instance.

The video below shows all the steps above.

If you liked this video on building out Business Critical Application Services with Open Incidents, in CMDB Query Builder, you will want to make sure that you get registered for the Knowledge21 CreatorCon session "Leveraging Performance Analytics with CMDB Query Builder".  We go even deeper into the abilities of CMDB Query Builder and we will also build out Performance Analytics with the returned data.

Useful Links

ServiceNow Docs
Create Reports in CMDB Query Builder
Build a CMDB query using the CMDB Query Builder

Performance Analytics Academy
Intro to CMDB Query Builder with Reporting

Now Learning
Common Service Data Model (CSDM) 3.0 Fundamentals
Configuration Management Database (CMDB) Fundamentals

Knowledge20 Lab Session
Make Better Decisions with Query Builder
Drive real Value with Common Service Data Model (CSDM) and CMDB

Click here to return to the Analytics, Intelligence, and Reporting Community page.