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|8 Comments

About the Author:

Brian Fahrenholtz

8 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

Leave A Comment