How to retrieve the History of work items in Service Manger

DSVella

Good morning all;

I'm working on creating a set of management reports for my director. I have been made aware of the DWDatamart DB and it has provided a lot. However I cannot find a means to query the History Tab of a given work item. The business would like to see a number of metrics from here such as Incidents that have had their priority level increase over time or Service requests that have been bounced from one person to another.

My googling around suggests that this is not an out-of-the-box solution. I would like to capture the work items history in the DW but I am unsure how to go about doing that.

As a note, I am a Business Intelligence Analyst and have very little experience with Service Manager. I am more than happy to dive into SQL but if this requires making configuration changes to SM then I may need some basic instructions.

Thanks in advance.

Adam_Dzyacky

You even said it in the other thread, but I still missed this one. So I'm replying twice for yourself and onlookers per this.

You're right this doesn't exist out of the box. The Entity Change Log table (ECL) keeps track of all the changes performed against an object in SCSM but that only exist in the operational ServiceManager DB. Given that it's transactional it's not ideal to query in the short, much less long term (i.e. where something like a DW would make more sense).

In order to achieve these kind of count based metrics, I'd advise creating a Class Extension and SCSM Workflow that simply increments a value on the Incident. In doing so, this will make its way to the DW so you can quickly just pull a value such as PriorityChangeCount or AssignedUserChangeCount. To set something like this up you can check out the following blog post.