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 !

Check if PowerBI Workspace exists using Get-UnifiedGroup PowerShell Command

Posted on Updated on

We will validate if Certian PowerBI workspace exists or not using Get-UnifiedGroup PowerShell Command in this link

The Get-UnifiedGroup is a little bit tricky because you cannot Load it unless you setup a Microsoft.Exchange connection through O365, and after than you can use the Get-UnifiedGroup command

The below PowerShell script is connected to O365 Exchange then get the unified Group.

Any PowerBI Workspace created is associated with O365 Group, If the workspace is deleted for any reason, the O365 Group will be deleted accordingly.
The Group Id should be part of the Workspace PowerBI Url as follow:

$Username = "admin@tenant.onmicrosoft.com"
$Password = "P@ssw0rd"
[System.Net.WebRequest]::DefaultWebProxy.Credentials = [System.Net.CredentialCache]::DefaultCredentials
$creds = New-Object System.Management.Automation.PSCredential($Username,(ConvertTo-SecureString $Password -AsPlainText -Force));
$session = New-PSSession -ConfigurationName Microsoft.Exchange -Authentication Basic -ConnectionUri https://ps.outlook.com/powershell -AllowRedirection:$true -Credential $creds
Import-PSSession $session -AllowClobber
Get-UnifiedGroup -Identity 05cb4ff7-7bcb-4f71-a8f9-f9135ed7a577

Please leave a comment if you faced any issues while running this script

How to Remove Duplicates items in SharePoint Online List and keep one by PowerShell ?

Posted on Updated on

Hello folks,

We have faced recently a critical situation with a customer:
He was running a certain PowerShell script to update a SharePoint Online list with Data, it reaches 7000 items, a certain issue occurred in the script, and he found that he as 20,000 items in the same list, 70% was duplicates.

He wants to solve the situation in a safe way with no risk, by removing duplicate items and keep the Original one.
To solve the situation I did the following, This solution can be applied on all SharePoint versions 2010, 2013, 2016, & SharePoint Online, as the script is written to work as Client Object Model.

      1. I took back-up from the List, if you use SharePoint On-premise version, you can take back-up of the site. If you are using SharePoint Online, you can use Sharegate, however this step is not mandatory.
      2. Export AllItems to Excel using the Excel action in the ribbon, and make sure the ID Column is part of the export.
      3.  Download and setup a free tool named Kutools for Excel from here , the page contains also description how to use it to mark the duplicated column.
      4. Now take of the IDs from the Excel, and paste it in a text file on your harddrive
        Ids
      5. Then run the below PowerShell Script
        Add-Type -Path "C:\Path\SPOnline\Microsoft.SharePoint.Client.dll"
        Add-Type -Path "C:\Path\SPOnline\Microsoft.SharePoint.Client.Runtime.dll"
        $siteurl = "https://tenant.sharepoint.com/sites/yourSite/"
        $UserName = "siteadmin@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)
        $context = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)
        $context.Credentials = $SPOCredentials
        $web = $context.Site.RootWeb
        $context.Load($web)
        $context.Load($web.Lists)
        $context.ExecuteQuery()
        $listTitle = "List Name"
        $list = $Context.Web.Lists.GetByTitle($listTitle)
        $Context.Load($list)
        $Context.ExecuteQuery()
        $TargetSites = Get-Content "C:\temp\List-of-Ids.txt"
        foreach ($targetSite in $TargetSites)
        {
        $id=$targetSite.Split(";")[0]
        $caml = @"<View Scope="RecursiveAll"><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy><Where><Eq><FieldRef Name='ID' /><Value Type='Number'>$id</Value></Eq></Where></Query><RowLimit Paged="TRUE">1</RowLimit></View>"
        $query = new-object Microsoft.SharePoint.Client.CamlQuery
        $query.ViewXml = $caml
        $items = $list.GetItems($query)
        $Context.Load($items)
        $Context.ExecuteQuery()
        if ($items.Count -gt 0)
        {
        for ($i = $items.Count-1; $i -ge 0; $i--)
        {
        Write-Host "Deleted: " $items[$i].Id.ToString() -ForegroundColor Yellow
        }
        $Context.ExecuteQuery()
        }
        }
        #$context.Dispose()

     

    Now, the items will start to be deleted, you can change the PowerShell script to fits your need.
    This method is safe because you see exactly what do you want to delete before executing the deletion script

Display PowerShell Full Exception Message with (Out-String)

Posted on Updated on

I was requested to write a detailed PowerShell Script that can log the Caught exception as string.

Actually it wasn’t easy as it looks like at first.

I tried to use write-host with a lot of Exception properties, as below, and all didn’t work as expected, I never found the error location as the Out-of-the-box PowerShell exception.

This is what I expected to see, detailed Exception with location error

then I came across one article mentioned that I need to use Out-String during the catch { }, this gives me exactly the details for the  exception I’m looking for

try
{
$v= 9/0
}
catch
{
write-host ($_ | Out-String) -ForegroundColor Red
}

Now the Exception looks really detailed and meaningful.

Create Test Microsoft O365 Group and Delete it

Posted on Updated on

If you are involved in rolling-out of Microsoft Teams or groups recently, then you may need a test script to create temporary O365 Groups
Using the below PowerShell script, you will be able to do so

Pre-requisites:
——————
You should have :

  1. Client Id
  2. Client Secret
  3. tenant admin Url & Credentials

function Test-Group {
##Get Access Token
$body = @{
scope="https://graph.microsoft.com/.default";
client_id=$AppID;
client_secret=$AppSecret;
grant_type="client_credentials";
}
$accessToken = (Invoke-RestMethod `
-Uri https://login.microsoftonline.com/$AADDomain/oauth2/v2.0/token `
-Method POST `
-Body $body `
-Headers @{"Accept" ="application/json"; "Content-Type"="application/x-www-form-urlencoded"}).access_token
##Create Group
$body = @{
displayName="$prefix$suffix";
description="$prefix$suffix";
groupTypes= @("Unified");
mailEnabled="true";
mailNickname="$prefix$suffix";
securityEnabled="false";
visibility="Private";
#"owners@odata.bind"=@("https://graph.microsoft.com/v1.0/users/5a97df74-a0c9-4fb9-8f9f-26bd29d4bf06");
}| ConvertTo-Json
$NewGroup = (Invoke-WebRequest `
-Uri https://graph.microsoft.com/v1.0/groups `
-Method POST `
-Body $body `
-Headers @{"Authorization" = "Bearer $accessToken"; "Accept" ="application/json"; "Content-Type"="application/json"} )
$NewGroupId = ($NewGroup.Content | ConvertFrom-Json).Id
##Check if group was provisioned
$success = $false
$time = [System.Diagnostics.Stopwatch]::StartNew()
for ($i=0; $i -lt 60; $i+=5) {
Start-Sleep -s 5
try{
Invoke-WebRequest `
-Uri https://graph.microsoft.com/v1.0/groups/$NewGroupId/drive `
-Headers @{"Authorization" = "Bearer $accessToken"; "Accept" ="application/json";} | Out-Null
$success = $true
break
}catch [System.Net.WebException]{
If ($_.Exception.Response.StatusCode.Value__ -ne 404){
throw
}
}
}
if($success){
##Delete Group, you can leave it or keep it based on your needs
# Invoke-RestMethod `
# -Uri https://graph.microsoft.com/v1.0/groups/$NewGroupId `
# -Method Delete `
# -Headers @{"Authorization" = "Bearer $accessToken"; "Accept" ="application/json";} | Out-Null
} else {
Write-Host ModernGroup failed to provision in ([Math]::floor($time.Elapsed.TotalSeconds)) seconds -ForegroundColor Red
Write-Host GroupId: $NewGroupId -ForegroundColor Red
Write-Host CorrelationId: $NewGroup.Headers.'request-id' -ForegroundColor Red
Write-Host Date: $NewGroup.Headers.'Date' -ForegroundColor Red
}
}
$AppID = 'xxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxxx' #Put your AppId
$AppSecret = '**********************************' #Put your Secret
$AADDomain = 'yourtenant.onmicrosoft.com'
for ($i=0; $i -lt 1; $i++) {
$suffix = (Get-Date).Ticks
$prefix = "MSFT_Test_"
Test-Group
}

At the End you will have a test group created

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)