I’ve been waiting for this one like I used to wait for Christmas as a child (or to be completely honest, like I wait for Christmas now. I’m one of those really annoying people that is desperate to put the decorations up as soon as we hit December).
When D365 HR first launched as D365 Talent, database logging wasn’t an option. In the multi-tenant world that was new to those of us with an AX background, there was a fear that database logging would result in ‘noisy neighbour’ situations, and greedy data loggers would start hogging resources in a way that would impact on other users.
It seems though that at least some of those concerns have been overcome, because database logging is now generally available via the feature management workspace.
What does that mean in English?
Sometimes changes get made in the system and you need to know when and who by. In my increasingly dim and distant SAP past, we used to have a whole transaction code for it. You could go in and check for table edits that had been made and which user account they were recorded by. It didn’t tell you why they had been made, but at least you could track that user down and interrogate them for more info. Very handy.
There has always been some capacity for this in D365 HR, if you knew how to use ‘manage changes’, ‘personnel actions’, and the table browser effectively. But accurately monitoring certain fields for all updates, or certain tables for all actions including deletions, was actually quite tricky. There were workarounds if you knew the right techies, but it wasn’t part of the standard product.
Now it is.
How do I get my hands on the magic?
Get yourself into the feature management workspace (System administration > Feature management) and find the feature named ‘Database Logging (Auditing)’. Enable it in the right hand pane. I always follow the enable click with a browser refresh, just because it makes me feel more confident that the feature is fully available to me. Once you’ve enabled, you can go and set things up.
How does it work?
First of all you need to have a good long think about what you actually need to track. Noisy neighbour situations might be apparently dealt with (or this feature wouldn’t be in the wild) but you can still create a bit of a monster if you’re not careful. You could track every single field in the database, but it would be a very very bad idea. Think about it. Every time you edit a field in a table, you’re doubling the number of changes that have to be made to the database. Not only are you making the change, you’re also writing a record of that change into the sysdatabaselog table. That table is going to get huge very quickly, so you need to give some consideration to:
- What you actually need to track (clearly not everything, so which fields and tables really matter to you)
- How long you need to keep the history for (when are you going to clear that sysdatabaselog table)
- Whether you might need to create a backup of that table periodically
Once you’ve had a good stern word with yourself and tempered a natural tendency to want to log every single thing, you’re going to go and use the database logging wizard to set up monitoring on your most important fields.
To do so, jump to System Administration > Links > Database > Database log setup.
First, unless this is the first time anyone’s accessed this page, you’re going to see a grid which shows which tables or fields are being monitored. Presumably the field you want to add isn’t already here, so click ‘New’ on the ribbon.
The database logging wizard shows up, with a nifty little progress bar to show you how many steps are included in the process (I can think of MANY other places where this progress bar could be handy, but that’s one for another time). Read the instructions and follow them by clicking Next to continue).
Now you’re going to see a screen which lists the tables and fields in the database, and you’re going to select the ones you want to track. If you’ve ever seen the old AOT in previous iterations of this product you’ll be having flashbacks here – but don’t worry, you’re not missing out if this is completely new to you.
Now, it might just be me, but I find it easier to work in technical field names rather than the plain English ones, which is the only way I feel sure I’m looking at the right thing. On that basis I always check the two boxes at the bottom to ‘show all tables’ and ‘show table names’. This expands the list and gives you first the technical name and then the plain English name of whatever table or view you’re looking at. Admittedly it looks a bit more complicated, but I actually find it easier to work with that way.
When selecting what fields you want to track, it helps to know exactly which table that field resides in. So here’s a tip that can help you identify the table location of any value. Let’s say I want to know the table that the job function field resides in (yes, I’m a sad case, and I know this by heart, but let’s pretend I haven’t spent the last seven years of my life committing the table structure of this system to memory).
- Find the field in a form
- Right click the field so that you see the ‘form information’ option pop up, and click on the form name to open the form information form (not a confusing sentence at all)
- Open up the ‘administration’ tab
- Find the ‘Control name’ option – that should be TableName_FieldName – so for job function I see HcmJobDetail_JobFunction, telling me that to track this field I need to find the table called HcmJobDetail in the list.
- This is where it helps to have checked the ‘show table names’ option in the wizard, because then the table list is sorted alphabetically by technical table name, and there’s no guesswork involved.
Now you can work your way down the list choosing which fields to monitor.
At this point I feel like it’s important to make a good practice point. Database logging shouldn’t be about shutting the stable door after the horse has bolted. Your first step to avoiding unwanted data changes in your database should be configuration of good security roles that are appropriate for your business. If you don’t want your average HR administrator to be able to edit the jobs, take that privilege out of the standard role. Don’t track every single change to the job table just in case somebody does something you asked them nicely not to, but never put anything in place to prevent them doing so. There’s always one user who doesn’t quite listen in training, presses random buttons just to see what happens, and then inadvertently causes problems. Take steps in advance to prevent that happening.
In the third step of the wizard, you’ll see a list of existing fields or tables being monitored, and any new selections you made at the previous stage. Worth mentioning here (in case you’ve spent ages trying to select a checkbox and can’t seem to get it clicked) that:
- At the table level you can track for all changes – records created, edited, or deleted, as well as renamed keys.
- At the field level you can only check for updates.
If there’s nothing in the ‘field label’ column, that’s monitoring the whole table, and you can track for everything.If there’s something in the field label column, you’re looking at a field, and so the only box you can check is the updates one.
Once you’ve selected everything you want to track (and yes, you can return and change this later if you need to add or remove something) you can hit next to move on to the last stage. This stage is just a confirmation table which has you check the number of changes you want to make to what you’re tracking. Barely even worthy of a screenshot, but I’ll throw one in anyway.
As soon as you’ve hit finish, you’ll start to notice that transactions which you’ve asked to track are hitting that sysdatabase log table. There’s nothing more to it than that – they just start queuing up. Remember I said that table would get bit quickly? It’s a little bit scary just how quickly that happens.
So how do I actually use it?
Enabling the feature adds another menu item, which doesn’t appear to actually exist in the links page (must be an oversight and will surely be fixed at some point, but for now this is true) – which is Sys Admin > Links > Inquiries > Database log. Use the search bar to get there instead (just type in ‘database log’.
The grid shown is a slightly nicer view of the sysdatabaselog table than you get purely through the table browser. You can click on the ‘history’ tab to see repeated updates to the same table, what the previous data was, what the new data is etc.
Why should I switch it on?
We’re all friends in HR, and we all know that sometimes you need to know who changed a field. Not everyone is decent and law abiding and being able to track who changed whose salary record (among other things) is a good thing. On that basis, you should definitely give some serious consideration to enabling this feature on key fields like that.