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:
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:
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:
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`