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:

Field	Description
Query Type	Use 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.
From	Select 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)

Service Management query

Example

Comments