×
 
 
  •  
$helper.renderConfluenceMacro('{bmc-global-announcement:$space.key}')

   

This documentation supports releases of BMC Helix Dashboards up to December 31, 2021. To view the latest version, select the version from the Product version menu.
BMC Helix Dashboards 21.3 ... Setting up dashboards Building queries to fetch data

Service Management query


As a tenant administrator or an editor, you can use the Service Management query type to fetch the services data in your environment.

For more information, see  Using MySQL in Grafana Open link .

This query requires the following data:

FieldDescription
Query TypeUse this field to select the query type
Type

Use one of the following options:

  • Form
    Select this option to enable other fields.
  • SQL
    Select this option to enable the SQL Editor.
Format As

Use one of the following options:

  • Table
    You can create a table with your data in the panel.
  • TimeSeries
    You can create a time series data in the panel.
Header

Add additional header properties in the request.

Use the add  icon to add a header. Depending on your selection, add the remaining details.

Use the hide icon to prevent the headers from appearing in the panel.

Use the add  icon at the end of the field to add multiple headers.

Select one of the following options:

  • Date Format
    Select the date format to be returned in the query result. For example, DD/MM/YYYY.
  • Date/Time Format
    Select the date and time format to be returned in the query result. For example, DD/MM/YYYY HH:MM:SS.
  • Locale
    Select this option to localize the enumerated values into a selected local language. For example, Status, Priority etc.
  • Row Limit
    Select this option to override the server's limit for the number of rows returned for a given query.  By default, the server limit is 2000 rows. 
    You can also specify the limit in the centralized configuration console for the JDBC query by using the Jdbc-Row-Limit parameter under the component com.bmc.arsys.server.shared. The default limit in the centralized configuration console is 10000 rows. If both the centralized configuration console and the Row Limit are defined, the server will consider the lower limit.
  • Timeout
    Select this option to enter the time (in seconds) allowed for the query to return results. By default, the timeout limit is 120 seconds.
  • TimeZone
    Select the time zone for the data to be displayed on the panel. 
Option

Select details for the following options:

  • Use Distinct
    Select this option to remove duplicate entries and display only unique values.
  • Fetch Entries
    Enter the number of entries that the panel fetches.
  • Manage DST
    Select this option to apply daylight saving time (DST) in dashboard query. By default, this option is set to SERVER, which means the DST changes are ignored. To apply DST changes to the dashboard query, set this option to APPLYDST.
  • Sync SQL
    Enable if you want to sync the SQL and move to edit the generated SQL.
  • Show Query
    Enable if you want to view the SQL query.
  • Show Help
    Enable if you want to display the help.
FormSelect a clause to group your data in the panel.
Column

Use the icon next to Column to select a column from the available list. Depending on your selection, add the remaining details for the Column.

Use the icon to prevent the columns from appearing in the panel.

Use the icon at the end of the field to add multiple statements.

Where

Use the icon to add a calculation. Depending on your selection, add the remaining details.

Use the icon to prevent the columns from appearing in the panel.

Use the icon at the end of the field to add multiple statements.

Group By

Select the values to group items on your panels together.

Use the icon to prevent these values from appearing in the panel.

Use the icon at the end of the field to add multiple statements.

Having

Use the icon to add a calculation. Depending on your selection, add the remaining details.

Use the icon to prevent the calculation appearing in the panel.

Use the icon at the end of the field to add multiple statements.

Order By

Select an ascending or descending order to sort items in the panel.

Use the icon to prevent these values appearing in the panel.

Use the icon at the end of the field to add multiple statements.

Calculated Field

As a tenant administrator or editor, you can add one or more predefined, calculated fields to the service management query without writing any SQL script. Currently, the following calculated fields are supported by their respective IT service management modules:


IT Service Management
Modules

Calculated Fields

Incident Management
  • Number of Incidents
  • Open Incidents
  • Avg MTTR in days
  • Avg MTTAcknowledge in days
  • Percentage of Reopened Incidents
  • Percentage of SLA Met Incidents
Change Management
  • Number of Changes
  • Open Changes
Problem Management
  • Number of Problems
  • Major Problems
Work Order
  • Number of Work Orders
Task Management
  • Number of Tasks
Release Management
  • Number of Releases
Knowledge Management
  • Number of Knowledge Articles
Service Request Management
  • Number of Service Requests
Asset Management
  • Number of Assets
CMDB
  • Number of CI
Asset Availability
  • Avg MTBF in days

Use the icon to prevent these values appearing in the panel.

Use the icon at the end of the field to add multiple calculated fields.

For more information about the supported calculated field queries, see Creating reports with advanced database functions by using Advanced Authoring.

Example - Average open incident age

View the average open incident age in a selected time range. This query is of the type SQL, with the output in Table format.

SELECT
AVG(DATEDIFF('day',`HPD:Help Desk`.`Submit Date`, CurrentDate())) AS C1
FROM `HPD:Help Desk`
WHERE `HPD:Help Desk`.`Status` < 'Resolved'
and `HPD:Help Desk`.`Company` In ($company)
and (`HPD:Help Desk`.`Assigned Group` In ($AssignedGroup))
and (`HPD:Help Desk`.`Priority` In ($priority))
and (`HPD:Help Desk`.`Submit Date` >= $__from/1000 and `HPD:Help Desk`.`Submit Date` <= $__to/1000)

Example - Calculated fields for incident management

View the total number of incidents, open incidents, and MTTR by submitter and status. This query is of the type SQL, with the output in Table format.

SELECT DISTINCT
`HPD:Help Desk`.`Submitter` AS C1,
`HPD:Help Desk`.`Status` AS C2,
COUNT(`HPD:Help Desk`.`Incident Number`) AS Total_Incidents,
SUM(CASE WHEN `HPD:Help Desk`.`Status`<'Resolved' then 1 ELSE 0 END) AS Open_Incidents,
AVG(DateDiff('ss',`HPD:Help Desk`.`Submit Date`,`HPD:Help Desk`.`Last Resolved Date`)) AS MTTR
FROM
`HPD:Help Desk`
GROUP BY
`Submitter`,
`Status`

LIMIT 100

Example - Calculated fields for change management

View the total number of changes, open changes, and emergency changes by submitter. This query is of the type SQL, with the output in Table format.

SELECT DISTINCT
`CHG:Infrastructure Change`.`Submitter` AS C1,
`CHG:Infrastructure Change`.`Change Request Status` AS C2,
COUNT(`CHG:Infrastructure Change`.`Infrastructure Change ID`) AS Total_Changes,
SUM(CASE WHEN `CHG:Infrastructure Change`.`Change Request Status` > 'Draft' AND `CHG:Infrastructure Change`.`Change Request Status` > 'Completed' THEN 1 ELSE 0 END) AS Open_Changes,
SUM(CASE WHEN `CHG:Infrastructure Change`.`Change Timing` = 'Emergency' then 1 ELSE 0 END) AS Emergency_Changes
FROM
`CHG:Infrastructure Change`
GROUP BY
`Submitter`,
`Status`

LIMIT 100

Example - Calculated fields for problem management

View the total number of problems, major problems, and resolved problems by submitter. This query is of the type SQL, with the output in Table format.

SELECT DISTINCT
`PBM:Problem Investigation`.`Submitter` AS C1,
`HPD:Help Desk`.`Status` AS C2,
COUNT(`PBM:Problem Investigation`.`Problem Investigation ID`) AS Total_problems,
SUM(CASE WHEN `PBM:Problem Investigation`.`Priority` = 'Critical' THEN 1 ELSE 0 END) AS Major_Problems,
SUM(CASE WHEN `PBM:Problem Investigation`.`Investigation Status` IN ('Completed','Closed') THEN 1 ELSE 0 END) AS Resolved_Problems
FROM
`PBM:Problem Investigation`
GROUP BY
`Submitter`,
`Status`

LIMIT 100

Was this page helpful? Yes No Submitting... Thank you
Last modified by Shantanu Chatterjee on Feb 24, 2022

Comments

  1. Mohammad Abdul Wajid Basha

    Query for Example - Average open incident age seems to be incorrect, query fetches details from CHG:Infrastructure Change form

    Nov 23, 2021 09:24
    1. Shantanu Chatterjee

      This has been corrected and will reflect in the upcoming release.

      Dec 07, 2021 04:55
  2. Daniel Schaefer

    "Example - Calculated fields for change management" also seems to be incorrect. SUM(CASE WHEN CHG:Infrastructure Change.Change Request Status > 'Draft' AND CHG:Infrastructure Change.Change Request Status > 'Completed' THEN 1 ELSE 0 END) AS Open_Changes, needs to be < 'Completed' to reflect open changes

    Also I do not see any sense in adding Incident status to that query. Rather should be Change status: HPD:Help Desk.Status AS C2

    Nov 25, 2021 07:53
    1. Shantanu Chatterjee

      This has been corrected and will reflect in the upcoming release.

      Dec 07, 2021 04:54

Log in or register to comment.

Metric query
Adding descriptions and tags to dashboards
© Copyright 2020-2021 BMC Software, Inc.
Legal notices

Powered by Atlassian Confluence and Scroll Viewport

© Copyright 2005-2024 BMC Software, Inc. Use of this site signifies your acceptance of BMC’s Terms of Use . BMC, the BMC logo, and other BMC marks are assets of BMC Software, Inc. These trademarks are registered and may be registered in the U.S. and in other countries.