SCSM – Incidents History SLA Reporting Query

Dujon Walsham • 6 November 2018

Retrieve all incident log data

In order to obtain the details of your Incidents History including the following information;

·Incident Names

·Created By

·Assigned To

·Resolved By

·Created Date

·Assigned to Date

·Resolved By Date

·Billable Time Users

·Billable Time


These are within several tables within your Data Warehouse databases, mainly within the DWDataMart and DWRepository databases.

Below is a SQL Query which is able to obtain this information


cSelect distinct

dbo.IncidentDim.Id,

dbo.IncidentDim.Title,

dbo.IncidentDim.DisplayName,

dbo.IncidentDim.Description,

dbo.IncidentDim.CreatedDate,

dbo.IncidentDim.FirstAssignedDate,

dbo.IncidentDim.TargetResolutionTime,

Resolved.DisplayName As ResolvedByUser,

dbo.IncidentDim.ResolvedDate As ResolvedByDate,

Analyst.DisplayName As Analyst,

AssignedTo.DisplayName As AssignedToUser,

dbo.WorkItemAssignedToUserFactvw.CreatedDate As AssignedToDate,

CreatedBy.DisplayName As CreatedByUser,

AffectedUser.DisplayName As AffectedUser,

dbo.BillableTimeDimvw.TimeInMinutes,

dbo.BillableTimeDimvw.LastUpdated,

dbo.SLAInstanceInformationFactvw.StartDate As SLAStartDate,

dbo.SLAInstanceInformationFactvw.TargetEndDate As SLAEndDate,

dbo.SLAInstanceInformationFactvw.IsBreached As SLAIsBreached

From dbo.IncidentDim

LEFT OUTER JOIN dbo.IncidentResolvedByUserFactvw

ON dbo.IncidentDim.IncidentDimKey = dbo.IncidentResolvedByUserFactvw.IncidentDimKey

LEFT OUTER JOIN dbo.UserDim

ON IncidentResolvedByUserFactvw.TroubleTicketResolvedByUser_UserDimKey = dbo.UserDim.UserDimKey

LEFT OUTER JOIN dbo.WorkItemDim

ON dbo.IncidentDim.EntityDimKey = dbo.WorkItemDim.EntityDimKey

LEFT OUTER JOIN dbo.WorkItemHasBillableTimeFactvw

ON dbo.WorkItemDim.WorkItemDimKey = dbo.WorkItemHasBillableTimeFactvw.WorkItemDimKey

LEFT OUTER JOIN dbo.BillableTimeDimvw

ON dbo.WorkItemHasBillableTimeFactvw.WorkItemHasBillableTime_BillableTimeDimKey = dbo.BillableTimeDimvw.BillableTimeDimKey

LEFT OUTER JOIN dbo.BillableTimeHasWorkingUserFactvw

ON dbo.BillableTimeDimvw.BillableTimeDimKey = dbo.BillableTimeHasWorkingUserFactvw.BillableTimeDimKey

LEFT OUTER JOIN dbo.SLAInstanceInformationFactvw

ON dbo.WorkItemDim.WorkItemDimKey = dbo.SLAInstanceInformationFactvw.WorkItemDimKey

LEFT OUTER JOIN WorkItemAssignedToUserFactvw

ON dbo.WorkItemDim.WorkItemDimKey = WorkItemAssignedToUserFactvw.WorkItemDimKey

LEFT OUTER JOIN dbo.WorkItemCreatedByUserFactvw

ON dbo.WorkItemDim.WorkItemDimKey = dbo.WorkItemCreatedByUserFactvw.WorkItemDimKey

LEFT OUTER JOIN dbo.WorkItemAffectedUserFactvw

ON dbo.workitemdim.WorkItemDimKey = dbo.WorkItemAffectedUserFactvw.WorkItemDimKey

LEFT OUTER JOIN dbo.UserDim As Resolved ON dbo.IncidentResolvedByUserFactvw.TroubleTicketResolvedByUser_UserDimKey = Resolved.UserDimKey

LEFT OUTER JOIN dbo.userdim As Analyst ON dbo.BillableTimeHasWorkingUserFactvw.BillableTimeHasWorkingUser_UserDimKey = Analyst.UserDimKey

LEFT OUTER JOIN dbo.UserDim As AssignedTo ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey

LEFT OUTER JOIN dbo.UserDim As CreatedBy ON dbo.WorkItemCreatedByUserFactvw.WorkItemCreatedByUser_UserDimKey = CreatedBy.UserDimKey

LEFT OUTER JOIN dbo.userdim As AffectedUser On dbo.WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey

Order By Id, LastUpdated

This will allow you to obtain all of the historical data of your Incidents so you can also see all of the users it went to when assigned to a new user and also for how long they worked on it for.

This can be added to a report in Report Builder, if shown any duplicate results the grouping in the Report Builder will resolve this.

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side Part 4 - SQL Server Requirement Analysis

by D Walsham • 13 December 2021
Looking through the current SQL Server topology and how it affects our decision

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side Part 3 - Current Branch Upgrade Approaches

by D Walsham • 19 November 2021
Impact of Current Branch Upgrades

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side - Part 2 - Operating System Considerations

by D Walsham • 29 October 2021
Introduction

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side

by D Walsham • 7 October 2021
Introduction

Endpoint Manager: Device Estate Cleanup

by D Walsham • 6 October 2021
Introduction

Modern Workplace Management: Creating Applications Common Practices - LOB vs Win32 Package

by D Walsham • 3 September 2021
Introduction

Modern Workplace Management: Build Endpoints Without SCCM Part 6

by D Walsham • 12 August 2021
All the parts of the series we went into great detail about how we analyse an end to end solution and how we would design a solution in which would allow us to build endpoints without SCCM being a dependency. Whilst we did this, there is another scenario which we have not touched on yet, which is the hybrid scenarios. In a perfect world ideally you would have your Azure Active Directory within the cloud, every machine meets the recommended requirements for Windows 10, everything is imported into Intune/Autopilot and everyone is happy. But we know this isn't realistic in all cases. Many organisations cannot just simply up and go from on-premise into the cloud therefore the checkpoint here is of course getting into hybrid solutions such as; Co-Management Between Intune and SCCM Hybrid AD with Azure AD and On-Premise AD syncing together These things can play a very interesting part in how you would tackle this if you envisage the next step in the blueprint is to be in a position in which you can build and manage endpoints soley within Intune. With this final part of the series we will go in-depth in how the common hybrid setups look like and how we go about moving into the next step of being able to manage and build devices without SCCM.

Modern Workplace Management: Build Endpoints without SCCM Part 5

by D Walsham • 29 July 2021
In continuation from the previous part where we had discussed how we create the "on site" piece of the solution, this was the part which would allow us to get our endpoints into a state in which they would essentially be ready to go through the Autopilot process. Which leaves our next piece of the puzzle, to begin the configuration of the actual backend side that resides within our Endpoint Management console. And you will see how everything ties up together to satisfy the full end to end process of getting an unknown (or known) device to proceed thorough the whole workflow to be finally managed by Intune without the aid of SCCM taking part in any of the prerequisites or preparation at hand.

Modern Workplace Management: Build Endpoints without SCCM Part 4

by D Walsham • 15 July 2021
In this part we are now going to look into the technical step by step points on how we put everything together. In the previous part we spoke about the structure of how we would asses whether a machine was actually ready to be built with Autopilot or not with a build checklist process which would step through all areas which would cover an endpoints eligibility. Now with everything planned out we finally want to step into making things reality by putting everything together.

Endpoint Manager: Find machines not in Autopilot

by D Walsham • 2 July 2021
When it comes to managing your endpoints in endpoint manager, one of the things you may be looking to do is to get all of your Intune registered machines to also be enrolled as Autopilot devices. Now we can of course just have the deployment profile deployed to all machines and then hit the "Convert targeted machines to autopilot" but this might not necessarily be feasible for every client. We may want to perform some due diligence first so we can at least understand what devices in Intune are not in Autopilot.
Show More