How to query Sitefinity Audit Trail logs in Azure Application Insights to find who changed a user's role assignments, such as identifying when and by whom an Administrator role was added or removed from a specific user account.

The Sitefinity Audit Trail module logs user role changes in Azure Application Insights when the Audit Trail module is activated and the Progress.Sitefinity.AppInsights NuGet package is installed.

The audit log entries contain two important user-related fields:

• Message.SfUserUsername or UserItemUsername — The user that is the target of the action (e.g., the user whose role was changed)
• Message.UserName — The user who performed the action

Use the following KQL query in the Azure Application Insights Logs blade to retrieve all Sitefinity Audit Trail entries:

// get Audit file entries
traces
| where customDimensions.sitefinityLogType == "Audit"
| extend Message = parse_json(message)
| extend Updates = parse_json(Message.Updates)
| order by timestamp
| project
    timestamp,
    Messages = Message.Messages,
    Event = strcat_array(Message.EventTags.$values, ", "),
    EventType = Message.EventType,
    SiteName = Message.SiteName,
    ItemType = Message.ItemType,
    ItemId = coalesce(Message.ItemId, Message.SfUserId),
    ItemTitle = Message.ItemTitle,
    ItemStatus = Message.ItemStatus,
    ItemLanguage = Message.ItemLanguage,
    ItemLifecycleStatus = Message.ItemLifecycleStatus,
    ItemUrl = Message.ItemUrl,
    UserItemUsername = Message.SfUserUsername,
    UserItemRoles = strcat_array(Message.SfUserRoles.$values, ", "),
    UserID = Message.UserID,
    UserName = Message.UserName,
    UserIP = Message.UserIP,
    UserRoles = strcat_array(Message.UserRoles.$values, ", "),
    Updates,
    Message,
    cloud_RoleName,
    cloud_RoleInstance

Append the following where conditions at the end of the query above to filter for specific scenarios:

1. Find all role assignment and unassignment events across all users:

| where Message contains "was unassigned of role" or Message contains "was assigned to role"

2. Find all role changes for a specific user account (target user):

| where Message contains "was unassigned of role" or Message contains "was assigned to role" 
| where UserItemUsername == "user@example.com"

3. Find all user-related audit events (profile updates, role changes, etc.):

| where Message.ItemType == "User"

The Messages column in the query results will display a human-readable summary of each action, for example:

"User user@example.com was unassigned of role Administrators by admin@example.com on mm/dd/yyyyy hh:mm:ss AM/PM"

• The Audit Trail module must be activated and the Progress.Sitefinity.AppInsights NuGet package must be installed for audit logs to appear in Azure Application Insights. For more information
• If a user role was modified using an API with suppressed security checks, the audit log will record the acting user as "Anonymous."
• The audit log data retention in Application Insights depends on your Azure Application Insights configuration. The default retention period is 90 days.

Progress Article(s):

1. Cloud: Audit Trail logs not available
2. Using Azure App Insights with Sitefinity

References to Other Documentation:

1. Sitefinity Documentation, Audit trail with Azure Application Insights https://www.progress.com/documentation/sitefinity-cms/audit-trail-with-azure-application-insights
2. Sitefinity Documentation, Audit trail module https://www.progress.com/documentation/sitefinity-cms/overview-audit-trail-module