The Incident Action Logs are not transferred to the Data Warehouse in SCSM 2010 or 2012 by default.
Therefore, when data grooming ocurrs, based on your data rentention settings, you will lose this information.
For some people, this is ok, they don’t require the incident logs for historical or reporting reasons, but if you want to keep this data read on…
It is possible to define additional facts (relationship data) and dims (class data) in the Data Warehouse for existing classes and relationships or any custom ones that you have defined. This includes those for the incident logs.
I’ve seen some posts showing how to do this, but often these only add the System.WorkItem.TroubleTicket.ActionLog class and System.WorkItem.TroubleTicketHasActionLog relationship.
There are actually 3 log classes and 3 relationship types involved (the Guids are shown in brackets):
Relationships
System.WorkItem.TroubleTicketHasActionLog (a860c62e-e675-b121-f614-e52fcbd9ef2c)
System.WorkItem.TroubleTicketHasAnalystComment (835a64cd-7d41-10eb-e5e4-365ea2efc2ea)
System.WorkItem.TroubleTicketHasUserComment (ce423786-16dd-da9c-fb7b-21ab5189e12b)
Classes
System.WorkItem.TroubleTicket.ActionLog (dbb6a632-0a7e-cef8-1fc9-405d5cd4d911)
System.WorkItem.TroubleTicket.AnalystCommentLog (f14b70f4-878c-c0e1-b5c1-06ca22d05d40)
System.WorkItem.TroubleTicket.UserCommentLog (a3d4e16f-5e8a-18ba-9198-d9815194c986)
Defining the Dimensions (Dims)
You need one Dimension defined for each class that you want to transfer to the Data Warehouse, so we need 3. Note the highlighted targets for each class:
Definining the Relationships (Facts)
We also need to define 3 fact tables to transfer the relationship data between these 3 classes and the incident class.
I have highlighted the source class (SourceType), source dimension (SourceDimension), relationship (RelationshipType) and target dimension (TargetDimension) for this fact the we previously defined. “IncidentDim” is the out-of-the-box dimension for the IncidentDimvw view.
Note that the names (ID) for the new facts and dims do not end with “vw” as Service Manager will automatically add “vw” to these. If you suffix these with “vw” you’ll end up with view names ending in “vwvw” 
To keep names consistent, you should suffix your relationship facts with “Fact” and your dimensions with “Dim”.
You’ll also need some additional references for the Data Warehouse:
There are some other bits and pieces that are needed, such as your language strings, all of this can be seen by downloading the complete Management Pack from here:
Lastly, you’ll need to seal your management pack using fastseal.exe so that it will transfer to the Data Warehouse.
Notes
- You need to wait for MPSync and then successful ETL processing before the new views will appear in the DWDataMart database.
- Only data created after you import these changes will be transferred, existing data will not appear. Therefore, you should decide very early on if you need this data or not.
- Please test this in a test environment and confirm that all class and relationship data you require is transferred to the Data Warehouse.
[...] Transferring Incident Action Logs to the Data Warehouse [...]
where to import MP on DW or Management server.
On a Management Server…
The Target attribute value is not valid. Element DWActionLogDimFactsCategory references a Target element that cannot be found.
Click on the link to “seal your management pack” and follow the bit about changing your Management Pack Public Key Token to your own one and then it should work ok.
Hello
We used you MP for Incidents to create the same for SR. But when we analyzed tables in SQL we found that ServiceRequestDim already exist in our database. We have not imported any other similar MP. Also I found in the same way ChangeReuqest and Problem. Here is screenshot http://social.technet.microsoft.com/Forums/getfile/154353
More details about this in thread http://social.technet.microsoft.com/Forums/en-US/dwreportingdashboards/thread/3cdb9c38-f20e-4df8-bb02-7899a9e64c72 in last message.
Could you help with these questions ?
Hi, I’ve updated the technet forum with an answer but for others reading this it was:
From a quick look, if you already have created a dim for “WorkItem!System.WorkItem.TroubleTicket.AnalystCommentLog” you cannot/don’t need another one for SRs, CRs, PRs, etc.
You are already transferring this data.
You just need to create the facts for the relationships to the other classes.
Good morning – Had a quick question. What table would you find the notes/comments for the change requests activities? I am looking to create a report that tells me the date created for those notes/comments to compare the date created to another report created from another source for audit purposes. My SQL DBA is looking into it but wasn’t sure if it was something you might be able to answer without taking hours to search. Any assistance would be appreciated.
Thanks
In DWDataMart, view ActivityDimVw, column Notes
Thank you for the info.
Last question:
What table or view would the date be for the notes/comments be located? I check the history for the activity and it tells me when the note has been entered. I checked the database and see the note but the date and time do not correspond to what I see in the console.
Trust me your assistance is greatly appreciated.
Sorry for the delay in replying. Is using the SDK an option. Check here:
http://scsmnz.net/c-code-snippets-for-service-manager-1/
under “Reading Manual Activity History records to determine the last modified by user”.
Thank you for the information. I need to see how I can use the code to get the data I am looking for.
We ran the SCSM for a few months with default data retention. the consultant didn’t tell us the action logs are not saved in DW; we missed a few months of Action Logs. I am trying to retrieve from database backups of operational database “ServiceManager”, can you please tell me which tables in the database “ServiceManager” contain the action logs?
Much appreciated.
Patrick these are :
I don’t have the user comments, I missed those, but you can figure it out by comparing the names above to the db views, the ones below are very similar to the ones in the xml above.
MTV_System$WorkItem$TroubleTicket$ActionLog
MTV_System$WorkItem$TroubleTicket$AnalystCommentLog
MTV_System$WorkItem$TroubleTicketHasAnalystComment
RelationshipGenericView (this joins the incident to the comments/actions using the source and target basemanagedentityid )
I have a tangential question, what, if ever, goes into the user logs? The only rows ever populated for us are Action logs and AnalystComment logs. I’ve never seen anything in the user logs, even though the AffectedUser often does submit new comments (via email) they end up in one of the 2 above,
To figure out if it’s the affected user in the reports I have to parse the first few hundred characters of the comment looking for the email and the display name of the emailer, and compare it to the display name of the affected user. This works but isn’t a great solution.
If I would like to transfer data that was created before these changes, I could delete, recreate, and re-register the data warehouse to get the action logs transferred? ( Our system is still under the default 180 day grooming settings so no data should be lost)
Hi, yes if you have no grooming you could do that but please ensure you have full backups just in case. I have also tested in the past restoring the DW fully from an old backup and all data (before grooming) is recreated.
Thanks a lot for your detailed guide. I have imported the MP tables and views has been created but still empty even I added an action comment in a new incident.
Is there any missing parts?
Hi, normally existing data is not transferred, are you getting data now? If not, please check the status of your data warehouse jobs…