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. You can use this query type to fetch data from multiple sources in your environment.

This query type uses the SQL syntax.

For more information, see  https://bit.ly/3pUe7I8 .

This query requires the following data:

FieldDescription
Query TypeUse this field to select the query type
Type

Use one of the following options:

  • Form
    You can use all other options when you select this.
  • SQL
    You can enter an SQL query when you select this.
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.
Option

Select details for the following options:

Use Distinct:

  • Fetch Entries
    Enter the number of entries that the panel fetches.
  • 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.
FromSelect 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.

Example

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

SELECT DISTINCT
COUNT(`CHG:Infrastructure Change`.`Request ID`) AS C1
FROM
`CHG:Infrastructure Change`
WHERE
(`CHG:Infrastructure Change`.`Change Request Status` >= 'Draft') AND (`CHG:Infrastructure Change`.`Change Request Status` < 'Completed') and (`CHG:Infrastructure Change`.`ServiceCI_ReconID` = $ServiceID) and (`CHG:Infrastructure Change`.`Submit Date` >= $__from/1000 and `CHG:Infrastructure Change`.`Submit Date` <= $__to/1000)

Was this page helpful? Yes No Submitting... Thank you

Comments