Setting up variables for dashboard queries

As a tenant administrator or editor, configure variables, which work as placeholders for values that you can use in dashboard queries and panel titles. Instead of hard-coding details such as server name or application name, use variables in your queries so that the system picks up the details at run time.

You can see variables in the lists at the top of a dashboard.

Related topics

Creating-and-customizing-dashboards

Building-queries-to-fetch-data

Copying-reports-and-dashboards-from-BMC-Helix-ITSM-Smart-Reporting-to-BMC-Helix-Dashboards

Filtering-data-and-personalizing-dashboards

To configure a variable to download dashboards, panels, and reports in XLSX with date and time format settings


To add a variable

  1. On any dashboard, click Dashboard settings dashboard_settings_icon.png.
  2. In the left navigation pane, click Variables.
  3. On the Variables page, click New.

  4. Depending on the variable type that you select, add the remaining options. For more information about variable types, click here.

  5. Click Apply, then Save.

Important

For Service Management queries, when Query is selected as the Type, you need to specify the SQL query variable in a specified format. Here is an example of the SQL query variable format:

remedy,{"sql": "Select Distinct case when `CHG:Infrastructure Change`.`ASGRP` is not null then `CHG:Infrastructure Change`.`ASGRP` else 'Unknown' end from `CHG:Infrastructure Change` limit 500"}

To create a query by using the visual query builder

While creating a variable, you can use the visual query builder to create a SQL query for the Service Management query type. The query is built automatically after you select a field from a view. You can also add filters to the query.

To create the query, perform these steps:

  1. On the Variables page, enable query editor under Query options.
  2. Click Query Builder.
  3. From the View list, select the required view.
  4. Select the required field and drag it to the Fields area.
  5. (Optional) Select and drag fields to the Filters area to configure filters.

    23.4_variable_query_vqb.png

  6. Click Use query.
    The query is created.
  7. Click Apply and then click Save.
    The variable is created and displayed at the top of the dashboard.


To manage variables

On a dashboard, click Dashboard settingsdashboard_settings_icon.png.

In the navigation pane, click Variables, and perform the following tasks:

Task

Procedure

Copy a variable

Click Duplicate Variable copy_variable.png against a variable. You can see the variable copied on the same page.

Click the copied variable and edit it as required.

Edit a variable

Click a variable.

Make your changes and click Update.

Delete a variable

Click Remove Variable delete_variable.png against the variable that you want to delete.

To configure the variable for supported report formats

As a tenant administrator or editor, configure the supported report formats for any of the out-of-the-box BMC Helix ITSM Printable dashboards or any custom dashboard using the supported_report_types variable. After you specify the supported report formats by using this variable, users will only see those formats when they download or schedule reports:

  1. On a dashboard, click Dashboard settings dashboard_settings_icon.png.
  2. In the navigation pane, click Variables.
  3. Click supported_report_types variable from the list of variables.
  4. From the Hide menu, select Variable.
  5. In the Values separated by comma field, specify the supported report types, separated by commas, and click Update. 
    For example, specify PDF,XLS,CSV to enable users to download dashboards or schedule reports in all three formats. 

Important

CSV report format will export only the table-type panels. 


To filter the Service Dashboard by services

As a tenant administrator or editor, filter the Service Dashboard by services. By default, the Service Dashboard displays data for the business services that are present in your tenant. By using variables, you can choose to display the metrics either for business or for technical services. 

  1. On the Service Dashboard, click Dashboard settings dashboard_settings_icon.png.
  2. In the navigation pane, click Variables.
  3. Create a new variable and configure the following options:
    • Name—Specify a name for the variable.
      For example, let's specify serviceName as the name of the variable.
    • Type—Select Query.
    • Label—Specify a label. 
      For example, let's specify Service Name.

    • Query—Specify the following query to display the business services in your tenant:

      smartgraph,{"query":"search BusinessService show #id,name","displayName":"name"}
  4. Click Update.

Important

To build queries for filtering services, see Logical and arithmetic expressions in BMC Discovery documentation.

Examples

  • Specify the following query to display the list of business services that are mentioned in the query:

    smartgraph,{"query":"search BusinessService where name in ['KM Service', 'Windows Service'] show #id,name","displayName":"#id"}

    where the data is displayed for the KM and Windows services.

  • Specify the following query to display all the business services that contain Windows in their names:

    smartgraph,{"query":"search BusinessService where os_type has subword "Windows" show #id,name","displayName":"#id"}


To filter data by domains in BMC Helix Continuous Optimization dashboards

Filter the dashboard data based on specific domains at runtime by configuring the domain filter in a variable. You can then share the dashboard report with respective stakeholders based on the domains they are interested in. For example, you can display only those systems with domains "AWS".

Task 1: To create the variable 

  1. On a custom dashboard, click Dashboard settings dashboard_settings_icon.png.
  2. On the Settings page, click Variables.
  3. Click + New variable and specify the following values:
    1. Select variable type: Optimizer variable 
    2. Name: appid
      Important: Make sure that you don't enter any other name than appid. 
    3. Label: Label that will be displayed as a filter in the dashboard. For example, Domain.
    4. Show on dashboard: Label and value
    5. Select optimizer variable type: Domain filter 
  4. Click Apply. The new variable is created and displayed at the top of the dashboard. 


domain_picker_24.3.png

Task 2: To filter data based on specific domains

  1. Click the domain filter at the top of the dashboard to locate the required domain quickly.
    1_domain_24.3.png

  2. Use the search box or domain picker to locate the required domain quickly. You can use an asterisk as a wildcard.
    Select the required domains and sub-domains.
    2_domain_24.3.png

The dashboard displays data for only the selected domains. 
3_domain_24.3.png


To display the filter data with NULL values

As a tenant administrator, filter data that contains NULL values in a dashboard. This information can help you identify missing data or incomplete records in a dashboard. 

Important

This option applies to the Service Management query type only and works with BMC Helix ITSM 21.3.06 and later. To enable this option, add the "Jdbc-Filter-By-Reserved-Keyword" parameter to the AR system Administration Console.

Steps to add the parameter
  1. In your browser, type http://midTierServerInstallDirectory/arsys/forms/serverName
  2. Log in.
  3. Select AR System Administration > AR System Administration Console.
  4. Expand System > General > Centralized Configuration.
  5. Select com.bmc.arsys.server.shared.
  6. Add the Jdbc-Filter-By-Reserved-Keyword parameter and set its value to T.

    Jdbc-Filter-By-Reserved-Keyword_parameter.png
  1. On a dashboard, click Dashboard settings dashboard_settings_icon.png.
  2. In the navigation pane, click Variables.
  3. On the Variables page, click the appropriate filter variable for which you want to filter the data.
  4. In the next screen, select Include all option and enter the following GUID in the Custom all value field:
    'ARJDBC6460AC66AB204CA7BE8869BB9AF532F9'
  5. Select Apply > Save dashboard.

    null_variable_23_1_02.png

    With this configuration, all the variable values including Null values are retrieved. If you want to retrieve only the values that are available in the variable list, contact BMC SaaS Operations team.


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*