Disclaimer: The setup described in this blog post is not useful for all situations. The number of audit logs should not be large. It can be larger if archiving is in place, so your SharePoint Online list data is moved from time to time to a data store being able to act on large data sets.
Ok, having said that 🙂 here is the actual content of this blog post.
Important to an audit log is that:
- only a specific group of people are allowed to read the audit logs.
- audit logs cannot be changed.
- it must be possible to log all kinds of data.
Only a specific group of people are allowed to read the audit logs
There are certain users who will always have access, like:
- Global admins.
- SharePoint admins.
This cannot be changed.
The level of control for a business solution creator like me starts at the site (site collection) level. Here, we can make sure that only specific accounts get owner (full control) permissions. The same applies to the actual list where the audit logs are stored.
If shared accounts are used for owner permissions, care must be taken who gets access to these accounts. Storing these accounts in a well configured Azure Key Vault with password rotation can add an extra level of protection.
It is always possible for a user to see (read) the item (s)he added in a SharePoint Online list. This cannot be changed. With a list setting, it can be configured that users can only see their own items (audit logs) instead of all when given read permissions on the list. The video supporting this blog post explains how to configure this setting.
Audit records cannot be changed
Admins/Owners always have full control and will therefore be able to change or delete audit logs.
An indication that something is wrong can be if:
- an item (audit log) with a particular ID is missing.
- the value of “Created” is not the same as the value “Modified”.
- the value of “Created By” is not the same as the value “Modified By”.
Users should only be able to add audit logs (SharePoint list items). This is not possible as mentioned above. Users can always see their own items. It is possible with a custom permission level that users can only read and add items. The video supporting this blog post explains how to create this custom permission level and apply it to the audit log (SharePoint Online list).
It must be possible to log all kinds of data
A SharePoint list can contain dozens of fields of many different types. No real limitation should be expected here. It is even possible to log files by adding them as list item attachments.
The amount of audit logs is a limitation though. SharePoint Online lists can hold millions of records, but this is only suitable for an archiving situation imo. I have no clear guidelines but when I expect more than a few thousand audit logs per year without archiving, I hesitate to use a SharePoint Online list for an audit log.
Putting an index (SharePoint Online list setting) on the fields you use for filtering is very much recommended.
Conclusion
Creating an audit log is possible using a SharePoint Online list if the limitations are approved. For many of the business solutions I have created, using a SharePoint Online list would have been just fine.
Please read my blog post “The data source is either read-only or has no primary key, so the function Patch can’t write to it” for a known, but no problematic design time error related the audit log setup as described in this blog post.
A video in support of this blog post can be viewed on my YouTube channel here. The video shows
- how to create a custom permission level so people can only read/add items and use it to set security.
- how to set a list setting so people can see/edit only their own items.
- a canvas app I created with which a record is added to the audit log (SharePoint Online list) than cannot be deleted or edited by that person.