Advertisements

Auditing Team Membership table in Dynamics CRM

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.

image

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.

image

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!

Advertisements

About Debajit
I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: