Change/Add Office 365 Group Email using Office 365 PowerShell


$Creds = Get-Credential -Message “Please enter Office 365 Global Admin Credentials”
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Creds -Authentication Basic -AllowRedirection
Import-PSSession $Session

$AllGroups = Get-UnifiedGroup -Filter *

#Getting first group just for checking. 
$FirstGroup = Get-UnifiedGroup -Identity $AllGroups[0].Id

#Syntax

#{Add=”<Type>:<emailaddress1>”,”<Type>:<emailaddress2>”,…; Remove=”<Type>:<emailaddress2>”,”<Type>:<emailaddress2>”}.

$EmailAdded = @{Add=”smtp:bigwigstest@sharepointmvp.onmicrosoft.com”}
Set-UnifiedGroup -Identity $FirstGroup.Id -EmailAddresses $EmailAdded -Verbose

Advertisements

Export SharePoint Online Site Collection Information to SQL Server


There is easy way to collection reports when you have large number of Site collections on SharePoint Online.  The new Admin center will help but sometime customers have external system where they want to review and control site collection information.  The script below will iterate site collections, sites, lists and permissions and store them to a SQL Server Database.  It uses SharePoint Online Client components so you must downloaded and install the latest version of client Components from PowerShell Gallary using the method below

https://www.nuget.org/packages/Microsoft.SharePointOnline.CSOM

Download nuget command line (I saved it to c:\nuget)

    Command Prompt: 

    1. cd C:\nuget
    2. nuget.exe install Microsoft.SharePointOnline.CSOM

    Powershell (elevated)

    1. cd C:\nuget
    2. Install-Package -Name ‘Microsoft.SharePointOnline.CSOM’ -Source .\Microsoft.SharePointOnline.CSOM.16.1.8412.1200
    3. Import-Module ‘C:\nuget\Microsoft.SharePointOnline.CSOM.16.1.8412.1200\lib\net45\Microsoft.SharePoint.Client.dll

    https://gallery.technet.microsoft.com/Export-SharePoint-Online-27920e67

    Add Custom Banner to SharePoint Site without changing master page


    Few days ago, I got a question if it is possible to add a banner message to a SharePoint Online or On-premises site without customizing the master page.  They were looking for a way to enable and disable this using the Feature framework so I had to write a feature event receiver to upload the customized master page.  The customer was happy but I was not. 

    I did some research and ended up finding something that makes it lot easier to do such things.  You can do it using Code using the event receiver but I wanted to make sure you do not deploy any server side solution.  The script below using CSOM to get things done.  All you need is the jquery file and the custom js file uploaded to a CDN or SharePoint assets library.  If you are running off the server then you need to deploy SharePoint client side assemblies from Microsoft download center.

    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”
    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”

    function Add-SPBanner($SiteUrl, $Credentials)
    {
      $context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
      $context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credentials.UserName,$Credentials.Password)
      $site = $context.Web
      $context.Load($site)
      $context.ExecuteQuery()

      $UserCustomActions = $site.UserCustomActions
      $context.Load($UserCustomActions)
      $context.ExecuteQuery()

      $newAction = $UserCustomActions.Add()
      $newAction.Location = “ScriptLink”
      $newAction.scriptSrc = “~SiteCollection/Style Library/jquery.min.js”
      $newAction.Sequence = 30000
      $newAction.Title= “BannerJquery”
      $newAction.Update()
      $context.ExecuteQuery()

      #add custom js injection action
      $customJSAction = $UserCustomActions.Add()
      $customJSAction.Location = “ScriptLink”
      #reference to JS file
      $customJSAction.ScriptSrc = “~SiteCollection/Style Library/test.js”
      #load it last
      $customJSAction.Title= “BannerJS”
      $customJSAction.Sequence = 30001
      #make the changes
      $customJSAction.Update()
      $context.ExecuteQuery()
     
      Write-Host “Banner has been Added…” -ForegroundColor Green
    }

    function Remove-SPBanner($SiteUrl, $Credentials)

      $context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
      $context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credentials.UserName,$Credentials.Password)
      $site = $context.Web
      $context.Load($site)
      $context.ExecuteQuery()

      $UserCustomActions = $site.UserCustomActions
      $context.Load($UserCustomActions)
      $context.ExecuteQuery()

      $UserCustomActions | ? Title -Like “Banner*” | Select Title, Sequence
      if($UserCustomActions.Count -gt 0)
      {
        $CA = $UserCustomActions | ? Title -eq “BannerJquery”
        $CA.DeleteObject()

        $CA = $UserCustomActions | ? Title -eq “BannerJS”
        $CA.DeleteObject()
        $context.ExecuteQuery()
        Write-Host “Banner has been Removed…” -ForegroundColor Green
      } 
    }

    $Creds = Get-Credential
    $SiteUrl = “https://sharepointmvp.sharepoint.com”

    Add-SPBanner -SiteUrl $SiteUrl -Credentials $Creds
    Remove-SPBanner -SiteUrl $SiteUrl -Credentials $Creds

    Export Office 365 Licenses to CSV


    $Creds = Get-Credential -Message “Please enter Office 365 Global Admin Credentials”
    $InputFile = “C:\temp\UserInput.csv”
    $OutputFile = “C:\temp\UserOutput.csv”

    Connect-MsolService -Credential $Creds

    #$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Creds -Authentication Basic -AllowRedirection
    #Import-PSSession $Session

    $AllUsersData = import-csv -Path $InputFile

    if($AllUsersData)
    {
      $UsersInfo = @()
      foreach($aUser in $AllUsersData)
      {
        $DomainName = “@sharepointmvp.onmicrosoft.com”
        $UserPrincipalName = $aUser.Identity + $DomainName
        Write-Host “Checking Licenses for: $UserPrincipalName” -ForeColor Yellow
        $msolUser = Get-MsolUser -UserPrincipalName $UserPrincipalName -ErrorAction SilentlyContinue
        if($msolUser)
        {
          $Licenses = “None”
          if($msolUser.IsLicensed)
          {
            Write-Host “User $UserPrincipalName is Licensed:” -ForegroundColor Green
            $MSOLUserLicense = $msolUser.Licenses.AccountSkuid
            $Licenses = “”
            for($i = 0; $i -lt $MSOLUserLicense.Count; $i++)
            {
              $Licenses += $msolUser.Licenses[$i].AccountSkuId
              if($i -le $MSOLUserLicense.Count)
              {
                $Licenses += “;`n”
              }
            }
          }
         
          $userObj = New-Object PSObject
          Add-Member -input $userObj noteproperty ‘UserPrincipalName’ $UserPrincipalName
          Add-Member -input $userObj noteproperty ‘Alias’ $aUser.Alias
          Add-Member -input $userObj noteproperty ‘DisplayName’ $aUser.DisplayName
          Add-Member -input $userObj noteproperty ‘Licenses’ $Licenses
          $UsersInfo += $userObj
        }
      }
      $UsersInfo | Export-Csv -Path $OutputFile -NoTypeInformation -Force -Verbose
      Write-host “Output written to $OutputFile”
    }
    start $OutputFile

    Lockout Content Database for Migration


    Below is a basic script to lockout Content Database for new site collections.  It sets the max to current sites count and warning to less than 1 of count.  May help someone.

    $ALLDB = Get-SPContentDatabase
    foreach($DB in $ALLDB)
    {
        $Count = $DB.Sites.Count
        if($Count -gt 0)
        {
            $CountWarnining = $Count – 1
            Set-SPContentDatabase $DB.Name -MaxSiteCount $Count -WarningSiteCount $CountWarnining
        }
    }

    Get-SPContentDatabase | Select Name, Current*, Max*, WarningSiteCount | Out-GridView

    Export Site Collection Admins from SharePoint Online Sites


    Basic but handy script to export Site Collection Administrators using SharePoint Online Client Object Model and SharePoint Online Management Shell.  I hope there is a better to way get that information as the script can take long time to run if you have large number of users.  I ran the script on multiple tenants with 90K users.  I have a minor updated script that also works for SharePoint Online sites created by Office 365 Groups.

    $Creds = Get-Credential

    $site = ‘https://tenant.sharepoint.com’
    $spoadminsite = ‘https://tenant-admin.sharepoint.com’

    Connect-SPOService -Url “https://ucsfonline-admin.sharepoint.com” -Credential $Creds
    Connect-MsolService -Credential $Creds

    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”
    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll”

    $Sites = Get-SPOSite -Limit All

    $OneDriveWithcollection = @()
    $SitesData = IMport-csv -Path $OneDrivePath
    foreach($Site in $Sites)
    {
        $Site.Owner
        $Url = $Site.Url
        Write-host $Url
        Set-SPOUser -Site $Url -LoginName $Creds.UserName -IsSiteCollectionAdmin $true
        $context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
        $spoadminusers = Get-SPOUser -Site $Url | where {$_.IsSiteAdmin}
        foreach($Admin in $spoadminusers)
        {
            Write-Host “Site :” $Url
            $OneDriveData = “” | Select “Url” , “OwnerName”, “Email”
            $OneDriveData.Url = $Url
            $OneDriveData.OwnerName = $Admin.LoginName
            $OneDriveData.Email = $Admin.Email   
            $OneDriveWithcollection += $OneDriveData
        }
       
    }

    $OneDriveWithItemsPath= “C:\temp\SPO-SitesAdmins.csv”
    $OneDriveWithcollection | Export-Csv $OneDriveWithItemsPath -NoTypeInformation -Encoding UTF8

    Update: The script is also available

    https://gallery.technet.microsoft.com/Export-Collection-Admins-e8c0c176

    Get OneDrive for Business Item Count SharePoint Online PowerShell


    $Creds = Get-Credential

    $site = ‘https://tenant.sharepoint.com’
    $spoadminsite = ‘https://tenant-admin.sharepoint.com’

    Connect-MsolService -Credential $Creds
    Connect-SPOService -Url $spoadminsite -Credential $Creds

    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”
    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll”

    #Get the Client Context and Bind the Site Collection
    $context = New-Object Microsoft.SharePoint.Client.ClientContext($site)

    #Authenticate
    $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)
    $context.Credentials = $credentials

    #Fetch the users in Site Collection
    $users = Get-MsolUser -All

    #Create an Object [People Manager] to retrieve profile information
    $people = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($context)
    $context.ExecuteQuery()
    $collection = @()
    Foreach($user in $users)
    {
     
        $ClaimsUserFormat = “i:0#.f|membership|$($user.UserPrincipalName)”                 
        $userprofile = $people.GetPropertiesFor($ClaimsUserFormat)
        $context.Load($userprofile)
        $context.ExecuteQuery()
        Write-Host $userprofile.Email
        Write-Host $user.LoginName
        Write-Host $userprofile.AccountName
        if($userprofile.AccountName -ne $null)
        {
            Write-Host “Email : ” $userprofile.Email
            $upp = $userprofile.UserProfileProperties

            $profileData = “” | Select “FirstName” , “LastName” , “AccountName” , “PersonalUrl” , “Email” , “LoginName”
            $profileData.LoginName = $user.UserPrincipalName
            $profileData.FirstName = $upp.FirstName
            $profileData.LastName = $upp.LastName
            $profileData.Email = $upp.WorkEmail
            $profileData.AccountName = $upp.AccountName
            $profileData.PersonalUrl = $userprofile.PersonalUrl
            $collection += $profileData

        }
    }

    $OneDrivePath= “C:\temp\SPO-UserInformation.csv”
    $collection | Export-Csv $OneDrivePath -NoTypeInformation -Encoding UTF8

    $OneDriveWithcollection = @()
    $SitesData = IMport-csv -Path $OneDrivePath
    foreach($Site in $SitesData)
    {
      $Url = $Site.PersonalUrl
      if($Url.Contains(“Person.aspx”))
      {
        Write-host “No profile”
      }
      else
      {
        Write-host $Url
        $context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
        Write-Host $Site.LoginName
        $SiteUrl = $Url.Remove($Url.Length-1, 1)
        Set-SPOUser -Site $SiteUrl -LoginName $Creds.UserName -IsSiteCollectionAdmin $true

        #Authenticate
        $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)
        $context.Credentials = $credentials
        $Web = $context.Web
        $context.Load($Web)
        $List = $context.Web.Lists.GetByTitle(“Documents”)
        $context.Load($List)
        $context.ExecuteQuery();
        Write-Host “Items Found :” $List.ItemCount
        $OneDriveData = “” | Select “Email” , “ItemCount”, “LastItemModifiedDate”, “LastItemUserModifiedDate”
        $OneDriveData.Email = $Site.Email
        $OneDriveData.ItemCount = $List.ItemCount
        $OneDriveData.LastItemModifiedDate = $Web.LastItemModifiedDate   
        $OneDriveData.LastItemUserModifiedDate = $Web.LastItemUserModifiedDate
        $OneDriveWithcollection += $OneDriveData
      }
    }

    $OneDriveWithItemsPath= “C:\temp\SPO-OneDriveItems.csv”
    $OneDriveWithcollection | Export-Csv $OneDriveWithItemsPath -NoTypeInformation -Encoding UTF8

    Update: The script is also available below

    https://gallery.technet.microsoft.com/Get-OneDrive-for-Business-04b3632c