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()  }

Export All SharePoint Online Term Group, Term Sets and Terms to CSV


param (
    [Parameter(Mandatory=$true)]
        [string]$RootSiteCollectionUrl
)

# Paths to SDK. Please verify location on your computer.
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.Taxonomy.dll”

$Credentials = Get-Credential -Message “Please enter SPO Admin User Name and Password”
$ctx=New-Object Microsoft.SharePoint.Client.ClientContext($RootSiteCollectionUrl)
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credentials.UserName, $Credentials.Password)
$ctx.ExecuteQuery() 

$session = [Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($ctx)
$ctx.Load($session)
$ctx.ExecuteQuery()

$termstore = $session.GetDefaultSiteCollectionTermStore();
$ctx.Load($termstore)
$ctx.ExecuteQuery()

  $groups=$termstore.Groups
  $ctx.Load($groups)

  $ctx.ExecuteQuery()

  $Termstring = “”
  $Header=”TermGroup,TermSet,Term`r`n”
  $Termstring = $Header
  foreach($group in $groups)
  {
    $ctx.Load($group)
    $ctx.Load($group.TermSets)
    $ctx.ExecuteQuery()
    Write-Output $group.Name
    $Termstring +=”$($group.Name)`r`n”
    foreach($termset in $group.TermSets)
    {
        Write-Output ”    $($termset.Name)”
        $Termstring +=”,$($termset.Name)`r`n”
        $ctx.Load($termset)
        $ctx.Load($termset.Terms)
        $ctx.ExecuteQuery()
        foreach($term in $termset.Terms)
        {
            Write-Output ”        $($term.Name)”
            $Termstring +=”,,$($term.Name)`r`n”
        }
    }
  }

Out-File -InputObject $Termstring C:\Scripts\Terms.csv -Encoding ascii -Force
C:\Scripts\Terms.csv

List and Export External Users to CSV


This windows application allows SharePoint Online Administrators to list and export all external users to CSV.  You can run the script to iterate all site collections or against the list provided as CSV.  You can double click on a row to open external user management in internet explorer.  Next steps for me would be to allow you to remove the user without going to the site.

I have tested this tool with tenants of 4K and 10K sites without any issue.

Note: The Root Site Collection URL must be SharePoint Admin Url. Not the root site collection url as asked

#To Generate the Input CSV

$SitesToAudit

= Get-SPOSite -Limit All | Where-Object {$_.SharingCapability –ne “Disabled”}

$SitesToAudit

| Select Url | Export-Csv -Path C:\Scripts\InputCSV.csv -NoTypeInformation -Verbose -Force

image

https://gallery.technet.microsoft.com/office/List-and-Export-SharePoint-d670dc40

Enable Auditing – SharePoint Online Site Collection


Auditing is normally a required feature for most organization which wants to track user actions on a SharePoint site.  The functionality can be enabled at site collection level.  But most organizations try to enable it when they already have large number of of site collections.  Doing it manually by going to Site settings – Site collection audit settings would be a tough job if you have large number of site collections.  I wrote the script which may help someone.

The script uses both Client Side Object Model and SharePoint Online Management Shell cmdlets.  Make sure you change the location of SharePoint Client OM dlls.  I have set it as C:\CSOM.  You do not need to specify the Admin Url as I am creating it from User name.

function Enable-SPOAuditing
{
    <#
        .SYNOPSIS
        Enables or disables Auditing on SharePoint ONline Sites
        .DESCRIPTION
        Enables or disables Auditing on SharePoint ONline Sites
        .EXAMPLE
        Enable-SPOAuditing
        explains how to use the command
        can be multiple lines
        .EXAMPLE
        Enable-SPOAuditing
        another example
        can have as many examples as you like
    #>
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory=$true)]
        [System.String]
        $SPOSiteUrl,
        [Parameter(Mandatory=$true)]
        [System.String]
        $UserName,
        [Parameter(Mandatory=$true)]
        [System.Security.SecureString]
        $Password
    )
    $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($SPOSiteUrl)
    $spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $Password)
    #SPO Client Object Model Context
    $spoCtx.Credentials = $spoCredentials
    $spoSite = $spoCtx.Site
    $spoCtx.Load($spoSite)
    $Audit = $spoSite.Audit
    $spoCtx.Load($Audit)
    $spoCtx.ExecuteQuery
    $All = [Microsoft.SharePoint.Client.AuditMaskType]::All;
    $None = [Microsoft.SharePoint.Client.AuditMaskType]::None;
    $CheckIn = [Microsoft.SharePoint.Client.AuditMaskType]::CheckIn;
    $CheckOut = [Microsoft.SharePoint.Client.AuditMaskType]::CheckOut;
    $ChildDelete = [Microsoft.SharePoint.Client.AuditMaskType]::ChildDelete;
    $CheckIn = [Microsoft.SharePoint.Client.AuditMaskType]::CopyCheckIn;
    $Move = [Microsoft.SharePoint.Client.AuditMaskType]::Move;
    $ObjectDelete = [Microsoft.SharePoint.Client.AuditMaskType]::ObjectDelete;
    $ProfileChange = [Microsoft.SharePoint.Client.AuditMaskType]::ProfileChange;
    $SchemaChange = [Microsoft.SharePoint.Client.AuditMaskType]::SchemaChange;
    $Search = [Microsoft.SharePoint.Client.AuditMaskType]::Search;
    $SecurityChange = [Microsoft.SharePoint.Client.AuditMaskType]::SecurityChange;
    $Undelete = [Microsoft.SharePoint.Client.AuditMaskType]::Undelete;
    $Update = [Microsoft.SharePoint.Client.AuditMaskType]::Update;
    $View = [Microsoft.SharePoint.Client.AuditMaskType]::View;
    $Workflow = [Microsoft.SharePoint.Client.AuditMaskType]::Workflow;
    $Audit.AuditFlags = $Search, $Update, $Undelete, $Workflow, $SecurityChange
    $Audit.Update()
    $spoSite.AuditLogTrimmingRetention = 60
    $spoSite.TrimAuditLog = $true
    #Enable All Options Auditing
    $Audit.AuditFlags = $All
    $Audit.Update()
    #Disable Auditing
    $Audit.AuditFlags = $None
    $Audit.Update()
    $spoCtx.ExecuteQuery()
}

$User = “firstnane.lastname@domain.onmicrosoft.com”
$Password = ConvertTo-SecureString “pass@word1” -AsPlainText –Force

#Or Use Get-Credentials to create the object.
$Creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Password

$CSOMAssembliesPath=”C:\CSOM”

$ClientDllPath         = $CSOMAssembliesPath +  “\Microsoft.SharePoint.Client.dll”
$ClientRunTimeDllPath  = $CSOMAssembliesPath +  “\Microsoft.SharePoint.Client.Runtime.dll”       
Add-Type -Path $ClientDllPath -ErrorAction SilentlyContinue
Add-Type -Path $ClientRunTimeDllPath -ErrorAction SilentlyContinue

$AdminUrl = $User
$AtSepPostion = $AdminUrl.IndexOf(‘@’) + 1
$DotPostion = $AdminUrl.LastIndexOf(“.onmicrosoft”)
$DomainName = $AdminUrl.Substring($AtSepPostion, $DotPostion – $AtSepPostion)
$FinalAdminUrl = “https://$DomainName-admin.sharepoint.com”

Connect-SPOService -Url $FinalAdminUrl -Credential $Creds
#To Test

#$Sites = Get-SPOSite -Limit 1

$Sites = Get-SPOSite -Limit All | Select Title, Url, Owner

foreach($Site in $Sites)
{
    Enable-SPOAuditing -SPOSiteUrl $Site.Url -UserName $User -Password $Password
}