MDS - Who approved a change? RRS feed

  • Question

  • Hi

    Looking at using MDS as a way of capturing user maintained config data (which currently sits in varying shaped excel documents) with the approval process being seen as a big bonus. However, don't seem to have come across a way of seeing who approved a change, only the person that made/requested the change. Assume it must be getting captured somewhere otherwise the approval process is partially useless. Ideally want to be able to get to this from the database. Been searching online but only seem to find guides on how to set up the approval process. Can anyone point me in the right direction?



    Wednesday, May 30, 2018 7:39 AM

All replies

  • There is a table for each model that ends in "_CS" to store the Changeset information. For example, model 1 would be named "mdm.tbl_1_CS". This table stores the current status as well as create and update information. There is some other useful information but no information on the approver there. 

    The Message column in "mdm.tblNotificationQueue" does contain that information. It looks like there are separate entries when the Changeset status is changed to Pending/Approved and Committed. It would be a lot easier if all of those were in the "_CS" tables.

    SELECT b.UserName AS [Approved By], 
    SUBSTRING(Message, CHARINDEX('<changeset>', Message) + LEN('<changeset>') , CHARINDEX('</changeset>',Message) - (CHARINDEX('<changeset>', Message) + LEN('<changeset>')) ) AS [Changeset] ,
    SUBSTRING(Message, CHARINDEX('<new_status>', Message) + LEN('<new_status>') , CHARINDEX('</new_status>',Message) - (CHARINDEX('<new_status>', Message) + LEN('<new_status>')) ) AS [Changeset Status] ,
    FROM mdm.tblNotificationQueue a
    JOIN mdm.tblUser b ON a.EnterUserID = b.ID
      where Message like '%<new_status>Approved</new_status>%'


    Wednesday, June 6, 2018 2:18 AM