Building SQL queries to analyze services data

Scenario

Susan is a Service Desk manager in an IT organization. She is responsible for managing and tracking changes to the IT infrastructure to keep the service disruption to a minimum. She wants to know how many change requests are generated for each business service. She also wants to filter business services according to critical change requests. By using BMC Helix Dashboards, Susan can build a query to get this information.

Benefits

The visual query builder saves your query creation time. Additionally, users with no prior knowledge of SQL can create queries by using the visual query builder.

Workflow

Susan performs the following tasks:

1. Create a new dashboard and configure it to fetch the business services data.
2. Create variables to be used as filters.
3. Update the query to use these variables and view the results.

Task 1: Create and configure a dashboard 

1. In BMC Helix Dashboards, select Add > Visualization.
2. In the Data source list, make sure that BMC Helix is selected.
3. From the Query type list, select Service Management.
4. From the Type list, select Visual query builder.
5. On the Visual query builder page, perform these steps:
   1. From the View list, select Change Management.
   2. Expand Change Details and drag the Business Service and Number of Changes fields to the Fields area.
      
      
      
      
   3. Click Preview.

1. 1. Click Use query.
      

1. Configure the visualization settings.
   For details, see Configuring-dashboards-panels-and-queries.
2. Save the dashboard.

Susan gets the total count of change requests for each business service. Now, she wants to filter the change requests according to their priority. Susan creates a variable to be used to filter change requests according to their priority. So, she creates the variable:

Task 2: Create a variable to be used as a filter

1. Open the dashboard that is saved.
2. In the Dashboard settings section, select Variables > Add variable.
3. On the variable page, perform these steps:
   1. In the General section, provide a name, label, and optional description for the variable.
   2. In the Data source list, make sure that BMC Helix is selected.
   3. Enable the query editor.
   4. Click Query Builder.
      
      
      
      
   5. From the View list, select Change Management.
   6. Expand Change Details, and drag the Priority field to the Fields area.
   7. Click Preview and then click Use query.
      
      
      
      
   8. Under Selection options, specify whether you want to enable multiple value and all options selection.
   9. Click Apply.
      The variable is created.
4. Save the dashboard.

Susan now updates the dashboard query to use the variable that she created.

Task 3: Update the dashboard query to use the variable and view the results

1. Open the dashboard.
2. Edit the panel.
3. Click Query builder.
4. Expand Change Details and drag the Priority field to the Filters area.
5. Configure the filter value.
   
   
   
   
6. Click Preview.
7. Click Use query.
   
   
   
   
8. Save the changes.
   Susan can now filter change requests according to priority.
   
   
   
   

Results

Susan gets information about the business services with critical status and can inform the stakeholders to resolve the change requests on priority.