SSRS Data Warehouse report that shows LastModifiedBy user

Aaron_Boock

My Incident Management process owner requires an SSRS report with some Incident properties.  There is only one I cannot deliver, Last Modified By user. 

DWDataMart EntityDimvw contains a LastModifiedBy GUID, but apparently it can only be looked up against tables in the ServiceManager operational database (see link: https://social.technet.microsoft.com/Forums/systemcenter/en-US/0caa815d-5d42-4760-aaba-7251fb090ebd/how-do-i-get-the-lastmodifiedby-value-from-the-scsm-data-warehouse?forum=dwreportingdashboards)

Has anyone solved this differently?  OR Has anyone successfully created custom dimension etc?

Appreciate any and all discussion.

Conner_Wood

SCSM DOES NOT have a "Last Modified By User" relationship, so you'd have to create a custom MP for adding that relationship in.  By your requirements you could either add it to System.Entity or System.WorkItem.  The deciding factor is would they ever request who last modified something besides a WorkItem, such as a Config Item.

You would then have to seal that management pack and import it into SCSM.  Since the point of the relationship is to be automatically keep track of who last modified the ticket you'd need to also create a workflow to trigger on an update from an instance of that class.  What logic you decide to determine the "Last Modified By" User is up to you though.  If you really wanted to follow the supported code SDK method, you could query the history for that ticket from the ServiceManager database and then you would find the last entry and ensure the entry was not a service account like the SCSM workflow account (so you don't create an infinite loop of updating the ticket relationship over and over).  Another way is basically doing the same thing but directly using SQL itself.  I don't have an exact query of what you would need, but let's break it down:

Tables:
CredentialManagerSecureStorage - Contains the SCSM Run As Accounts you need to ensure it's not.  Contains Domain and Username columns.
BaseManagedEntity - All objects in SCSM
EntityChangeLog - History Log Entries
EntityTransactionLog - History Log Entry Details => ContextGenerated column {DOMAIN\USERNAME}
Relationship - holds all relationships
RelationshipType - defines what relationship type for a relationship ex. RelationshipTypeName = 'System.WorkItemAssignedToUser'
MT_System$Domain$User - Contains UserName and Domain columns which then allows getting the correct GUID (BaseManagedEntityId) so you could then call the supported SDK and having both Ticket ID and a valid User ID that is not an SCSM Run As Account, and then remove the old relationship and add the data as the new relationship and commit it to SCSM (it's important the task is completed by a service account).  The reason you would remove and add the relationship again even if it's the same user is so you would have a record in the Data Warehouse of each time a user modified something.

Once you have that relationship working automatically and as intended (hahaha, still reading this are ya?).

You would then have to create a custom DW MP that would reference your custom sealed MP containing the relationship so you can create the additional Relationship Fact in order for SCSM to start archiving that relationship that you would have automatically updating.

In this instance, you would have to reference the System Center Data Warehouse Base Library (DWBase) which stores the UserDim, EntityDim, and WorkItemDim (it also shows how it defined its Relationship Facts, such as WorkItemAffectedUserFact)

<RelationshipFact ID="WorkItemAffectedUserFact" Accessibility="Public" Domain="Domain.InstanceManagement" TimeGrain="Daily" SourceType="WorkItem!System.WorkItem" SourceDimension="WorkItemDim">
  <Relationships RelationshipType="WorkItem!System.WorkItemAffectedUser" TargetDimension="UserDim" />
</RelationshipFact>

If you chose to create the Relationship Fact using EntityDim, you'd also have to make a "System" reference in your custom MP to "System.Library" and you would then reference EntityDim from the DWBase reference: SourceDimension="DWBase!EntityDim"

If you chose to create the Relationship Fact using WorkItemDim, you'd also have to make a "WorkItem" reference in your custom MP to "System.WorkItem.Library" and you would then reference WorkItemDim from the DWBase reference: SourceDimension="DWBase!WorkItemDim"

You would then reference UserDim from the DWBase reference: TargetDimension="DWBase!UserDim"

If you manage to get to this point, you would then seal the MP using fast seal as the xml won't contain the snk key data ( Categories => Category => ManagementPackPublicKeyToken ) because we are not defining SCSM classes, only DW functionality which is the way MS did it on their DW MPs.

Once imported the custom DW MP through the SCSM Console, you would run MPSyncJob to get it to generate the tables and logic to sync your custom relationship.

Now, you might be asking yourself.... WHY DIDN'T MICROSOFT HAVE THIS IN THE FIRST PLACE.
   I like to think if Microsoft responded, it would be with the most villainous response possible: WHY NOT!  

NathanH

I know it's been a while, but I figured I'd post to thank Connor for his post and pass on what I learned.  I wrote the query below for a report I needed, however I'm very new to the SCSM database and haven't validated my query with anything but my test environment.  It might help get you started though.  Note that in my where statement I had to filter out the NT Authority\Local System account.  I think that's because I'm using the local system account as the workflow account and/or the Operational Database Account in my lab (purely out of laziness).

Query:

USE DWDataMart --insert your DB name here if it's different

SELECT DISTINCT

  INC.ID AS 'Incident ID'

 ,INC.Title AS 'Incident Title'

 ,ENT.LastModified AS 'Last Modified Date'

 ,REPLACE(INC.Status, 'IncidentStatusEnum.', '') AS 'Incident Status'

 ,ETL.ContextGenerated AS 'Last Modified By'

 ,INC.Priority

 ,REPLACE(INC.TierQueue, 'IncidentTierQueuesEnum.', '') AS 'Support Group'

 ,DATEDIFF(HOUR, ENT.LastModified, GETUTCDATE())-1 AS 'Hours'

FROM IncidentDimvw INC

JOIN EntityDimvw ENT ON INC.EntityDimKey = ENT.EntityDimKey

JOIN [ServiceManager].[dbo].[EntityChangeLog] EC ON ENT.BaseManagedEntityId = EC.EntityId

JOIN [ServiceManager].[dbo].[EntityTransactionLog] ETL ON ENT.LastModifiedBy = ETL.DiscoverySourceId

JOIN [ServiceManager].[dbo].[EntityChangeLog] ECL ON ETL.EntityTransactionLogId = ECL.EntityChangeLogId

WHERE ETL.ContextGenerated NOT IN ('NT AUTHORITY\SYSTEM')