SharePoint 2010/2013: Trim audit Logs with Powershell

In case that your storage or MSSQL filegroup is full, because  you didn't implement a proper housekeeping of your Audit logs, you can use this PowerShell command to delete them fast:


$site = Get-SPSite https://yoursite.yourdomain.com
$date = Get-Date "20/04/2015"
$site.Audit.DeleteEntries($date)

As the deletion in SQL Server LL happen on a Transaction base it might take a while. Moke sure you have enough free space on your log drive, as the log LL grow and please don't truncate table or apply any operations directly in SQL Server - SharePoint LL not be your friend anymore after such operations! ;-)

SharePoint 2010: Export and Archive Audit Logs

If you ever work with customers with a high demand on compliance and audit reporting, you will think about using SharePoint Audit Reporting. I want to share some of my experiences about this topic.

Recently one of my customers was searching for an easy configurable and maintainable solution to monitor events in some specific site collections including sensitive data. I implemented the standard SharePoint audit logging. By default you can export and view the audit data then as Excel pivot table. I implemented the following configuration parameters:

• Capture all document and item events: all flags

• Capture all lists, libraries, sites: all flags

• Audit log trimming: No

http://office.microsoft.com/en-us/sharepoint-server-help/configure-audit-settings-for-a-site-collection-HA102031737.aspx

With this initial configuration I wanted to make sure, that the audit logs are not deleted automatically. But in fact 30 days later all my audit logs were deleted by SharePoint. I did some research and detected, that a time job called "SITECOLLECTION Audit Log Trimming" deleted all the audit logs.

Even if you configured no audit log trimming, this job will delete your logs after 30 days. If you disable the job, then logs won’t be deleted anymore.

Ok, now I thought I’m cool. But then the next issue bumped up. The content database of the audit enabled site collection grew heavily. MS SQL table size report detected, that the audit data table contains 12mio records and uses about 5 GB of disk space and this were just the audit logs of 1.5 months. You can minimize the logged data amount when you don’t log all actions. For example it is mostly not necessary to log the view and file open events because this is logged in web analytics also and there is no relevant document change in this case.

These are the attributes which are logged with each audit log record:

 

Based on compliance regulations, my customer needs to keep all data for 10 years. If the database grows that fast, it will grow too large and impact the performance of SharePoint and later will become unusable one day, because of it’s size. So i decided to offload the audit log table from time to time with SSIS to another database server and archive it there.

Based on the above table structure, you can see that there’s no primary key logged in this table. So there’s no unique identifier to match the production and archive data from time to time without data loss.

 

There were two options to solve the issue. First option was a custom development to synchronize production and archive database, based on a web service or a sql trigger. The web service option was time and cost intensive and a sql trigger should not be inserted in the SharePoint database, because you‘ll lose Microsoft product support.

Finally I started search for a 3rd party solution to cover this issue and discovered Idera Audit Management which provides the following features:

· Easily identifies and alerts on security issues

· Helps with SharePoint governance and regulatory compliance

· Adds new events not available in SharePoint’s native auditing

· Safeguards SharePoint performance with an external reporting database

· Find out what’s going on in detail with out-of-the-box reports

This was exactly was I was searching for. I installed a trial version and configured the necessary things. After some days of production use the customer licensed the product and was happy with the solution provided. I'm not paid to write this article, I'm just happy to have the ability to provide real audit logging including offloading and archiving of audit databases to my customers.

Some from the ISV about Idera Audit Management:

Idera SharePoint audit is a comprehensive SharePoint auditing solution that captures permission changes as well as log on events, views, inserts, updates, deletes, and changes, all the way to the field level. SharePoint audit gives you a complete view of who is doing what and where in your SharePoint environment. It enables you to automatically turn on auditing for new SharePoint sites, configure specialized auditing at the web application or site collection level, and monitor the level of auditing across your farm. It collects the data you need in order to comply with regulations and data security requirements, such as Sarbanes Oxley (SOX) and HIPAA.

http://www.idera.com/SharePoint/SharePoint-audit/

SharePoint 2010: VairationFixUpTool

SharePoint Variations is a great feature for creating multilingual Internet/Intranet sites.

The relation between the copies of a page in her language is maintained in a hidde list, called Relationship List. Sometimes, especially when creating new variation labels or sites, entries in this list get out of sync. Microsoft provides a stsadm command to automatically fix relationships:


http://technet.microsoft.com/en-us/library/dd789658(v=office.12).aspx

SharePoint 2010: My favorite 3rd party tools

Check some of my favorite 3rd party tools. I used all of them in real life projects and can recommend them based on experience. I'm not paied for this article and i will update this list from time to time.

Administration & Management Tools

• Feature Admin Tool
• SharePoint 2010 Feature Upgrade Kit
• SharePoint Manager 2010

Document Conversion / PDF / PDFA creation

• Muhimbi Software

Workflow

• HarePoint
• Nintex Workflow

Farm Documentation

• SPDockIT

Search

• FAST Search for Sharepoint MOSS 2010 Query Tool

CSV Export

• Export ZEN

SharePoint List Print

• Print ZEN

Document Managment

• SharePoint Boost, Copy & Move Documents
• Download as ZIP

Migration

• ShareGate

Password & User Management

• SharePoint Boost, Password Reset

SharePoint 2010 - Search Suggestions: Add/Remove Terms

Search suggestions are one of the key features of SharePoint 2010 search. One lack of functionality is, that you can manage them only by PowerShell.

Overview Search Suggestions concept: http://sharepointfieldnotes.blogspot.ch/2011/09/sharepoint-2010-search-query.html

Best Script to add suggestions based on a csv file input: http://get-spscripts.com/2010/07/add-pre-query-search-suggestions-with.html

ADD Suggestions (Script from get-spscripts.com):

#Set up default variables
$csvfile="C:\Install\SearchSuggestions.csv"
$ssa = Get-SPEnterpriseSearchServiceapplication -Identity "Search Service Application" #Create Lists from each item in CSV file
$csvData = Import-Csv $csvfile
foreach ($line in $csvData)
{
New-SPEnterpriseSearchLanguageResourcePhrase -SearchApplication $ssa -Language en-US -Type QuerySuggestionAlwaysSuggest -Name $line.Suggestion
} $timerJob = Get-SPTimerJob "Prepare query suggestions"
$timerJob.RunNow()


REMOVE Suggestions (modified script from get-spscripts.com):

#Set up default variables
$csvfile="C:\Install\SearchSuggestions.csv"
$ssa = Get-SPEnterpriseSearchServiceapplication -Identity "Search Service Application" #Create Lists from each item in CSV file
$csvData = Import-Csv $csvfile
foreach ($line in $csvData)
{
     Remove-SPEnterpriseSearchLanguageResourcePhrase -SearchApplication $ssa -Language en-US -Type QuerySuggestionAlwaysSuggest -Identity $line.Suggestion
} $timerJob = Get-SPTimerJob "Prepare query suggestions"
$timerJob.RunNow()

Note: Search suggestions are added trught the Timer Job "Prepare query suggestions", no reindexing is required after modifications.

Hint: To avoid problems with special characters, please import the CSV as UTF-8. ANSI will end up in unknow special characters.

SharePoint 2010 - Metro Style Interface: Recommendation

Metro Interface already on SharePoint 2010? No problem! The guys from LifeInSharePoint created a fabulous masterpage and design solution to start your project. It is up and running in minutes, it is configurable an looks as nice as SharePoint 2013 interface and you can get it on 2010. Great!

> http://lifeinsharepoint.codeplex.com/

Most Important features:

• Liquid Layout
• Cufon Integration
• Jquery Accordion Quicklaunch with Sub Item Count
• Full Solution with single Site Feature to activate branding (Thanks CKS:Dev Team)
• Custom Site Settings Action to choose one of three colour schemes (Green, Blue & Red)

> More info: http://www.lifeinsharepoint.co.uk/2011/12/03/metro-ui-sharepoint-2010-masterpage-solution/

 

SharePoint 2010 - Feature Admin Tool: Recommendation

The Swiss army knife for feature management in SharePoint 2010!

If you need to batch manage features in your farm, the best tool I can reccomend is: http://featureadmin.codeplex.com/

• This tool allows detection and removal of broken features and feature definitions in SharePoint content databases.
• It provides a simple and clean UI
• It allows activation/deactivation/installation/uninstallation of features
• You can manage features of all scopes

SharePoint 2010 - Move Managed Metadata Databases (MMD) between Farms

Managed metadata and taxonomy is one of the cool things in SharePoint. If you are responsible for the managment of a multi farm infrastructure with development(DEV), integration(INT) and production(PRO) systems you have to provide a solution for moving managed metadata content across different farms. I tried different approches like export/import, shared service application, etc, but none of them provide a complete solutionö. Andrew Connell is the man who can help you. In his article he describes how to move the whole service database from one farm to another.

http://www.andrewconnell.com/blog/archive/2011/06/15/sharepoint-2010-managed-metadata-movingcopying-mms-instances-term.aspx

Usually taxonomy and term store is managed and maintained on production system. If your developers need a copy of the database for testing purposes, or your project manager needs a up to date integration system, you can just move the database as described in the article.

HINT: I automated the database backup/restore process for repeating actions with MS SQL SSIS. It allows you to prepare a package with all parameters and actions required for the movement.

At the end you get a "click once" deployment between farms! Quite nice..

SharePoint 2010 - Move Nintex Workflow Databases between SharePoint farms

Some months ago my project manager came to me and asked if it is possible to move a content database from production farm to development farm. Our developers needed real content to test different extensions. I answered YES, with a big smile. BUT the I recognized, that we use Nintex Workflows. If I would only copy the SharePoint content database, all Nintex workflows would be in a inconsistent state and not functioning on development/integration farm. So I stopped smiling and started searching for a solution.

First of all: Nintex published some whitepapers about this issue.

http://connect.nintex.com/files/folders/tutorials_and_how_tos_nw2010/entry20197.aspx
 http://connect.nintex.com/files/folders/technical_and_white_papers_nw2010/entry17226.aspx

Based on this inputs I created a approch to move our content and Nintex Workflow databases:

Production:

1. Backup SharePoint Content Database
2. Backup Nintex Database
3. Both have to be in Sync. No Workflow changes while Backup

Development/Integration:

1. Restore SharePoint Content Database
2. Delete existing Nintex Database
3. Restore Nintex Database
4. Mount SharePoint Database
5. Perform IIS Reset
6. Change license key to development key

 

HINT: I automated the database backup/restore process for repeating actions with MS SQL SSIS. It allows you to prepare a package with all parameters and actions required for the movement.

At the end you get a "click once" Nintex database deployment between farms! Quite nice..