Get Site collection Administrators for SharePoint Online using PowerShell


SharePoint Online sites can have thousands of users.  There are times when people are asked to get site collection admins using SharePoint Online Management Shell or Get-SPOUser cmdlet with Where $_.IsSiteAdmin.  This will result in timeout if there are lot of users on the site.  Instead I decided to use a different approach.  First get all the users from site into a CSV and then make the check. 

image

$Creds = Get-Credentials

$site = ‘https://sharepoint-admin.sharepoint.com’
Connect-SPOService -Url $site -Credential $Creds

$AllUsers = Get-SPOUser -Site https://site.sharepoint.com -Limit all | select DisplayName, LoginName,IsSiteAdmin
$AllUsers | Export-Csv -Path C:\temp\allusers.csv -NoTypeInformation -Force
$Data = Import-Csv C:\temp\allusers.csv
foreach($aUser in $Data)
{
  if($aUser.IsSiteAdmin -eq “True”)
  {
    Write-Host $aUser.DisplayName $aUser.LoginName
  }
}

Advertisements

Get SharePoint Online Group Information from PowerShell using Client Object Model


If you try to use PowerShell and CSOM to get SharePoint Online Group Owner information, you learn that the returned Microsoft.SharePoint.Client.Group object Owner property will be empty.  I tried to read value of Owner property and got the following.

image

I found two ways to get over this.  First I used the basic Object.Retreive method and pass an array or string properties.  The output was show as expected.

To see all the properties you can use

$Owner | Select *

$Group = $Web.SiteGroups.GetByName(“Test”)

$Owner = $Group.Owner

$Owner.Retrieve(“Id”,”LoginName”,”Title”,”PrincipalType”,”IsHiddenInUI”)

$Context.ExecuteQuery() $

$Owner | Select *

The output is exactly as I expected

image

The second method would be to use Lambda expressions.  PowerShell does not support lambda expressions like C# (object, a => a.XYZ) so I did some research and found a post from PowerShell Guru Garry Lapointe here.  It has a PowerShell script which enables you to call any property of the main object directly. 

https://gist.github.com/glapointe/cc75574a1d4a225f401b#file-load-csomproperties-ps1

$Group = $Web.SiteGroups.GetByName(“Test”)
$Owner = $Group.Owner
Load-CSOMProperties -object $Group.Owner -propertyNames @(“ID”,”LoginName”,”Title”,”PrincipalType”,”IsHiddenInUI”)
$Context.ExecuteQuery()
$Owner | Select *

Output is exactly the same but lot cleaner script as I don’t have to work with Retrieve method.
image

Export Server Certificates to CSV and Email


We have large SharePoint environments where we use different certificates.  Certificates expires over the period of times and some time admins leave old certificates on the servers which cause confusions.  To Avoid this behavior I wrote the script below to check Root and Personal certs from all servers that I need and save it to CSV.  I have not made the servers list SharePoint specific so you can add as many servers as you needed.  The script also sends the CSV as email.  I hope it might save time for others.

Add-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue

#The mail address of who will receive the backup exception message

$from

= “someone@domain.com”

#Send email function

function

SendMail($subject, $body, $file)

{

try

{

#Getting SMTP server name and Outbound mail sender address

$caWebApp = (Get-SPWebApplication -IncludeCentralAdministration) | ? { $_.IsAdministrationWebApplication -eq $true }

$smtpServer = $caWebApp.OutboundMailServiceInstance.Server.Address

$smtp = new-object Net.Mail.SmtpClient($smtpServer)

#Creating a Mail object

$message = New-Object System.Net.Mail.MailMessage

$att = New-Object System.Net.Mail.Attachment($file)

$message.Subject = $subject

$message.Body = $body

$message.Attachments.Add($att)

$To = “someone@domain.com”

$message.To.Add($to)

$message.From = $from

#Creating SMTP server object

#Sending email

$smtp.Send($message)

Write-Host “Email has been Sent!”

}

catch [System.Exception]

{

Write-Host “Mail Sending Error:” $_.Exception.Message -ForegroundColor Red

}

}

function

Get-Cert($computer){

$ro=[System.Security.Cryptography.X509Certificates.OpenFlags]“ReadOnly”

$lm=[System.Security.Cryptography.X509Certificates.StoreLocation]“LocalMachine”

$store=new-object System.Security.Cryptography.X509Certificates.X509Store(“\\$computer\My”,$lm)

$store.Open($ro)

$store.Certificates

}

function

Get-RootCert($computer){

$ro=[System.Security.Cryptography.X509Certificates.OpenFlags]“ReadOnly”

$lm=[System.Security.Cryptography.X509Certificates.StoreLocation]“LocalMachine”

$store=new-object System.Security.Cryptography.X509Certificates.X509Store(“\\$computer\root”,$lm)

$store.Open($ro)

$store.Certificates

}

 

$Servers

= @(“Server1”,“Server2”)

$datestring

= (Get-Date).ToString(“s”).Replace(“:”,“-“)

$file

= “E:\temp\Certificates-$env:COMPUTERNAME$datestring.csv”

$Databases

= @();

foreach

($Server in $Servers)

{

$Certs = Get-Cert($Server)

foreach($Cert in $Certs)

{

$FriendlyName = $cert.FriendlyName

$Thumbprint = $Cert.Thumbprint

$Issuer = $Cert.Issuer

$Subject = $Cert.Subject

$SerialNumber = $Cert.SerialNumber

$NotAfter = $Cert.NotAfter

$NotBefore = $Cert.NotBefore

$DnsNameList = $cert.DnsNameList

$Version = $cert.Version

$DB = New-Object PSObject

Add-Member -input $DB noteproperty ‘ComputerName’ $Server

Add-Member -input $DB noteproperty ‘FriendlyName’ $FriendlyName

Add-Member -input $DB noteproperty ‘DnsNameList’ $DnsNameList

Add-Member -input $DB noteproperty ‘ExpirationDate’ $NotAfter

Add-Member -input $DB noteproperty ‘IssueDate’ $NotBefore

Add-Member -input $DB noteproperty ‘Thumbprint’ $Thumbprint

Add-Member -input $DB noteproperty ‘Issuer’ $Issuer

Add-Member -input $DB noteproperty ‘Subject’ $Subject

Add-Member -input $DB noteproperty ‘SerialNumber’ $SerialNumber

$Databases += $DB

}

$RootCerts = Get-RootCert($Server)

foreach($Cert in $RootCerts)

{

$FriendlyName = $cert.FriendlyName

$Thumbprint = $Cert.Thumbprint

$Issuer = $Cert.Issuer

$Subject = $Cert.Subject

$SerialNumber = $Cert.SerialNumber

$NotAfter = $Cert.NotAfter

$NotBefore = $Cert.NotBefore

$DnsNameList = $cert.DnsNameList

$Version = $cert.Version

$DB = New-Object PSObject

Add-Member -input $DB noteproperty ‘ComputerName’ $Server

Add-Member -input $DB noteproperty ‘FriendlyName’ $FriendlyName

Add-Member -input $DB noteproperty ‘DnsNameList’ $DnsNameList

Add-Member -input $DB noteproperty ‘ExpirationDate’ $NotAfter

Add-Member -input $DB noteproperty ‘IssueDate’ $NotBefore

Add-Member -input $DB noteproperty ‘Thumbprint’ $Thumbprint

Add-Member -input $DB noteproperty ‘Issuer’ $Issuer

Add-Member -input $DB noteproperty ‘Subject’ $Subject

Add-Member -input $DB noteproperty ‘SerialNumber’ $SerialNumber

$Databases += $DB

}

}

# $Databases | Out-GridView

$Databases

| Sort FriendlyName | Export-Csv -Path $file -NoTypeInformation -Append -Force

SendMail

“Abbvie NA Farm” “Server Certificates” $file

Export Certificates from All Servers to CSV


If you are managing a large SharePoint Farm which uses SSL Certificates for Web Applications, Apps etc, then you would like to see the status of Certificates of certificates every once  while.  Most certificates are issued once every year so SharePoint site can result in Error and app can fail if failed certificate is used.  I wrote the script below to export and email the certificate from a SharePoint Server.  It may save few clicks for someone.  The script below will export all Root and Personal Certificates to CSV and Email the designated User or Group.  It should be deployed on a SharePoint Box to dynamically read the SMTP Server.  Change or Disable the Email script to make it dynamic for Non SharePoint Environment.

Check here

bit.ly/exportCertificatesToCSV

Adding SharePoint Group to List Permissions


There are situations when users create lists and libraries and set them to use unique permissions.  Many times users mistakenly remove the Site Owners group from the list and get access denied.  Few times you have large number of list that uses Unique permissions and you want to add a specific group to all those lists.  The script below can do that for you.  You just need to select the lists and then add a SharePoint Group with right permission Level.  In the example below I am using Site Owners Group with Full Control Permissions. 

Adding SharePoint Group to List Permissions

https://gallery.technet.microsoft.com/Adding-SharePoint-Group-to-ce2b0591?redir=0

$Creds = Get-Credential    $site = Get-SPOSite https://site.sharepoint.com    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'     #Get the Client Context and Bind the Site Collection  $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($site.Url)    #Authenticate  $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)  $ctx.Credentials = $credentials    #Fetch the users in Site Collection    $Web = $ctx.Web;  $ctx.Load($Web)  $Lists = $Web.Lists  $ctx.Load($Lists)  $ctx.ExecuteQuery()    $Lists | Select Title, BaseType, ItemCount  #$SAPLists = $Lists | ?{$_.Title -Like "*LibraryName*" } | Select Title, BaseType   #OR  $SAPLists = $Lists | ?{$_.BaseType -eq "DocumentLibrary" } | Select Title,BaseType, ItemCount  #OR  #$SAPLists = $Lists | ?{$_.Title -Like "SAP Data Quality Review*" } | Select Title | Out-GridView -PassThrough  $SAPLists | Select Title, BaseType,ItemCount | Sort ItemCount -Descending    foreach($alist in $SAPLists)  {      Write-Host $alist.Title    $OwnersGroupTitle = "Site Owners"    $OwnerGroup = $Web.SiteGroups.GetByName($OwnersGroupTitle)        $PermissionLevel = "Full Control"    $FullControl = $web.RoleDefinitions.GetByName($PermissionLevel)    # Create a role assignment and apply the 'Full ' role.    $roleAssignment = New-Object Microsoft.SharePoint.Client.RoleDefinitionBindingCollection($ctx)    $roleAssignment.Add($FullControl)         $SelectedList = $Web.Lists.GetByTitle($alist.Title)    $ctx.Load($SelectedList)    $ctx.Load($SelectedList.RoleAssignments.Add($OwnerGroup,$roleAssignment))    $SelectedList.Update()    $ctx.ExecuteQuery()  }

“Attachments” folder in OneDrive for Business


In the first week of January, one of our customer rolled out OneDrive for business function to 5000 Users.  After all the sites were provisioned, some users came back and asked question about a “Attachments” folder.  We did not had any details. 

SNAGHTML15249fd

After some research I did found about “Email Attachment” folder.  This folder will be used by Office 365 Outlook App to store attachments to OneDrive.  This feature was introduced back in January 2016. You can find more information from https://blogs.office.com/2015/01/14/save-outlook-com-email-attachments-onedrive-one-click/

After working with Microsoft, we are told that this feature is part a new feature that is being rolled out. 

Focused Inbox for Outlook for Windows, Mac and web

The Focused Inbox helps you focus on the stuff that matters most. Loved on Outlook for iOS & Android, now available on Outlook for Mac. Soon to be available on all Outlook endpoints. More details

SharePoint Online Site Alerts with Mail-Enabled Security Groups


SharePoint alerts are important part of end user functions for SharePoint sites.  Alerts can be created for Lists, Libraries, folders and individual items.  You can create alerts for yourself and others.   In SharePoint Online you can not send alerts to mail-enabled Security Groups by default. You must add the Mail-Enabled security Group one SharePoint group with view permissions.  SharePoint Alerts will work.  If you do not want to add the group to SharePoint then you can use the Outlook Rules to forward email to the group.  SharePoint workflow can also be used to send email as well.