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.

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.

    Important

    While running large queries, we recommend setting the timeout option up to 600 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 add 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 hide icon to prevent the columns from appearing in the panel.

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

Where

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

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

Use this option to filter data in your dashboard column by using the non-aggregated calculated fields. For example, you can filter the incidents by aging in your dashboards. 

To filter the incidents by aging:

  1. In the Fields box, select the Incident Ageing calculated field.
  2. In the Equal box, specify >15 days to filter the incidents which are older than 15 days.
Group By

Select the values to group items on your panels together.

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

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

Having

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

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

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

Use this option to filter data in your dashboard column by using the aggregated calculated fields. For example, you can filter the count of open incidents by submitter against the submitter in your dashboards. 

To filter the count of open incidents by submitter:

  1. In the Fields box, select the Open Incidents calculated field.
  2. In the Equal box, specify 10 to filter out submitters with 10 open incidents.
Order By

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

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

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

Calculated Field

As a tenant administrator or editor, add one or more predefined calculated fields to the service management query without writing any SQL script.

  1. In the Query section, select the BMC Helix data source.
  2. In the Query Type field, select Service Management.
  3. In the Type field, select Form and in the Format As field, select Table.
  4. Select a form from the list of available forms. 
    For example, HPD:Help Desk form is used for the Incident Management module.
  5. Select the appropriate calculated fields.

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

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

Currently, the following calculated fields are supported by their respective IT service management modules:

IT Service Management module

Form name

Calculated fields

Incident ManagementHPD:Help Desk
  • Number of Incidents—Displays the count of incidents.
  • Open Incidents—Displays the count of open incidents.
  • Avg MTTR in days—Displays the mean time in days before the incident request was resolved.
  • Avg MTTAcknowledge in days—Displays the mean time in days before the incident request was acknowledged.
  • Percentage of Reopened Incidents—Displays the percentage of reopened incidents.
  • Percentage of SLA Met Incidents—Displays the percentage of incidents that met the service-level agreement.
  • Resolved Incidents—Displays the number of incidents that are resolved.
  • Closed Incidents—Displays the number of incidents that are closed.
  • Incident Ageing—Displays the count of total/open/resolved/closed incidents for a period of 1 day, 1-3 days, 3-7 days, 7-15 days and more than 15 days.
Change ManagementCHG:Infrastructure Change
  • Number of Changes—Displays the count of change requests.
  • Open Changes—Displays the count of open change requests.
  • Resolved Change—Displays the number of incidents that are in the resolved state.
  • Closed Change—Displays the number of incidents that are in the closed state.
  • Delayed Changes (open and late)—Displays the number of changes that are delayed.
Problem ManagementPBM:Problem Investigation
  • Number of Problems—Displays the count of problems.
  • Major Problems—Displays the count of open problems.
Work OrderWOI:WorkOrder
  • Number of Work Orders—Displays the count of work orders.
Task ManagementTMS:Task
  • Number of Tasks—Displays the count of tasks.
Release ManagementRMS:Release
  • Number of Releases—Displays the count of releases.
Knowledge ManagementRKM:KnowledgeArticleManager
  • Number of Knowledge Articles—Displays the count of knowledge articles.
Service Request ManagementSRM:Request
  • Number of Service Requests—Displays the count of service requests.
Asset ManagementAST:BaseElement
  • Number of Assets—Displays the count of assets.
CMDBBMC.CORE:BMC_BaseElement
  • Number of CI—Displays the count of CI’s.
Asset AvailabilityAST:CI Unavailability CI Join
  • Avg MTBF in days—Displays the mean time between failures in days (for selected CI).


To create custom calculated fields

As a tenant administrator or an editor, create custom calculated fields that suits your business requirements. 

Refer to the following video (3.26) to see how you can create custom calculated fields:


https://youtu.be/9RC9yR_jzks

To use calculated fields as filters

As a tenant administrator or an editor, use the calculated fields as dashboard filters. 

Refer to the following video (2.55) to see how you can use calculated fields as dashboard filters:


https://youtu.be/UgEZzcgQSBM


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

Comments