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.

This query requires the following data:

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`

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`

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`