Audit Log - Not on power app but the data written to source

[deresistance]

Hey Everyone,

    I would say a simple question but finding mixed things on this topic.    I am aware of the power app audit log that tells me when authors change the app etc.  I am not talking that audit log.   This is a custom audit log.   So I want to create an audit log of what my users change/subit/edit in my powerapp.    I would say an easy way would be to just write rows changed to SQL.

    My thought is that when I do a SubmitForm() can you have it write the changes to another data source?   Is there something built in with this type of function or is this all need to be hard coded.    Any tips would be great thanks in advance.

[ansrikanth]

Hi @deresistance 

Assuming you are talking about Canvas Apps, the correct place to log such kind of data updates is App Insights. Refer to Integrating App Insights with Power Apps to log te... - Power Platform Community (microsoft.com)  or Log telemetry for your Apps using Azure Application Insights | Microsoft Power Apps for more details. 

Thank you

-Srikanth

[cwebb365 · Accepted solution]

I Just did this with one of my apps. Stay away from Triggers. Those would have made things a lot easier, but they silently break things and your app can't write to a table with triggers out of the blue :). 

Anyway, my approach is half baked, but works and is dynamic, I just haven't created a easier to read front end for it, but for logging all changes etc. it works just fine. 

I have an AuditLog table in my database. With the following Schema. 

CREATE TABLE [dbo].[AuditLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SourceTable] [nvarchar](250) NULL,
	[OldRowData] [nvarchar](2000) NULL,
	[NewRowData] [nvarchar](2000) NULL,
	[DmlType] [varchar](10) NULL,
	[DmlTimestamp] [datetimeoffset](7) NULL,
	[DmlCreatedBy] [int] NULL,
	[Sourceid] [bigint] NULL,
 CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I basically based on which screen or "table" is being modified, it is stored in here with JSON of the record. So it has all the columns the record would have touched before and after. 

So on my Submit button I have this to log the Before State to a variable

Set(varJSONUpdate,JSON(LookUp(Tickets,id=ThisItem.id),JSONFormat.IndentFour));

Then on OnSuccess of the Form I have this to utilize the varJSONUpdate as before column, and then use the Self.LastSubmit to get the post save data in JSON format as well.

I hard code in that form what table the patch is using in case I want to report on that later. As well as if it was an "Update", "Delete" etc. based on the buttons clicked. 

Patch(AuditLog,Defaults(AuditLog),{NewRowData:JSON(Self.LastSubmit,JSONFormat.IndentFour),
DmlCreatedBy:varLoggedInUserID,DmlTimestamp:Now(),DmlType:"Update",OldRowData:varJSONUpdate,Sourceid:varSelectedEditItemID,SourceTable:"Tickets"})

This gives me JSON of Before and After, with dates, what userID is logged in made the change etc. 

I plan to build a UI around it by parsing the JSON back in and comparing fields changed and only showing those etc. But haven't gotten that far. I just needed something in that at least logged the changes that was dynamic and could tell me everything that changed and this is what I came up with. Hopefully it'll help you tackle this and get started to at least tweak it to your needs.