SharePoint Online

Get InfoPath Usage in your SharePoint Online site using PowerShell

Posted on Updated on

I was involved recently in a project to get InfoPath usage in SharePoint Online site.
This script is useful to know How many InfoPath file is created in your Site/Tenant, it also differentiate between InfoPath path form, and InfoPath Custom list form.
This also works on SharePoint 2013 & SharePoint 2016 as it is relying on Client Object Model, but you will point to SP2013/SP2016 Client dlls


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
Function Get-SPOContext([string]$Url,[string]$UserName,[string]$Password)
{
$SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
$context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
return $context
}
$Header = "Site Url; Web Url; Base Template; List Title; Item Count; "
$UserName = "sitecollectionadmin@site.onmicrosoft.com"
$Password = Read-Host -Prompt "Password"
$Url = "https://tenant.sharepoint.com/sites/yoursite"
$context = Get-SPOContext -Url $Url -UserName $UserName -Password $Password
$site = $context.Site
$context.Load($site)
$context.ExecuteQuery()
$rootWeb = $site.RootWeb
$context.Load($rootWeb)
$context.ExecuteQuery()
$webs = $rootWeb.Webs
$context.Load($webs)
$context.ExecuteQuery()
$AllWebs= @()
$AllWebs += $rootWeb
$AllWebs += $webs
foreach ($web in $AllWebs)
{
$web.Url
$context.Load($web)
$context.ExecuteQuery()
$lists = $web.Lists;
$context.Load($lists)
$context.ExecuteQuery()
foreach ($list in $lists)
{
$context.Load($list)
$context.ExecuteQuery()
#Write-Host $list.Title $list.BaseTemplate -ForegroundColor Yellow
if($list.BaseTemplate -eq 115 )
{
Write-Host "XML InfoPath Forms " $list.Title $list.BaseTemplate -ForegroundColor Green
}
elseif ($list.BaseTemplate -ne 101)
{
$context.Load($list.ContentTypes);
$context.ExecuteQuery();
if($list.ContentTypes.Count -gt 0)
{
$context.Load($list.ContentTypes[0]);
$context.ExecuteQuery();
if ($list.ContentTypes[0].EditFormUrl.Contains("editifs.aspx"))
{
Write-Host "Custom InfoPath List :" $list.Title $list.BaseTemplate -ForegroundColor Cyan
}
}
}
}
}
$context.Dispose()

You an adjust the script to output the results to csv or excel
Please leave me a comment if you faced any issues with it
Enjoy !

Advertisements

Extract Audit Logs for Office 365 Services using PowerShell

Posted on Updated on

It was always confusing while using Search-UnifiedAuditLog, because you need first to load Exchange libraries.
In this script we will retrieve the Audit logs from Office 365 for the below services

  1. Skype for Business
  2. Exchange
  3. Yammer
  4. OneDrive for Business
  5. SharePoint Online

Please be aware that the command “Search-UnifiedAuditLog” will never loads until you load the Exchange New-PSSession -ConfigurationName Microsoft.Exchange, this means you have to use the Tenant admin to execute the below code.

The extraction will be very useful to get audit logs for your Office 365 Services, and it will be extracted to CSV files


$Username = "tenant.admin@henkel.com"
$TenantODFBUrl = "https://tenant-my.sharepoint.com"
$TenantAdminUrl = "https://tenant-admin.sharepoint.com"
$Pass = "PaSSword!"
[String]$Output = ".\report_$((Get-Date -uformat %Y%m%d).ToString()).csv"
$usersList = Get-Content "C:\temp\TargetUsers.txt"
$cred = [System.Net.CredentialCache]::DefaultCredentials
[System.Net.WebRequest]::DefaultWebProxy.Credentials = $cred
$creds = New-Object System.Management.Automation.PSCredential($Username,(ConvertTo-SecureString $Pass -AsPlainText -Force));
$session = New-PSSession -ConfigurationName Microsoft.Exchange -Authentication Basic -ConnectionUri https://ps.outlook.com/powershell -AllowRedirection:$true -Credential $creds
Import-PSSession $session -AllowClobber
$startDate = $(Get-Date).AddDays(-60).Date
$endDate = $(Get-Date).AddDays(-1).Date
Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -ResultSize 5000 |Sort-Object CreationDate -Descending | Export-Csv .\SearchResult.csv -NoTypeInformation
#Skype for Business
Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -RecordType "SkypeForBusinessCmdlets","SkypeForBusinessPSTNUsage","SkypeForBusinessUsersBlocked" |Sort-Object CreationDate -Descending | Export-Csv .\SkypeSearchResult.csv -NoTypeInformation
#Exchange
Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -RecordType "ExchangeAdmin","ExchangeItemGroup","ExchangeItem","ExchangeAggregatedOperation" |Sort-Object CreationDate -Descending | Export-Csv .\ExchangeSearchResult.csv -NoTypeInformation
#Yammer
Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -UserIds $usersList -RecordType "yammer" |Sort-Object CreationDate -Descending | Export-Csv .\YammerSearchResult.csv -NoTypeInformation
#OneDriveForBusiness
$OneDriveworkload = '*"Workload"' + ":" + '"OneDrive"*'
Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -UserIds $usersList | Where-Object {$_.AuditData -like $OneDriveworkload } |Sort-Object CreationDate -Descending | Export-Csv .\OneDriveSearchResult.csv -NoTypeInformation
#SharePoint Online
$workload = '*"Workload"' + ":" + '"SharePoint"*'
Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -UserIds $usersList -RecordType "SharePointFileOperation","SharePoint","SharePointSharingOperation" | Where-Object {$_.AuditData -like $workload} |Sort-Object CreationDate -Descending | Export-Csv .\SPSearchResult.csv -NoTypeInformation

Please leave me a comment if you faced any issues with it
Regards !

Get SharePoint Online Event Receivers

Posted on Updated on

In this article we will get list of Event receivers attached to SiteCollection or Web Levels.
Please follow these steps :

  1. Make sure Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll
  2. Open PowerShell ISE & Run the Below script after setting the target parameter
    # 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"
    #declare the variables
    $tenantSite = "https://tenant-admin.sharepoint.com"
    #read file
    $siteurl = "https://tenant.sharepoint.com/teams/site1"
    $UserName = "admin@tenant.onmicrosoft.com"
    $SecurePassword = Read-Host -Prompt "Please enter your password" -AsSecureString
    $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $SecurePassword
    $SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
    Connect-SPOService -Url $tenantSite -Credential $Credentials
    $context = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)
    $context.Credentials = $SPOCredentials
    $web = $context.Site.RootWeb #You can make it only $context.Site if you want to get the Events on Site Collection level
    $context.Load($web)
    $context.Load($web.EventReceivers)
    $context.ExecuteQuery()
    $eventReceivers = $web.EventReceivers
    foreach($eventReceiver in $eventReceivers)
    {
    Write-Host $eventReceiver.ReceiverId " " $eventReceiver.EventType.ToString() " " $eventReceiver.ReceiverName
    }
  3. Output will be as follow

Of course the above script can be modified to be compatible with If you want to verify your results, you can use SharePoint Client Browser (SPCB)
It is a free helpful open source tool that can give you nice insights about your Site/Web event receivers and it supports 3 versions (SP2013, SP2016, & SPOnline)

 

Get SharePoint Admins for All SharePoint Online Sites

Posted on Updated on

In this article we will get list of all SharePoint Online Sites, and then List Site Admins using PnP library
Please follow these steps :

      1. Set-up PnP module latest version from here https://github.com/SharePoint/PnP-PowerShell/releases
      2. Run the Below script after setting the target parameter
        #Connect to SPO tenant
        $CurrentCred = Get-Credential
        Connect-SPOService "https://tenant-admin.sharepoint.com" -Credential $CurrentCred
        #Get all Site collections
        $sites = Get-SPOSite -Limit All
        foreach ($site in $sites)
        {
        Connect-PnPOnline -Url $site.Url -Credential $CurrentCred
        $admins = Get-PnPSiteCollectionAdmin | select Title #You can also add ,Email
        $allAdmins=""
        foreach($admin in $admins)
        {
        $allAdmins += $admin.Title +";" #You can have also $admin.Email
        }
        Write-host ($site.Url+","+$allAdmins) -ForegroundColor Green
        ($site.Url+","+$allAdmins) >> "C:\temp\SiteCollectionAdmins.csv"
        }

Then you will have a CSV file with SiteUrl & Site Admin Name delemited by ;

Apply SharePoint Online template using PnP PowerShell

Posted on

In this article we will define a template, and apply it for other SPO sites using PnP library
Please follow these steps :

    1. Set-up PnP module latest version from here https://github.com/SharePoint/PnP-PowerShell/releases
    2. Go to your SPO tenant, and create a SPO Site, you can also define (List structure, Theme, Pages, Content Types) because all of those will be part of the template.
    3. Make sure that the app catalog is activated on the tenant (There is no relation between app catalog and the tenant, but there is a bug found in January PnP package that required App catalog to be activated or a failure occurred)
    4. After having the site ready, run the below PowerShell command:
      $Creds = Get-Credential
      $SiteURL = "https://test.sharepoint.com/sites/demo1/"
      Connect-PnPOnline -Url $SiteURL -Credentials $Creds
      Get-PnPProvisioningTemplate -Out C:\temp\demo1Template.xml
    5. You should see a progress running for a while to export all settings.
    6. Please open the XML file at the provisioned path (C:\temp\demo1Template.xml), and have a look, it is also doable to do medications through the file, by adding items, or fields, but you need to be careful not to crap the schema.
    7. Now it is time to import it to the destination site, Create a destination site on SPO with a default teamsite template
    8. Apply the new template by running the below command.
      $DestUrl = "https://test.sharepoint.com/sites/destination1/"
      Connect-PnPOnline -Url $DestUrl -Credentials $Creds
      $template = Load-PnPProvisioningTemplate -Path "C:\temp\demo1Template.xml"
      Apply-PnPProvisioningTemplate -InputInstance $template

After this, refresh the destination site, and you should see the template applied.
I suggest to start first with a simple template, then apply more complexity to it. this approach should run smoothly also with SP2016, but I haven’t tried to import a template from SPO and import it to 2016 or vise versa

List SharePoint Online Sites in PowerShell using PnP

Posted on Updated on

How to Retrieving List of SharePoint Online Sites in PowerShell using PnP.

Currently PnP is considered to be the most modern way to deal with SharePoint Online and On-Prem, because it provides one interface for both

Quick handy script to do that, and you can take the output to write it in CSV file or import it in SharePoint List, or store it in any other repository

1. You need first to download PnP PowerShell release, from here
https://github.com/SharePoint/PnP-PowerShell/releases

2. Download the library, and un-zip the file
3. Now you will need to relocate the module files, you should find it in this paths
C:\Program Files (x86)\SharePointPnPPowerShellOnline\Modules\SharePointPnPPowerShellOnline\SharePointPnPPowerShellOnline.psd1 


$pnpmodulepath="C:\Program Files (x86)\SharePointPnPPowerShellOnline\Modules\SharePointPnPPowerShellOnline\SharePointPnPPowerShellOnline.psd1"
Import-Module $pnpmodulepath -Force
#Specify tenant admin and site URL
$User = "admin.tenat@withavanade.com"
$Password = ConvertTo-SecureString -String "password" -AsPlainText -Force
$Tenant = "https://o365accelerator-admin.sharepoint.com"
$CurrentCred = New-Object Management.Automation.PSCredential ($User, $Password)
Connect-PnPOnline -TenantAdminUrl $Tenant -Url $Tenant -Credentials $CurrentCred
$AllSites = Get-PnPTenantSite
foreach ($site in $AllSites )
{
if($site.Url.Contains("yourcompany.sharepoint.com")) #This if condition to dismiss public sites, but you can remove it
{
Connect-PnPOnline -Url $Site.Url -Credentials $CurrentCred
Write-Host ($site.Url +";"+
$site.Title +";"+
$site.Lcid +";"+
$site.LastContentModifiedDate +";"+
$site.LockState +";"+
$site.StorageMaximumLevel +";"+
$site.StorageUsage +";"+
"")
Disconnect-PnPOnline
}
}