SharePoint audit in action

1. SharePoint audit in action Inside the Audit Facilities for the SharePoint Platform By : George Gergues May 2014

2. George Gergues : Software Developer , SharePoint architect. • Focus : SharePoint platform, Knowledge management, Business Process Enhancement. • yet another SharePointer . • You can reach me via email at georgeDOTgerguesATgmailDOTcom • LinkedIn (semi active). Introduction

3. The need for audit log. [Business] • Why not use Versioning only. [Business] • Standard Audit Log settings. (DEMO) [IT] • Standard Audit Log functions. (DEMO) [IT] • Reading standard log through OM. [DEV] • Writing custom log entries through OM. [DEV] • Client Site Object Model Integration. [NOT POSSIBLE] • Knowing what we know - architecture points.[ARCH] Agenda

4. 21 CFR 11 Subpart B (code of federal regulation )

5. SOX – 404 Management assessment of internal controls.

6. Any respectable platform should have an audit facility (the SharePoint implementation) to show data integrity at a record level, and system level. • Regulatory compliance (main reason). • Building any validated system, that will host records. • Code for Federal Regulation (used by FDA) 21CFR part 11 subpart B , section 2-e is the FDA version. (show audit trail for records). • Sarbanes-Oxley act of 2002, (SOX) Section 404 (Microsoft link) • HIPPA , GCP, GxP . • All regulations revolve around the same concept [Tracking changes in a controlled environment]. • For SharePoint : Augment the item version history as a first level of record integrity check (see more later). • Augments the OS and database level audit if needed, the record level audit data should be visible to system users. (personal opinion). • Note: Social tags, are not part of the record (Like , Notes, Tags etc.) The Need for Audit Trail

7. Versions

8. We have Version control, why need audit trail. ??

9. We have Version control, why need audit trail. ??

10. Before After If you don’t have the modified by and modified date , on the views, you will not notice the changes. We have Version control, why need audit trail. ??

11. We have Version control, why need audit trail. ??

12. At the Item level ,there were no changes. (version history information again) • But at the List level the schema changes are visible. We have Version control, why need audit trail. ?? -> Look at audit trail for schema changes.

13. We have Version control, why need audit trail. ?? -> Look at audit trail for schema changes.

14. To the DEMOamajigger, Microsoft will not mind at all if you do !!! DEMO Time (version vs audit)

15. In this session we will discuss the core facilities (SPAudit) that come within SharePoint for Audit Trail (2.e) and show best practices and code samples. Will also discuss some architecture concerns if you are planning to use existing farms or build new ones. SharePoint Audit facilities

16. Configure site Audit (Site Collection level)

17. To view the audit logs you need to activate this feature at the site collection. If this feature is not activated, you will get a message to activate it. Configure site Audit (Site Collection level)

18. Extracting audit log data.

19. Run a custom report

20. Report (xlsx) output sample

21. To the DEMOamajigger, Microsoft will not mind at all if you do !!! DEMO Time (Configure Audit at site collection level)

22. The main pain point is the [AuditData] Table growing constantly. • No formula to predict growth rate. • The only way to measure (guesstimate) a rate, is by getting trending data over time (size of table etc.) and periodic communication with business users to see business usage trends. (that will be manifested in the number of list items.) Cost of audit (architecture points I)

23. Never (think it over) allow Audit on the home site collection (where you have a landing page). • Never (think it over) enable Audit on a public site with • Isolate the systems that require audit in own site collection • Isolate the systems that require audit in own Content Database. Cost of audit (architecture points II)

24. To the DEMOamajigger, Microsoft will not mind at all if you do !!! DEMO Time (Cost OF audit)

25. Number of rows added to the AuditData Table without the view options configured, with only one transaction (one row) useWSS_Content_AUDIT_DEMO SELECTCOUNT(SiteId)fromAuditData • Number of rows added to the AuditData Table with all audit actions options selected. We are counting the entries after one visit. (8 rows) useWSS_Content_AUDIT_PUB SELECTCOUNT(SiteId)fromAuditData

26. The Core functionality • The SPAudit query scopes [Item , List , Site(collection)] • (Web) is not available as a scope. • Event Types 2007 – 15 • SPAuditQuery • SPAuditMaskType The Sample application

28. publicenumSPAuditMaskType http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spauditmasktype(v=office.15).aspx

29. Need to include in the same section, to write a custom Event Log Entry with the impersonated User information , this way you don’t drop the initial identity. • Client Side Object Model (CSOM) not possible ,as this data cannot be exposed via CSOM. • If you don’t want to write against the server OM, you can create a WCF and can perform the same functions , and use JavaScript , or any client side code to consume it . Just to accommodate the JSON friendly folks . Considerations

30. SPSite site = SPContext.Current.Site; SPSecurity.RunWithElevatedPrivileges(delegate() { using (SPSiteeSite = newSPSite(site.ID)) { // run elevated code here // Add audit log entry here too  . } }); When performing any action using elevated privileges (elevated rights code) the user will always be the SharePoint SYSTEM account. Considerations

31. To the DEMOamajigger, Microsoft will not mind at all if you do !!! DEMO Time (SPAuditdemo – Codeplex )

32. This system was designed with the concepts from MSDN and the article by Ted Pattison. • Extended the option to allow non admins right on item level, and List / library level • Extending the option to allow non site admins access to collection audit data (with group membership). • Data visualization (using the Telerik™, RAD CONTROLS data grid, as it has many features out of the box with little scripting / configuration) , but you can use any grid view or any data visualization control you’re comfortable with . • Packaged as a solution with features scoped at collection level for admins to operate in a single place. A full application with custom actions .(DEMO ONLY)

33. Adding a dictionary for translating the events to local language (still in English). • Remove the 3rd Party dependency and use only native components. • Create a Web Only level audit by filtering the Audit level to the SPWeb ID , by Url instead of Id. • Better UI. • AutoSave the report to a site library , with periodic subscription. (daily, weekly etc.) enhancements

34. To the DEMOamajigger, Microsoft will not mind at all if you do !!! DEMO Time (FULL APPLICATION.)

35. SPAudit Demo Code for this presentation. https://spauditdemo.codeplex.com • [Ted Pattison – Writing Custom Audit Log Entries ] http://msdn.microsoft.com/en-us/library/bb466221%28v=office.12%29.aspx • [Ted Pattison – Item Level Audit ] http://msdn.microsoft.com/en-us/library/bb397403%28v=office.12%29.aspx • The Audit Scope problem – 2013 http://msdn.microsoft.com/en-us/library/ms458658%28v=office.15%29.aspx References and Links , etc.

36. ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Quizz/(tions) Comments