SharePoint 2010: Track User Profile Changes Over Time Using MERGE

I recently blogged about a report you can write to see what users don’t have managers in SharePoint 2010 (get your Org Browser fixed!). Well, how about tracking who get’s added or deleted every day, or edited? You could probably look at the ForeFront Identity Manager that SharePoint 2010 uses and do some logging or use the API to log things, but that is.. well, harder than this.

What I wanted to do was keep logs for debugging and reporting. Who is getting added to SharePoint 2010 user profile sync? Is last user added to Active Directory a valid user, or a system account? Now, you could query Active Directory (just like finding users without managers) but I am guessing that most SharePoint Admins don’t have AD access, so they need another way.

What I did was create another database on my database server where the SharePoint content and profile databases are stored, so I can use it as a dumping ground.

What you need to do is create a table to dump the profile table and then every day do a SQL MERGE into it and save the changes. This is where it gets interesting

First, create your table (for this example I am only tracking NTName and PreferredName, so “domainusername” and “Name, User”, but you could easily add other columns.)

CREATE TABLE [dbo].[SharePointUserTracking](
	[RecordId] [bigint] NOT NULL,
	[NTName] [nvarchar](400) NULL,
	[PreferredName] [varchar](256) NULL,
 CONSTRAINT [PK_SharePointUserTracking] PRIMARY KEY CLUSTERED
(
	[RecordId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Once you have your table, you are ready to create your MERGE query, track the changes, and the get it scheduled up. So what we want to do is MERGE our UserProfile_Full data from our ProfileDB into our new table, tracking INSERT, UPDATE, and DELETE’s as well, in a tracking table. Let’s create our “tracking changes” table:

CREATE TABLE [dbo].[SharePointUserProfileDelta](
	[ExistingRecordId] [bigint] NULL,
	[ExistingNTName] [nvarchar](400) NULL,
	[ExistingPreferredName] [varchar](256) NULL,
	[ActionTaken] [nvarchar](10) NULL,
	[NewRecordId] [bigint] NULL,
	[NewNTName] [nvarchar](400) NULL,
	[NewPreferredName] [nvarchar](256) NULL,
	[LogDate] [datetime] NULL
) ON [PRIMARY]

GO

Notice the column “ActionTaken” to track what we do, INSERT, UPDATE, DELETE.

Now, for the super merge (note, the profile DB might not be named the same, also note the collation thing you need to do.. may or may not to do this in your environment)

  MERGE dbo.SharePointUserTracking AS target
    USING
            (SELECT RecordId,NTName,PreferredName FROM SP2010_ProfileDB.dbo.UserProfile_Full) AS source
            (RecordId,NTName,PreferredName) ON (target.RecordId = source.RecordId)
    WHEN MATCHED AND target.NTName  source.NTName COLLATE SQL_Latin1_General_CP1_CI_AS OR target.PreferredName  source.PreferredName COLLATE SQL_Latin1_General_CP1_CI_AS THEN
        UPDATE SET
                  NTName = source.NTName,
                  PreferredName = source.PreferredName
      WHEN NOT MATCHED THEN
          INSERT (RecordId,NTName,PreferredName)
          VALUES (source.RecordId,source.NTName, source.PreferredName)
      WHEN NOT MATCHED BY SOURCE THEN
            DELETE
      OUTPUT deleted.*, $action, inserted.*,GETDATE() INTO dbo.SharePointUserProfileDelta;

Now, you can schedule that to run after your daily user profile sync job in SharePoint and then you have a running log of changes, which you could make an SSRS report off of easily or do whatever you want with it. Pretty cool! Just know, you can use this method to track changes to any table.. slowly changing dims and even more.