Ability to Track User's Activity History

A system administrator would like to be able to view the pages in the application that a specific Application User accessed. For example If they have an application user JaneDoe and wanted to know if she visited the Constituent record of John Smith. A system administrator can run as that user and have access to the last few pages they accessed, however older records that were accessed are not displayed there. Can a system administrator view older information that was accessed by an Application User?
The answer to this is Yes and No.  Every single database table in the system has a corresponding AUDIT table.  So the CONSTITUENT table has a corresponding CONSTITUENTAUDIT table.  This table can be reviewed to look at any CHANGES to the records, this being any Added information, Edited information, or Deleted information.  Unfortunately this does not track whether a user just visited the page or not. 
 
There is also the query view of ‘Active users’ that can be used to query on active users in the system but this also does not show where the user is in the system, just that they are logged in.
 
All that said there is one option that can be done via SQL to pull user session information regarding where exactly they are currently or have visited in the system but this would not be available in the front end of the system without a customization.  In any case the USERPAGEHISTORY table can be used to pull information specific to users sessions in CRM.  The USERPAGEHISTORY.HISTORY field can be reviewed, which is XML data related to application users individual sessions and what records they have visited. 
 
How the table works:
It may be found that duplicate/multiple entries are seen for the same user at times, which is normal, as this table will store separate rows for each different area in the system the application user visits.  So if the application user goes to some constituent records first there will be one line item/row for the constituent session. If that application user then reviews revenue records there will be a new line item/row in the table for the revenue session for that application user.
 
How the query works:
The query WHERE clause can be changed to pull whatever distinct time frame needed by changing the ‘DATEADD(HOUR, -1, GETDATE()) portion of the code.  The HOUR and the value of ‘-1’ are the values that need to be changed here.  The value of -1 in the below statement means the last 1 hour of time.  This can be changed to -5 for the past five hours, for example, if desired.  The HOUR value to MINUTE or DAY can be changed as needed as well.  The Date Changed can be used however needed, but this will give a quick way to filter on things that have changed within the past X time frame. 
 
 
select *
from userpagehistory (NOLOCK)
inner join appuser (NOLOCK) on appuser.id = userpagehistory.appuserid
where userpagehistory.DATECHANGED > DATEADD(HOUR, -1, GETDATE())
 

Environment

 Blackbaud CRM
 4.0
 4.0
 4.0.166.0

Was this article helpful?