How to: Get User Profile Activities from SharePoint Database

Though, it is not recommended way to query SharePoint database tables sometimes due to critical requirements we might need to extract user profile log report.
SharePoint stores the user profile update events in [UserProfileEventLog] table. If there is any profile update occured, the activity event will get saved in [ActivityEventsPublished] and [ActivityEventsConsolidated] tables.

• ActivityEventsPublished – Stores the data relevant to that particular user only. Unique record for that event.
• ActivityEventsConsolidated – Stores the data which the user shared to rest of all the users (colleagues). SharePoint adds an entry in this table about the user’s event against all his/her colleagues (say, 1:1000 | ActivityEventsPublished  : ActivityEventsConsolidated )

There is limitation using SharePoint Object model to fetch the user profile data beyond more dates. Alternatively, we can query SQL database directly and get the required data easily.
Run the following SQL query to get it done.
SELECT [EventId]    
      ,[ChangeType]
      ,[EventTime]    
      ,[ChangedPropertyId]
  FROM [Profile_DB].[dbo].[UserProfileEventLog]
  ORDER BY convert(datetime, [EventTime], 103) ASC
Note: SharePoint maintains the event log of user profile only for a limited period of time so that can restrict the database growth.

Read More