** IMPORTANT UPDATE #2 -this is no longer needed.

As of September 2015, Site Quotas are no longer needed!

The screenshot below came from our tenant settings screen – Set it to Automatic and forget about site quotas!

** IMPORTANT UPDATE RELATED TO SHAREPOINT / OFFICE 365 VIDEO SITES ** (Updated April 29th 2015)

If you use the script below, you may want to put in a filter to prevent the script from working on any site with the managed path of /portals/

Explanation:

Microsoft recently added Video portals to office 365.

Ours was added this morning.

I looked at it in the admin screen because I was curious what site collections had been created.

I noticed something peculiar: The Storage Quota was 0.

You can’t set a quota to zero. Not in the UI, not in PowerShell.

Well tonight my script ran and guess what, it reset my quote on the Video portal.

Originally, Video portal storage was supposed to come out of the overall SPOnline allotment, so this may not be a big deal, maybe we needed to set it anyways?

But, since it’s not possible to set zero manually, I wonder if this was one site you didn’t have to manage/pre-allocate storage too? Or maybe MS decided to make storage unlimited?

I’m not really sure, but in the short term, I thought I’d best warn people that there are some unknowns here as it relates to using the below script with Office Video.

AND NOW BACK TO OUR ORIGINAL ARTICLE, as it was published on April 15th:

The year is 2015, You’ve just been given “TONS” of storage on SharePoint online, and someone says “Lets give everyone a 200GB site quota”.

Seems like a great idea, you’ll never run out of space, so why not set the limit high?

Well, it turns out, the word “Quota” has a different meaning in SharePoint Online / Office 365 than it did in SP on Premise.

In the On Premise version of SharePoint, the quota was a limit.
In SharePoint Online, it’s an Allocation.

What’s the difference you ask?

Say you have a 1000GB of storage on SharePoint on premise.

With On premise SharePoint, you can allocate a 200gb quota to 10 sites, “over committing” what you actually have. It works because space isn’t ‘reserved’ for that site, it’s just a limit. You’re telling on premise sharepoint “Don’t let any site get bigger than 200gb”.

Take a similar situation on SharePoint Online:

Say you have 1000GB of storage on SPO

You can only allocate 200GB quotas 5 times – each time you do, your total available drops by 200GB so by the 5th one, you have nothing left to give. This is true, even if the sites are empty!

So SharePoint online works a little differently, at least in 2015 it does – maybe one day this article won’t be relevant, but it is today.

What are we to do if we want to give users basically unlimited sized site collections

Now the question: What are we to do if we want to give users basically unlimited sized site collections, but we can’t allocate large numbers to EACH site collection?

Well, here’s what I did – I wrote a script that looks at how much storage each site collection is using, then adjusts it so there is a certain amount of ‘headroom’.

I run the script daily via a scheduled task.

I also have another script that sets up the connection to SharePoint Online which runs first, if you need that part, it’s elsewhere on this site.

The logic is fairly straightforward, but lets do an example:

All sites should be 4GB or more over the size used:

• An empty site would have 4gb allocated
• A site using 3gb would have 7gb allocated
• A site using 10gb would have 14gb allocated.

Make sense?

That logic is pretty simple, take the size of the site, add 4gb to it, that’s it’s allocation.

For performance reasons, it would be great if we weren’t constantly adjusting each and every site, every time we run the script, the $slack setting helps with that.

Here’s the script:

(If you need help scheduling a task, I have a blog post and video here about that. )

write-host "Be sure to connect to SPO first" -foregroundcolor yellow

$headroom = 4000;
$slack = 500;

#this could really be one line, but it's broken out into two for readability
#first, we get all the sites that we need to change the quota for.
# we do this by the formula Site size must be > the quota, minus the headroom
# now if we just did this, we'd likely have to increase every site every time. so we also factor in a slack amount.
#if the desired headroom is say 4000, we'll allow the site to come within 3500 of being full, then make an adjustment to 
# ensure we have 4000 free.

#this first line gets the list of sites we need to work with
$sites = get-sposite -detailed | where {$_.StorageUsageCurrent -gt $($_.StorageQuota - $headroom + $slack)}

#this lets us see what we're doing...
write-host "$($sites.count) sites to work on"
foreach ($site in $sites)
{
 write-host "$($site.url) : $($site.StorageUsageCurrent) of $($site.StorageQuota)"
}

#This line allocates the storage quota, based on the storage in use
#this was timing out
#$sites | set-sposite -StorageQuota $($_.StorageUsageCurrent + $headroom)
foreach ($site in $sites)
{
 $newquota = $site.StorageUsageCurrent + $headroom
 write-host "altering: $($Site.url) used: $($site.StorageUsageCurrent), old Quota: $($site.StorageQuota), new Quota: $newquota"
 $site | set-sposite -StorageQuota $newquota
}


write-host "Done"

– Jack

Adding SharePoint licenses to Office 365 can be a bit tricky.

If you add the E3 license, you get EVERYTHING that comes with E3, if that’s what you need, great, but what if you ONLY want SharePoint, and not Lync, email, etc??

I ran into this recently and used a few resources to come up with a script.

This article was really helpful:  http://www.powershellmagazine.com/2012/04/23/provisioning-and-licensing-office-365-accounts-with-powershell/

As was some script work by an awesome guy I work with named Chris.

The tricky thing here is you can’t directly grant just a SharePoint license in MSOL E3…

You have to do it subtractively.

Let me explain…..

Say you have 3 letters, A, B & C

You might expect to add a license for b like this:

Add-license -option B

It doesn’t work that way. (At least not in 2015 when I wrote this)

Instead you have to say:

Add-License -disable A C

No problem you say.

“I’ll just add code to disable A C”

That’s great, until….

Microsoft adds Option D

Now, when you try

Add-License -disable A C

You’ve just assigned a B and D license, when you only wanted to assign a B license.

Now you see the issue….

The solution is not too hard – we can pull a list of all options available, remove the one we want, and then build the disable list from that.

This way we won’t get caught when Microsoft springs options EFGHI on us.

The full script is below.

Note: there are some unused functions in the script for setting a password – if you have brand new users to Office 365, they may never have used their identity and will need the password reset and sent to them, if that’s the case, just add the call to Reset-Password -upn $upn at the appropriate place(s)

Here’s the script:

#http://sharepointjack.com
#based on content from http://www.powershellmagazine.com/2012/04/23/provisioning-and-licensing-office-365-accounts-with-powershell/
#use at your own risk, while this has worked for me, you should always test in a test environment and understand what it is the script is doing before using it.



write-host "Don't forget to connect to MSOL first!" -foregroundcolor yellow
Start-transcript 
function main()
{
    #variables used in the program
    $script:MissingUsers = @()
	$script:SMTPServer = "YOUR.SMTP.ADDRESS.COM"
	$importFilename = "_SPOUserList.txt"
	$LicenseType = "TENANTNAME:ENTERPRISEPACK"
	$SubLicense = "SHAREPOINTENTERPRISE"

	$path = $(get-location).path
	$userlist = get-content -path "$path\$importfilename"

	foreach ($upn in $userlist)
	{
	    	$upn = $upn.trim()
		
		#note the continue statement on next line, this skips the rest of the loop if the user is not found.
		if ((Check-UserExists -upn $upn) -ne "YES") {write-host "skipping $upn" -foregroundcolor black -backgroundcolor yellow; continue}
	    if ((CheckUserHasPrimaryLicense -upn $upn -PrimaryLicense $LicenseType) -eq $true)
		{
			#user has E3 license
			Write-host "User $upn has $LicenseType License, adding $SubLicense SubLicense"
			Add-SubLicense -upn $upn -PrimaryLicense $LicenseType -SubLicense $SubLicense
		} else {
		    #user has no license of any kind, but is still provisioned in MSOL
			write-host "User $upn does not have a license for $LicenseType adding now"
			Assign-NewLicense -upn $upn -Location "US" -PrimaryLicense $LicenseType -SubLicense $SubLicense
			
		}	
  #note, if you need to reset the users password and email that to them, add a line such as:
  # Reset-Password -upn $upn
	}	
    
	Report-MissingUsers   #report the names of any missing users so they can be investigated	
}#end main

##---------------------------------------------------------------------
##  Utility Functions from here down
##---------------------------------------------------------------------


##---------------------------------------------------------------------
## this function checks the upn (email address) to see if they exist at all in MSOL
## typically if they don't, there is a misspelling or other problem with the name and we want to report on that...
##---------------------------------------------------------------------
function Check-UserExists ($upn)
{
   $spouser = get-msoluser -user $upn -erroraction silentlycontinue
   if ($spouser -eq $null)
   {
       Write-host "user >$upn< Not found in MSOL" -foregroundcolor DarkRed -backgroundcolor Gray
	   $Script:Missingusers += $upn
       return $false 
   }
   else
   {
       return $true
   }
}

##---------------------------------------------------------------------
## this function checks the upn (email address) to see if it has the passed primary License (For example TENANTNAME:ENTERPRISEPACK)
## it returns true if the user has this license, and false if they do not.
##---------------------------------------------------------------------
function CheckUserHasPrimaryLicense ($upn, $PrimaryLicense) 
{
	$ReturnValue = $false
	$spouser = get-msoluser -user $upn
	$count = $($spouser.Licenses | where {$_.AccountSkuId -EQ $PrimaryLicense}).count
	write-host "Found exactly $count Licenses that matched $PrimaryLicense for user $upn" -foregroundcolor yellow
	if ($count -eq 1) 
	{ $ReturnValue = $true }
	return $ReturnValue
}

##---------------------------------------------------------------------
## this function Adds a given SubLicense (for example SHAREPOINTENTERPRISE) 
##  to a users Pre-Existing License (for example TENANTNAME:ENTERPRISEPACK)
##---------------------------------------------------------------------
function Add-SubLicense($upn, $PrimaryLicense, $SubLicense)
{
	$spouser = get-msoluser -user $upn
	#assemble a list of sub-licenses types the user has that are currently disabled, minus the one we're trying to add 
	$disabledServices = $spouser.Licenses.servicestatus | where {$_.ProvisioningStatus -eq "Disabled"}  | select -expand serviceplan | Select ServiceName | where {$_.ServiceName -ne $SubLicense}
	
	#disabled items need to be in an array form, next 2 lines build that...
	$disabled = @()
	foreach  ($item in $disabledServices.servicename) {$disabled += $item}
	
	write-host "  Adding Sub-license $SubLicense to existing $PrimaryLicense License to user $upn" -foregroundcolor green
	write-host "    Disabled License options: '$Disabled'" -foregroundColor green
	
	$LicenseOptions = New-MsolLicenseOptions -AccountSkuId $PrimaryLicense -DisabledPlans $disabled
	set-msoluserlicense  -userprincipalname $upn -licenseoptions  $LicenseOptions
}

##---------------------------------------------------------------------
## this function Assigns a new Primary License ($PrimaryLicense) and SubLicense to a users MSOL account
##---------------------------------------------------------------------
Function Assign-NewLicense($upn, $Location, $PrimaryLicense, $SubLicense)
{
    #assemble a list of sub-licenses available in the tenant, we want to disable all but our target sublicense
	$disabledServices = get-msolaccountsku | Where {$_.accountSkuID -eq $PrimaryLicense} | Select -expand "ServiceStatus" | select -expand "ServicePlan" | select ServiceName | where {$_.ServiceName -ne $SubLicense}
	
	#disabled items need to be in an array form, next 2 lines build that...
	$disabled = @()
	foreach  ($item in $disabledServices.servicename) {$disabled += $item}
	
	write-host "  Adding Completely new $PrimaryLicense license with $SubLicense sublicense for user $upn " -foregroundColor cyan
	write-host "    Disabled License options: $Disabled" -foregroundColor cyan
	
	$LicenseOptions = New-MsolLicenseOptions -AccountSkuId $PrimaryLicense -DisabledPlans $Disabled
	Set-MsolUser -UserPrincipalName $upn –UsageLocation $Location 
	Set-MsolUserLicense -User $Upn -AddLicenses $PrimaryLicense -LicenseOptions $LicenseOptions

}


##---------------------------------------------------------------------
## This function changes the MSOL users password and
## emails the user the temp password and some basic instructions
##---------------------------------------------------------------------
Function Reset-Password($upn)
{
	#generates a random password, 
	#Changes the MSOL Password,
	#emails the user the temp password and some basic instructions
	
	$tempPassword = Generate-Password
	Set-msolUserPassword -UserPrincipalName $upn -NewPassword $tempPassword
	$to = $upn
	$cc = "adminemail@yourdomain.com"
	$from = "adminemail@yourdomain.com"
	$Subject = "Important: Temporary password for your SharePoint Online Account"
	$body =  "Hello, <br/><br/>    You've just been granted a license for SharePoint Online.<br/><br/>"
	$body += "Your user ID is  <b>$upn</b> and your Temporary Password is <b>$TempPassword</b><br/>"
	
	$body += "Please log on to <a href='http://portal.office.com'>http://portal.office.com</a> <b>right now</b> and change the temporary password above to one you'll remember.<br/><br/>"
	
	write-host "Sending email"
	Send-MailMessage -From $from -to $to -cc $cc -Subject $subject -bodyashtml $body -SmtpServer $script:SMTPServer	
	Write-host "Email sent to $upn with $tempPassword"
}

##---------------------------------------------------------------------
## This function generates a random password
## 17 chars long with guaranteed min of 1 number, 1 lower and 1 upper case 
##---------------------------------------------------------------------
Function generate-Password
{
	$alphabetUpper = $NULL;
	for ($a=65; $a -le 90; $a++)
	{
		$alphabetUpper+=,[char][byte]$a 
	}

	$alphabetlower = $NULL;
	for ($a=97; $a -le 122; $a++)
	{
		$alphabetlower+=,[char][byte]$a 
	}

	$ascii=$NULL;
	For ($a=48;$a -le 122;$a++) 
	{
		$ascii+=,[char][byte]$a
	}

	$Fullset=$null
	For ($a=48;$a -le 57;$a++)  #0-9
	{
		$Fullset+=,[char][byte]$a
	}
	For ($a=65;$a -le 90;$a++) #A-Z
	{
		$Fullset+=,[char][byte]$a
	}
	For ($a=97;$a -le 122;$a++) #a-z
	{
		$Fullset+=,[char][byte]$a
	}

	$onepassword = $null
     #start password with an alphabetical letter.
	 $onepassword += (get-random -InputObject $alphabetlower)
	 $onepassword += (get-random -InputObject $alphabetUpper)
	 #now add a number to guarantee we have a number
	 $onepassword += get-random -Minimum 0 -Maximum 9
	 
	 
     #now add 14 random chars from the combined set.
	 for ($pwlen=0; $pwlen -le 11; $pwlen++)
	 {
       $onepassword += (get-random -inputObject $Fullset)
     }
	 return $onePassword
}

##---------------------------------------------------------------------
## This function displays/emails any missing user infomation
##---------------------------------------------------------------------	 
function Report-MissingUsers()
{
  if ($script:MissingUsers.length -gt 0)
  {
    write-host " -------------------------------------- "
	Write-host "          The following users were not found in SPO:          " -foregroundcolor magenta -backgroundcolor gray
	$script:Missingusers
	Write-host "          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^          " -foregroundcolor magenta -backgroundcolor gray
	
	$to = "adminemail@yourdomain.com"
	$from = "adminemail@yourdomain.com"
	$Subject = "Missing users in last license assignment"
	$body = $script:MissingUsers -join "<br/>"
	
	write-host "Sending email of missing users to $to"
	Send-MailMessage -From $from -to $to -Subject $subject -bodyashtml $body -SmtpServer $script:SMTPServer	
	Write-host "Missing user email sent to $to"
  }
}

main #call main procedure, this line must be the very last line in the file.
Stop-transcript #ok, actually this line must be the very last line...

We use ShareGate to migrate content.

We recently started using ShareGate to migrate content from On Premise to SharePoint Online.

When I did this, I found that one of our domain’s users kept showing up as errors in ShareGate – it said it could not find the user in SharePoint Online.

ShareGate has a nice feature for mapping users from one system to users in another – but doing this manually to any scale would be pretty time consuming.

Thankfully, ShareGate lets us save the mappings, which are just XML files with a .sgum file extension.

Wouldn’t it be great if there was a way to automate creating a mapping file like this for everyone in the domain at once?

Have a look at the script below, it pulls all the user accounts from an OU in AD, then looks up each user to find them in MSOL (Office 365 Azure AD) Then grabs the o365 display name and makes the mapping . Any user not found is logged so it can be dealt with separately.

The whole thing is written out as a complete .sgum file, ready to import into ShareGate the next time you migrate!

Note I didn’t figure out the XML stuff in a vacuum – I found an article on powershellmagazine.com to be very helpful and noted it in the script.

# sharepointjack.com
# use at your own risk

$users = get-aduser -server server.domain.com -filter * -searchbase "OU=Users,DC=server,DC=domain,DC=COM"

$total = $users.count
$count = 0
$badnames = @()


#--------------------------------------
# from http://www.powershellmagazine.com/2013/08/19/mastering-everyday-xml-tasks-in-powershell/
$dt = get-date -format "yyyyMMdd"
$path = "$(get-location)\UserMap_$dt.sgum"
$XmlWriter = new-object System.XML.XMLTextWriter($path, $null)
$XmlWriter.Formatting = 'Indented'
$xmlwriter.Indentation = 1
$XmlWriter.IndentChar = "`t"


#write the header
$xmlWriter.WriteStartDocument()

$XmlWriter.WriteComment("Start of XML")
$XMLWriter.WriteStartElement('UserAndGroupMappings')
$XmlWriter.WriteAttributeString('xmlns:xsd', 'http://www.w3.org/2001/XMLSchema')
$XmlWriter.WriteAttributeString('xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance')


$XMLWriter.WriteStartElement("Mappings")
$XmlWriter.WriteComment("Start of main loop")
foreach ($OneUser in $users)
{ 
    $count ++
    $XMLWriter.WriteStartElement("Mapping")
    $SourceAccountName = "DOMAINGOESHERE\$($OneUser.Samaccountname)"
    $SourceDisplayname = $OneUser.name

    $DestinationAccountName = "i:0#f|membership|$($OneUser.UserPrincipalName)"
    #pull the destination user name from MSOnLine
    $DDN = $(get-MSOLuser -userprincipalName $OneUser.UserPrincipalName).Displayname
    #if MSOL not found, length will be zero. in that case use the AD displayname
    if ($DDN.length -eq 0)
    {
       $DestinationDisplayname = $OneUser.name
       $badnames += $OneUser.userprincipalName
       write-host "Warning: $($OneUser.userprincipalName) username Not found in MSOL" -foregroundcolor cyan
    }
    else
    {
       $DestinationDisplayname = $DDN 
       write-host "$count of $total"
    }
 
    $XMLWriter.WriteStartElement("Source")
    $XmlWriter.WriteAttributeString('AccountName', $SourceAccountName)
    $XmlWriter.WriteAttributeString('DisplayName', $SourceDisplayname)
    $XmlWriter.WriteEndElement() #source

    $XMLWriter.WriteStartElement("Destination")
    $XmlWriter.WriteAttributeString('AccountName', $DestinationAccountName)
    $XmlWriter.WriteAttributeString('DisplayName', $DestinationDisplayname)
    $XmlWriter.WriteEndElement() #Destination
    $XmlWriter.WriteEndElement() #mapping
}
$XmlWriter.WriteEndElement() #mappings
$XmlWriter.WriteEndElement() #UserAndGroupMappings


#finalize the document
$xmlWriter.WriteEndDocument()
$xmlWriter.Flush()
$xmlWriter.Close()

$bnpath = "$(get-location)\BadNames_$dt.txt"
$badnames | out-file -filepath $bnpath
notepad $path

– Jack