Counting specific-user changes in Dataverse with Audit History

 Goal

Show how to count distinct records whose tracked columns were changed by a specific user (e.g., a Power Pages account), using Audit History only.

Why use a plugin for this

• The audit “what changed” detail lives in RetrieveAuditDetails/AttributeAuditDetail. Plugins call these messages directly, run server-side, and respect Dataverse security.

• Power Automate has no first-class audit-detail action. You’d need custom calls and would still loop per audit row, slower and harder to manage.

• A plugin exposed via a Custom Action gives a clean, reusable API.

Demo scenario
Count how many hx_demoentity records were updated by the portal user and touched any of these columns: hx_field1, hx_field2. Optional date window.

---

Minimal plugin (Custom Action)

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;      // RetrieveAuditDetails*
using Microsoft.Xrm.Sdk.Messages;      // RetrieveEntity*
using Microsoft.Xrm.Sdk.Metadata;
namespace HelloX.AuditDemo
{
    /// Custom Action: hx_CountChangedRecords(StartOn: DateTime?, EndOn: DateTime?)
    /// Output: Count (int)
    public sealed class CountChangedRecords : IPlugin
    {
        // --- Demo configuration (adjust for your org) ---
        private const string ENVVAR_TARGET_USER_FULLNAME = "hx_TargetUserFullName";
        private const string ENTITY_LOGICAL_NAME = "hx_demoentity";
        private const string ENTITY_PRIMARY_ID   = "hx_demoentityid";
        private static readonly string[] TRACKED_FIELDS = { "hx_field1", "hx_field2" };
        public void Execute(IServiceProvider serviceProvider)
        {
            var ctx     = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            var factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            var svc     = factory.CreateOrganizationService(null);
            var tracing = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
            DateTime? startOn = GetOptionalUtc(ctx, "StartOn");
            DateTime? endOn   = GetOptionalUtc(ctx, "EndOn");
            var sw = Stopwatch.StartNew();
            tracing.Trace("[HX][AuditDemo] START Corr={0} StartOn={1} EndOn={2}",
                ctx.CorrelationId,
                startOn.HasValue ? startOn.Value.ToString("o") : "null",
                endOn.HasValue   ? endOn.Value.ToString("o")   : "null");
            // Resolve the specific user by Full Name from an Environment Variable
            var fullName = GetEnvVarString(svc, ENVVAR_TARGET_USER_FULLNAME);
            if (string.IsNullOrWhiteSpace(fullName))
                throw new InvalidPluginExecutionException($"Environment variable '{ENVVAR_TARGET_USER_FULLNAME}' is empty.");
            var targetUserId = ResolveActiveUserIdByFullName(svc, fullName);
            if (targetUserId == Guid.Empty)
                throw new InvalidPluginExecutionException($"User '{fullName}' not found or disabled.");
            // Count distinct records touched by that user on tracked fields
            var count = CountDistinctChangedRecords(svc, tracing, targetUserId, ENTITY_LOGICAL_NAME, TRACKED_FIELDS, startOn, endOn);
            ctx.OutputParameters["Count"] = count;
            sw.Stop();
            tracing.Trace("[HX][AuditDemo] END DistinctChangedRecords={0} Ms={1}", count, sw.ElapsedMilliseconds);
        }
        private static int CountDistinctChangedRecords(IOrganizationService svc, ITracingService tracing,
                                                       Guid userId, string entityLogicalName, string[] tracked,
                                                       DateTime? startOn, DateTime? endOn)
        {
            var recordIds = new HashSet<Guid>();
            int otc = GetTypeCode(svc, entityLogicalName);
            var q = new QueryExpression("audit")
            {
                ColumnSet = new ColumnSet("auditid", "objectid", "objecttypecode", "operation", "createdon", "userid"),
                NoLock = true,
                PageInfo = new PagingInfo { PageNumber = 1, Count = 5000 }
            };
            q.Criteria.AddCondition("objecttypecode", ConditionOperator.Equal, otc);
            q.Criteria.AddCondition("operation", ConditionOperator.Equal, 2); // Update
            q.Criteria.AddCondition("userid", ConditionOperator.Equal, userId);
            if (startOn.HasValue) q.Criteria.AddCondition("createdon", ConditionOperator.OnOrAfter, startOn.Value);
            if (endOn.HasValue)   q.Criteria.AddCondition("createdon", ConditionOperator.OnOrBefore, endOn.Value);
            int pages = 0, details = 0;
            while (true)
            {
                pages++;
                var page = svc.RetrieveMultiple(q);
                foreach (var a in page.Entities)
                {
                    try
                    {
                        var resp = (RetrieveAuditDetailsResponse)svc.Execute(new RetrieveAuditDetailsRequest { AuditId = a.Id });
                        var ad = resp.AuditDetail as AttributeAuditDetail;
                        if (ad == null) continue;
                        if (!TouchesTracked(ad, tracked)) continue; // presence-only
                        EntityReference objRef;
                        if (TryGet(ad.AuditRecord, "objectid", out objRef) && objRef != null && objRef.Id != Guid.Empty)
                        {
                            details++;
                            recordIds.Add(objRef.Id);
                        }
                    }
                    catch
                    {
                        // ignore individual detail failures; keep scanning
                    }
                }
                if (!page.MoreRecords) break;
                q.PageInfo.PageNumber++;
                q.PageInfo.PagingCookie = page.PagingCookie;
            }
            tracing.Trace("[HX][AuditDemo] AuditPages={0} DetailHits={1} DistinctRecords={2}", pages, details, recordIds.Count);
            return recordIds.Count;
        }
        // Presence-only: was any tracked field in the change set?
        private static bool TouchesTracked(AttributeAuditDetail ad, string[] tracked)
        {
            if (tracked == null || tracked.Length == 0) return false;
            var newAttrs = ad.NewValue != null ? ad.NewValue.Attributes : null;
            var oldAttrs = ad.OldValue != null ? ad.OldValue.Attributes : null;
            for (int i = 0; i < tracked.Length; i++)
            {
                var a = tracked[i];
                if ((newAttrs != null && newAttrs.Contains(a)) ||
                    (oldAttrs != null && oldAttrs.Contains(a)))
                    return true;
            }
            return false;
        }
        private static int GetTypeCode(IOrganizationService svc, string logicalName)
        {
            var req = new RetrieveEntityRequest { LogicalName = logicalName, EntityFilters = EntityFilters.Entity };
            var resp = (RetrieveEntityResponse)svc.Execute(req);
            return resp.EntityMetadata.ObjectTypeCode ?? 0;
        }
        private static DateTime? GetOptionalUtc(IPluginExecutionContext ctx, string name)
        {
            if (!ctx.InputParameters.Contains(name)) return null;
            var v = ctx.InputParameters[name];
            if (v == null) return null;
            if (v is DateTime dt) return dt == DateTime.MinValue ? (DateTime?)null : (dt.Kind == DateTimeKind.Utc ? dt : dt.ToUniversalTime());
            if (v is string s && !string.IsNullOrWhiteSpace(s))
            {
                DateTime parsed;
                if (DateTime.TryParse(s, CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind, out parsed))
                    return parsed.Kind == DateTimeKind.Utc ? parsed : parsed.ToUniversalTime();
            }
            return null;
        }
        private static string GetEnvVarString(IOrganizationService svc, string schemaName)
        {
            var defQ = new QueryExpression("environmentvariabledefinition")
            { ColumnSet = new ColumnSet("environmentvariabledefinitionid", "schemaname", "defaultvalue") };
            defQ.Criteria.AddCondition("schemaname", ConditionOperator.Equal, schemaName);
            var defRes = svc.RetrieveMultiple(defQ);
            if (defRes.Entities.Count == 0) return null;
            var def = defRes.Entities[0];
            var valQ = new QueryExpression("environmentvariablevalue")
            { ColumnSet = new ColumnSet("value"), TopCount = 1 };
            valQ.Criteria.AddCondition("environmentvariabledefinitionid", ConditionOperator.Equal, def.Id);
            var valRes = svc.RetrieveMultiple(valQ);
            var v = valRes.Entities.Count > 0 ? valRes.Entities[0].GetAttributeValue<string>("value") : null;
            return string.IsNullOrWhiteSpace(v) ? def.GetAttributeValue<string>("defaultvalue") : v;
        }
        private static Guid ResolveActiveUserIdByFullName(IOrganizationService svc, string fullname)
        {
            var q = new QueryExpression("systemuser")
            {
                ColumnSet = new ColumnSet("systemuserid", "fullname", "isdisabled"),
                TopCount = 5, NoLock = true
            };
            q.Criteria.AddCondition("fullname", ConditionOperator.Equal, fullname);
            q.Criteria.AddCondition("isdisabled", ConditionOperator.Equal, false);
            var res = svc.RetrieveMultiple(q);
            return res.Entities.Count == 0 ? Guid.Empty : res.Entities[0].Id;
        }
        private static bool TryGet<T>(Entity e, string attr, out T value)
        {
            object v;
            if (e.Attributes.TryGetValue(attr, out v) && v is T) { value = (T)v; return true; }
            value = default(T); return false;
        }
    }
}

What it does

• Filters audit by entity type, Update events, specific user, optional time window.

• For each audit row, pulls AttributeAuditDetail and checks if any tracked field was in the change set (presence-only; values not compared).

• Adds objectid to a HashSet<Guid> and returns its size.

---

Setup checklist

1. Enable auditing on hx_demoentity and on hx_field1, hx_field2.

2. Create Environment Variable hx_TargetUserFullName with the portal account’s Full Name.

3. Register plugin on a Custom Action hx_CountChangedRecords with outputs Count.

4. Call the action with optional UTC StartOn and EndOn.

---

Performance notes

• Each audit row requires a RetrieveAuditDetails call. Large windows or heavy users can produce many rows.

• For demos and small windows this is fine. For production scale:

  • Run the action asynchronously or offload to a worker (Azure Function via webhook).

  • Keep time windows tight.

  • Consider writing a lightweight “change marker” on the target entity in a separate plugin when portal saves occur, so counting later is a single query.

---

Variations

• Track multiple entities: repeat the loop over a SourceDef[] list.

• Track different fields per entity.

• Return both count and the list of IDs for downstream reporting.