Interesting Exploits in Office 365 and SharePoint

Category Archives: PowerShell

Content Database Files –Where are you?

I have been asked several times this week where the SharePoint 2010 content database files and log files are stored by default.  Well, sort of hard to answer as everyone seems to install their SQLServer slightly differently and the locations of these files depends on how you approach the installation of SQLServer.

If you “click through” the installation, not really paying any attention and take the defaults, then your database files and log files are going to be created and stored in the default location.  This typically is C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.Choice_jpg

If we create our databases in SQL Server Management Studio, we can specify the location of the MDF files and LOG files.  However, we will have to go through some manipulation to use a pre-created content database in Central Administration – probably not the most streamlined approach.  However, the pre-creation of content databases may be the optimal solution if your organization has DBAs that don’t want the SharePoint Administrators creating databases all over the place.

So, here is a quick tip and modification that you can give to your DBA so that the data and log files go in the location that they want and you don’t have to go through down-time or other configuration steps to have the default Central Administration Create Site Collection process do what you expect it to do.

The simple SQL script below can be executed to change the default location for MDF and LOG files:

USE [master]
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultData’, REG_SZ, N’D:\SQL_Data’
EXEC xp_instance_regwrite N’HKEY

USE [master]
–Data file
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultData’, REG_SZ, N’Location’
— Log file
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultLog’, REG_SZ, N’Location’

Don’t forget that Sharepoint’s Central Administration interface nor powershell provide the ability to specify a location for the MDF and/or log files.


Pre-Processing Content for Migrating to SharePoint 2010

I have recently been working with a number of customers to establish information governance in their SharePoint 2010 environments.  The question always comes up is “how do we get content off the network shares into the SharePoint environment?”

There are a number of commercial tools that will analyze, cleanup and migrate network share content to SharePoint.  These tools come from vendors such as StoredIQ, Active Navigation, EMC Kazeon and Metalogix.

If you are going to be migrating file shares to SharePoint, regardless of the tool being used, there is a basic process that I think you should be taking. This process is broad, but could help you get started thinking about your environment. You can see the basic steps below.

You may look at that process and be thinking that it doesn’t really say anything, nor does it really help you.

As I said, there are many tools out on the market today to help in this endeavor.  These tools can help you discover all the files you are looking to migrate; they can also help you filter out files by date, specific strings, or other information that you may not want to migrate to SharePoint.

However! There are a couple tools that you can use right now that can help. They are PowerShell and Microsoft Excel! Yes, PowerShell and Excel!

Take the example of a network file share you want to migrate into SharePoint. The share is N:\team1. In the process above, the first step is to discover what files are in the file share, how old they are, what types there are etc. Of course, you could go through and manually look at them one by one. Or, you could ask the person responsible for the file share to do that.  Or you could use PowerShell to iterate through and output the results to a CSV file. Simply by running this command:

Get-Childitem –recurse \\Ndriveserver\team1 |Export-CSV c:\export.csv

This process will create a CSV file that can be opened with Excel as you can see below.

With the power of excel sorting and filtering you can scroll through and really look at a ton of data about the files. Such as Extension, Last Access Time, Last Write Time etc.

This could be a great help; however, it could also be too much. Say you only want to see all the files with exe, MSI, and MP3 extensions? You could use the same command but use the Include parameter.

Get-Childitem –recurse \\Ndriveserver\team1 -include *.exe, *.MSI, *.MP3 |Export-CSV C:\export.csv

Or, if you want to narrow down your list by excluding files you don’t care about like DOC, XLS, and PPT since these are probably more natural files to migrate.

Get-Childitem –recurse \\Ndriveserver\team1 -exclude *.doc, *.xls, *.ppt |Export-CSV C:\export.csv

At this point, congratulate yourself, you have discovery, now you can run these PowerShell commands and use Excel to sort and filter.

Now, let’s go to the next, Cleanse.  We want to remove what is often termed ROT (Redundant, Obsolete and Trash or Trivial) files. Once again there are many products that make this part of the migration or import process.

However, you can do some cleaning with PowerShell as well. When you come up with a list of files you want to get rid of such as all the MSI files in the \\Ndriveserver\team1\Marketing directory, you can use the Remove-Item to get rid of them.

Remove-item \\Ndriveserver\team -recurse1\Marketing\*.pptx

You could also do something a little more fun.

get-childitem \\Ndriveserver\team1\Marketing -include *.MSI -recurse | foreach ($_) {remove-item $_.fullname}

However remember that you want to test your process for jumping in with both feet. You can use a parameter on the PowerShell command to test before you actually remove.

get-childitem \\Ndriveserver\team1\Marketing -include *.MSI -recurse | foreach ($_) {remove-item $_.fullname -whatif}

The –Whatif command will output what the command would have done, but not do it.

Like the shot below if I was to delete all the PPTX files

Running the command with the –Whatif parameter will help you make sure you are aware of exactly what the command would delete.

So now we can identify file types, and that may be useful in the case of log files or obscure application output files; however that typically is not all the criteria that you would practically use. What if you want to enforce a policy of removing OLD files? For example, what if you wanted to remove anything that has not been modified in the past year? With the capabilities of PowerShell you can do that!

Get-Childitem N:\Marketing -recurse |Where-Object {$_.LastWriteTime.Date -le (Get-Date).Date.AddDays(-365)}|foreach {remove-item $_.fullname }

Finally, you do have another option that can take advantage of that CSV file you are creating. Once you create that export file (CSV) you can delete all the entries of files you wish to keep, thus leaving in all the ones you want to delete. Then use the Import-csv PowerShell command and use that as the input to the remove-item command as follows:

Import-CSV c:\export.csv | foreach {remove-item $_.fullname }

The result is any file listed in that CSV goes away. This is one where you definitely want to be careful and ensure that you are only listing out files you really want to get rid of!  Remember the WhatIf!

Hopefully, I’ve given you some insights how to Discover and Cleanse your network file shares in preparation of migrating them to your SharePoint environment!