SCSM Reporting: Dataset for Incident Requests

It’s Christmas time again, and that means it’s time for another SQL query for Service Manager reporting. 🙂

This query gives you a dataset in list form to work with in Excel or PowerBI, containing the following informations related to Incident Requests.

ID
Classification
Closed Date
Created Date
Created Day
Created Month
Created Year
Created Week
First Response Date
Priority
Resolution Category
Resolved Date
Resolved Day
Resolved Month
Resolved Year
Resolved Week
Source
Status
TargetResolutionTime
TierQueue

You will need to run the SQL query against the Service Manager Data Warehouse database DWDataMart.

The output will be like below, when you using it in Excel.

Untitled

Mary Christmas

By | 2015-12-07T13:00:00+00:00 December 7th, 2015|Service Manager (SCSM), SQL|10 Comments

About the Author:

Brian Fahrenholtz

10 Comments

  1. Hunter May 3, 2017 at 20:19 - Reply

    This seems pretty useful, is there a way where this incident dataset is available for download? thanks

    • Brian Fahrenholtz May 4, 2017 at 13:50 - Reply

      You can mark and copy the SQL query, and then paste it into a SQL query or Excel data connection.

  2. Vibor Kalmar January 3, 2018 at 13:14 - Reply

    Hi, thank you for the query. Is there a way to get the info about incident escalation? To be exact escalation history of all incidents so we can see everyone that incident was at.

    • Brian Fahrenholtz
      Brian Fahrenholtz January 3, 2018 at 13:19 - Reply

      Hi Vibor, the escalation property can easily be added to this query. It is more difficult with the history.

      • Vibor Kalmar January 3, 2018 at 13:37 - Reply

        Thank you very much for a fast reply Brian. Yes i agree it is hard to find the history. I am begining to think it is even impossible.

  3. Josh G January 25, 2018 at 16:45 - Reply

    This is awesome. Question: Is there an easy way you could add the fields that are required for the calculation for “Active Work” time per incident. In other words the difference between created and resolved dates minus the time that it is in a “clock stopped” phase?

    • Brian Fahrenholtz
      Brian Fahrenholtz January 26, 2018 at 14:30 - Reply

      Hi Josh,
      It’s two differens things. One is the time between created and resolved that should be easy to add, the second “clock stopped” is based on time working on the workitem. That information is located in another tabel in the database, but can be added to this as well.

  4. Kenny March 13, 2018 at 17:15 - Reply

    Brian,
    Can you please get a query to list all the service requests?
    Thanks

    • Gerhard Goossens October 19, 2018 at 22:45 - Reply

      /****** Developed by Brian Fahrenholtz (Coretech A/S) ******/
      SELECT
      ‘1’ AS SRDimCount,
      SR.Id,
      Classification = ISNULL(AreaEnumDS.DisplayName, AreaEnum.ServiceRequestAreaValue),
      Convert(Date,SR.CompletedDate) As ‘Completed Date’,
      SR.CreatedDate,
      DATEPART(DAY,SR.CreatedDate) AS ‘Created Day’,
      DATEPART(MONTH,SR.CreatedDate) AS ‘Created Month’,
      DATEPART(YEAR,SR.CreatedDate) AS ‘Created Year’,
      DATEPART(WEEK,SR.CreatedDate) AS ‘Created Week’,
      SR.FirstResponseDate AS ‘First Response Date’,
      SR.Priority,
      SR.CompletedDate AS ‘Completed Date’,
      DATEPART(DAY,SR.CompletedDate) AS ‘Completed Day’,
      DATEPART(MONTH,SR.CompletedDate) AS ‘Completed Month’,
      DATEPART(YEAR,SR.CompletedDate) AS ‘Completed Year’,
      DATEPART(WEEK,SR.CompletedDate) AS ‘Completed Week’,
      Source = ISNULL(SourceDS.DisplayName, SourceEnum.ServiceRequestSourceValue),
      Status = ISNULL(StatusDS.DisplayName, StatusEnum.ServiceRequestStatusValue),
      SupportGroup = ISNULL(SupportGroupEnumDS.DisplayName, SupportGroupEnum.ServiceRequestSupportGroupValue)

      FROM
      DWDataMart.dbo.ServiceRequestDimvw SR

      INNER JOIN DWDAtaMart.dbo.EntityDimvw Entity
      ON SR.EntityDimKey = Entity.EntityDimKey

      INNER JOIN DWDAtaMart.dbo.WorkItemDimvw WI
      ON SR.EntityDimKey = WI.EntityDimKey

      /****** Tier Queue ******/
      Left Outer Join DWDataMart.dbo.ServiceRequestSupportGroupvw SupportGroupEnum
      ON SR.SupportGroup_ServiceRequestSupportGroupId = SupportGroupEnum.ServiceRequestSupportGroupId

      Left Outer Join DWDataMart.dbo.DisplayStringDimvw SupportGroupEnumDS
      ON SupportGroupEnum.EnumTypeId = SupportGroupEnumDS.BaseManagedEntityId
      AND SupportGroupEnumDS.LanguageCode = ‘ENU’

      /****** Source ******/
      LEFT OUTER JOIN
      DWDataMart.dbo.ServiceRequestSourcevw AS SourceEnum
      ON SourceEnum.ServiceRequestSourceId = SR.Source_ServiceRequestSourceId

      LEFT OUTER JOIN
      DWDataMart.dbo.DisplayStringDimvw SourceDS
      ON SourceEnum.EnumTypeId=SourceDS.BaseManagedEntityId
      AND SourceDS.LanguageCode = ‘ENU’

      /****** Classification ******/
      Left Outer Join DWDataMart.dbo.ServiceRequestAreavw AreaEnum
      ON SR.Area_ServiceRequestAreaId = AreaEnum.ServiceRequestAreaId

      Left Outer Join DWDataMart.dbo.DisplayStringDimvw AreaEnumDS
      ON AreaEnum.EnumTypeId = AreaEnumDS.BaseManagedEntityId
      AND AreaEnumDS.LanguageCode = ‘ENU’

      /****** Status ******/
      LEFT OUTER JOIN
      DWDataMart.dbo.ServiceRequestStatusvw AS StatusEnum
      ON StatusEnum.ServiceRequestStatusId= SR.Status_ServiceRequestStatusId

      LEFT OUTER JOIN
      DWDataMart.dbo.DisplayStringDimvw StatusDS
      ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
      AND StatusDS.LanguageCode = ‘ENU’

      Order by SR.CreatedDate

  5. Gerhard September 18, 2018 at 12:40 - Reply

    Thanks you for this.

    It will be awesome if you can share some Service Request Queries / Datasets.

Leave A Comment