ServiceDesk

I've been trying to work out where and how the history for tickets in Service Desk is held. This is because I want to be quickly and easily determine at any point in time the complete data set relating to the ticket - e.g. status, classification, priority, type, assigned to, last modified by, last comment, what was the last set of changes etc.

It's probably dead easy, but the database schema is not immediately clear! If anyone has any pointers I'd be grateful... I've searched the KB and Forums but not found much related to this.

This is an interesting idea. I may be off base, but I believe there isn't going to be a good answer here, at least not entirely.

The status at a particular time should be stored in the ReportProcessStatusHistory table. You should be able to work backward from your desired date (i.e., "SELECT TOP 1 ... WHERE DatePosted < ... ORDER BY DatePosted DESC") to get the right status from the Status column.

Comments are also easy as they are simply stored by date in the ReportProcessComment table. If I recall user created comments have a ProcessViewMessage column value of 1. ProcessViewMessage values of 0 are generally things created by the system (or workflows therein). This is important though, because some of the 0 value rows are going to be the only place you can get the incident details from a point in time, I think.

The ImIncidentTicket table doesn't store any historical data. When incident details are editing, changes to things like classification, close code, affected user, incident name and description, etc. get overwritten with the new values. The old values and the new are compiled into a "Incident details changed" comment that gets put into the ReportProcessComment table. You'd have to parse the text of the comment in order to get the values that a ticket had at any given point in time. Of course, if the details never changed, then you won't find such an entry on this table.

Thanks Michael,

Thats a great help... It's the sort of thing that I've regulalry needed when implementing Service Management solutions over the years - for rules and reporting purposes, such as if something has gone from this value to another value then do something or include it in this report...

Steve

Yup, it seems like it would come in handy for that sort of thing. And you should be able to get it here, you'll just have to dig a bit through that one column.