Who modified that method?

I was investigating an issue in one of our QA systems today trying to understand why it wasn’t behaving the same as the code in our development system. It didn’t take me too long to realise that someone had overridden our CUS layer code with some unexpected code in the USR layer and this was masking our CUS layer changes. I wanted to know who had made that change so that I could ask them “what the heck is that doing there?”.

Because this is a QA system, there’s no version control in place here to tell me who made the change – we simply don’t expect there to be any code changes to be made in this system, so I needed an alternative strategy. My first step was to look at the properties pane in the AOT but the ChangedBy and ChangedDate gave identical values to the CreatedBy and CreatedDate properties and I knew that this couldn’t be the actual modified date.

At this point I wondered whether the model store database might come to my rescue so I took a look in there. From previous work that I had done looking at the data model of the model store, I knew that I would need to look at the ModelElement table and maybe the ModelElementData table. Sure enough the ModelElementData table carries CreatedDateTime, ModifiedDateTime, CreatedBy and ModifiedBy fields so it looked like I was onto a solution.

I noticed that the ModelElement table carried a column called AxId which I guessed might be the AX element Id so I looked up the element Id of the object that I was interested in on the AOT property pane.

RetailTransactionTableX-AOT

You can see in the picture that on my system the table RetailTransactionTableX has ID 101212 and that it’s the static table method InsertToRegularTable that carries the USR layer modification that I’m interested in.

After a little trial and error I managed to work out a SQL query that would give me what I needed to know…

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT p.[Name] AS Element
    ,c.[Name] AS ChildNode
    ,d.[MODIFIEDDATETIME]
    ,d.[MODIFIEDBY]
    ,d.[ModelId]
    ,m.[Name] AS modelName
  FROM [AX1QA_model].[dbo].[ModelElement] p
  JOIN [AX1QA_model].[dbo].[ModelElement] c ON c.ParentHandle = p.ElementHandle 
  JOIN [AX1QA_model].[dbo].[ModelElementData] d ON d.ElementHandle = c.ElementHandle
  JOIN [AX1QA_model].[dbo].[ModelManifest] m ON m.ModelId = d.ModelId
  WHERE p.AxId = 101212 
  AND c.Name = 'InsertToRegularTable'
  ORDER BY d.LayerId

As you can see I’m feeding the AX element Id 101212 and the name of the method that I’m interested in into the WHERE clause of my query and here’s the output that I got.

ModelWhoDunnit

So now I know that I need to go and see “edward.p” and find out what he was doing modifying code directly in my QA system.

UPDATE:

I should also say that this technique can just as easily be applied to any other AOT node, not just methods. So if you want to know who, for example, changed the properties of a field on a given table or who created a specific relation then you can follow the same principles.

Setting All Dynamics AX Batch Job Status to Withhold using SQL

When restoring a Microsoft Dynamics AX 2012 production database to a test database one of the tasks required before attaching and starting an AOS is to change all of the batch jobs to a status of ‘Withhold’

This can be done with the below SQL statement, the database name needs to be updated with the name or the database to be updated.

1
2
3
-- Reset the batch job status to "Withhold" for "Waiting", "Executing" and "Ready" jobs 
UPDATE [DynamicsAX].[dbo].[BATCHJOB] SET [STATUS] = 0
WHERE [STATUS] IN (1,2,5)

Remember to change the database name “[DynamicsAX]” in the script above to match your database.