Before you start reading this, please note that although the title says auditing of team membership table, it strictly does not cover all audit scenarios. However this article will help you to track some stuffs in team membership table that would be of help in some scenarios.
Recently in my project, customer walked up to me and asked if I could find out when some users have been added to a specific team and if the additions were manual or a programmatic approach. Basically customer have team based security model and adding or removing the members of the team impact user’s access to the application. They had some undesired users who were added to a team and wanted to find out the root cause of the same.
Honestly I did not do it in any of my previous CRM implementations, but before saying anything I bought some time from the customer and gave it a shot.
Our’s is a on-premise deployment and hence I had the privilege of the looking up in the TeamMembership table in the SQL. Please note that whatever I mention below is applicable only to on-premise deployments. For online deployments, this stuff won’t work.
First of all, TeamMembership table in CRM is a N:N relationship table. So there would be not created on or modified on columns in the table.
So what else we have.
All the fields look familiar in except the VersionNumber column which is a timestamp column in the SQL server. Let us clear any doubt before you actually develop it. Timestamp does not contain the timestamp when the record got created. Rather it tracks a relative time within a database, not an actual time that can be associated with a clock. Please refer to the SQL documentation on timestamp if you need more information on the same.
So it does not store time information. Then how is it useful?
Well it gives you a pattern. I execute the below query and the screenshot shows the result we get.
For the demo, I have just queried for an arbitrary Teamid and also type casted the VersionNumber field to BIGINT so that it is in readable format.
Now can you identify some pattern in the entries? If you observe carefully, all the values in the RelativeTime column starting with 44684 seems to be created by the system almost at the same time.
Similarly the ones starting with 450295 are all created in the same time.
Obviously a not a great way to audit. But surely it can help you with something before you go ahead with some custom auditing stuff of the team membership table.
Hope this helps!