powershell
PowerShell List Big Files
By fredericdietrich On 07/04/2023
Get Sharepoint all Big files with powershell and Sharepoint Search
Import-Module PnP.PowerShell
# Connect-PnPOnline -Url $url -UseWebLogin
$select = "Title,SiteTitle,SPSiteURL,SPWebUrl,ViewsLifeTimeUniqueUsers,ViewsLifeTime,language,Created,Size,Path,LastModifiedTime,ViewsLastMonths3,ViewsLastMonths3Unique,LastAnalyticsUpdateTime,ViewerCount"
$sortlist = @{Size = 'descending' }
$siteName = "serviceanimationreseauFPFSITW"
$search = Submit-PnPSearchQuery -Query "ContentTypeId:0x0101*" -MaxResults 500 -SelectProperties $select -TrimDuplicates $true -SortList $sortlist -StartRow 0
$ret = $search.ResultRows.Count
$count = 0
$stop = $false
$itemsToSave = @();
while ($ret -eq 500 -and -not $stop) {
# Disconnect-PnPOnline
# -MaxResults 10
$lastSize = 0
foreach ($row in $search.ResultRows) {
$mo = 0;
[Int64]::TryParse($row["Size"], [ref] $mo ) | Out-Null
$nice = '{0:N0}' -f $mo
$data = [PSCustomObject]@{
"Title" = $row["Title"]
"Size" = $row["Size"]
"SizeMo" = '{0:N0}' -f ($mo / 1000000)
"NiceSize" = $nice
"SiteTitle" = $row["SiteTitle"]
"ViewsLifeTimeUniqueUsers" = $row["ViewsLifeTimeUniqueUsers"]
"ViewsLifeTime" = $row["ViewsLifeTime"]
"language" = $row["language"]
"Created" = $row["Created"]
"LastModifiedTime" = $row["LastModifiedTime"]
"ViewsLastMonths3" = $row["ViewsLastMonths3"]
"ViewsLastMonths3Unique" = $row["ViewsLastMonths3Unique"]
"LastAnalyticsUpdateTime" = $row["LastAnalyticsUpdateTime"]
"ViewerCount" = $row["ViewerCount"]
"Path" = $row["Path"]
"SPSiteURL" = $row["SPSiteURL"]
"SPWebUrl" = $row["SPWebUrl"]
}
$lastSize = $data.Size
if ([int64]::Parse($data.Size) -lt 10000000) {
$stop = $true;
break;
}
$itemsToSave += $data
}
$count += 500
$search = Submit-PnPSearchQuery -Query "ContentTypeId:0x0101*" -MaxResults 500 -SelectProperties $select -TrimDuplicates $true -SortList $sortlist -StartRow $count
$ret = $search.ResultRows.Count
Write-Host "$($itemsToSave.Count) $($count) $($lastSize)"
}
$itemsToSave | Export-Csv -Path "AllSites_8.csv" -Encodin:UTF8 -NoTypeInformation -Delimiter ";"
Disconnect-PnPOnline
Get Sharepoint Audit Logs
By fredericdietrich On 05/04/2023
Create certification for azure app
# Create certificate
$mycert = New-SelfSignedCertificate -DnsName "myCertificate.org" -CertStoreLocation "cert:\CurrentUser\My" -NotAfter (Get-Date).AddYears(1) -KeySpec KeyExchange
$mypwd = ConvertTo-SecureString -String "myCertificatePsw" -Force -AsPlainText
# Export certificate to .pfx file
$mycert | Export-PfxCertificate -FilePath myCertificate.pfx -Password $mypwd
# Export certificate to .cer file
$mycert | Export-Certificate -FilePath myCertificate.cer
Connect to site
$url = "https://m365x6422vvvvd.sharepoint.com/";
$appId = "868d7a0c-a3dc-45af-b4a7-f72a70f61a60";
$thumbprint = "A17177BB0E8A465F6AD08B0CEAE2F369C46D6481";
$tenantId = "3533ab30-c2f0-48fd-b4c5-f5dc6ca77ec3"
Connect-PnPOnline -Url $url -Tenant $tenantId -Thumbprint $thumbprint -ClientId $appId
Export audit
premission required
Office 365 Management APIs (3) :: ActivityFeed.Read
possible filters
- FilePreviewed
- FileAccessed
- SignInEvent
- FileModifiedExtended
- FileUploaded
- PageViewed
- PagePrefetched
- FileCheckedIn
- FileModified
- FolderCreated
- ListUpdated
- ListViewed
$ele = Get-PnPUnifiedAuditLog -ContentType SharePoint -StartTime (Get-Date).AddDays(-2) -EndTime (Get-Date).AddDays(-1)
$ele = Get-PnPUnifiedAuditLog -ContentType SharePoint
$ele = Get-PnPUnifiedAuditLog -ContentType SharePoint | Where-Object {$_.Operation -eq "PageViewed"} | Select-Object CreationTime,Operation,Workload,UserId,ObjectId,SourceFileName,SiteUrl,SourceFileExtension,SourceRelativeUrl
$ele | Export-Csv -Path "Audit_3.csv" -Encodin:UTF8 -NoTypeInformation -Delimiter ";"
with sharepoint search request
kqlQuery = "ContentTypeId:0x0101009D1CB255DA76424F860D91F20E6C4118*";//news
kqlQuery = "ContentTypeId:0x0101* language=fr ViewsLastMonths3=0";//documents
kqlQuery = "ContentTypeId:0x0101* ViewsLifeTime=0";
var seletvvv = "Title,ViewsLifeTimeUniqueUsers,ViewsLifeTime,language,Created,Size,Path,LastModifiedTime,ViewsLastMonths3,ViewsLastMonths3Unique,LastAnalyticsUpdateTime";
&sortlist='Size:descending'
&sortlist='ViewsLifeTime:descending'
Powershell Import Export Fields
By fredericdietrich On 03/02/2023
Write-Output 'Connexion Portail'
# Connect-PnPOnline -Url $siteUrl -Tenant $aadDomain -Thumbprint $certifThumbprint -ClientId $appId
function ExportFieldsToCsv {
Param(
[string]$listTitle,
[string]$csvFilename,
[boolean]$includeHidden = $false
)
#$list = Get-PnPList -Identity $list
if ([string]::IsNullOrEmpty($listTitle)) {
$fields = Get-PnPField
$aFieldToExports = @();
for ($i = 0 ; $i -lt $fields.Length ; $i++) {
$field = $fields[$i];
$aFieldToExport = New-Object -TypeName PSObject -Property @{
'Title' = $field.Title;
'InternalName' = $field.InternalName;
'Id' = $field.Id;
'TypeAsString' = $field.TypeAsString;
'Hidden' = $field.Hidden;
'StaticName' = $field.StaticName;
'Required' = $field.Required;
'Description' = $field.Description;
'TypeDisplayName' = $field.TypeDisplayName;
'Group' = $field.Group;
}
$aFieldToExports += $aFieldToExport
$aFieldToExports | Export-Csv -Path "$($csvFilename)" -Encodin:UTF8 -NoTypeInformation -Delimiter ";"
}
}
else {
<# Action when all if and elseif conditions are false #>
}
}
function ExportFieldsToXml {
Param(
[string]$listTitle,
[string]$xmlFilename,
[boolean]$includeHidden = $false
)
#$list = Get-PnPList -Identity $list
if ([string]::IsNullOrEmpty($listTitle)) {
$fields = Get-PnPField
$stringBuilder = New-Object System.Text.StringBuilder
$null = $stringBuilder.AppendLine("")
for ($i = 0 ; $i -lt $fields.Length ; $i++) {
$field = $fields[$i];
$null = $stringBuilder.AppendLine($field.SchemaXml);
}
$null = $stringBuilder.AppendLine("")
$stringBuilder.ToString() | Out-File -FilePath "$($xmlFilename)" -Encodin:UTF8 -Append
}
}
function ImportFields {
Param(
[string]$listTitle,
[string]$xmlFilePath,
[boolean]$includeHidden = $false
)
[XML]$xmlfile = Get-Content -Path $xmlFilePath -Encoding:UTF8
if ([string]::IsNullOrEmpty($listTitle)) {
foreach ($field in $xmlfile.fields.Field) {
Write-Host $field.Name
$exists = Get-PnPField -Identity $field.Name -ErrorAction:SilentlyContinue
if ($null -eq $exists) {
Write-Host "$($field.Name) is null"
Add-PnPFieldFromXml -FieldXml $field.OuterXml
Write-Host "$($field.Name) added"
}
else {
Write-Host "$($field.Name) exists"
}
}
}
else {
foreach ($field in $xmlfile.fields.Field) {
$siteField = Get-PnPField -Identity $field.Name -ErrorAction:SilentlyContinue
$listField = Get-PnPField -Identity $field.Name -List $listTitle -ErrorAction:SilentlyContinue
if ($null -eq $siteField -and $null -eq $listField) {
Write-Host "$($field.Name) is null"
Add-PnPFieldFromXml -FieldXml $field.OuterXml
Add-PnPField -List $listTitle -Field $field.Name
Write-Host "$($field.Name) added"
}
elseif($null -eq $listField){
Write-Host "$($field.Name) exists"
Add-PnPField -List $listTitle -Field $siteField.InternalName
Write-Host "$($field.Name) added to $($listTitle)"
}
}
}
}
Add WebPart On Modern Page PNP Powershell
By fredericdietrich On 07/05/2021
Add custom WebPart On Modern Page Powershell
List your webpart with :
$page = Get-PnPPage -Identity "aTestDisp2.aspx"
$page.AvailablePageComponents() select your webpart by name or guid
param([string]$siteUrl="https://myyTenant.sharepoint.com/sites/muSite", [string]$pageName="aTestDisp2", [string]$webPartName="Test - Display Item") clear Connect-PnPOnline -Url $siteUrl -UseWebLogin #set your wenpart properties $wpProps = @{webUrl="https://myyTenantsharepoint.com/sites/
muSite" listId="b4d5f780-bd74-4cd5-b051-fed423e43125" field="Title" itemId="1"} #get the page $page = Get-PnPPage -Identity $pageName # get the target webpart $wp = $page.AvailablePageComponents() | Where-Object {$_.Name -eq $webPartName } if($wp -eq $null) { Write-Host "webpart '$($webPartName)' not found " -ForegroundColor:Red } #Add webpart with default user, to column 1 in section 1 Add-PnPPageWebPart -Page $pageName -Component $wp -Section 1 -Column 1 -WebPartProperties $wpProps
Disconnect-PnPOnline
You can check webpart properties, in result
PowerShell Csom Batch Delete / delete all list items
By fredericdietrich On 09/03/2021
Powershell CSOM clear list items / delete all items
clear
# Connect-PNPOnline -url $ProdWaveplaceURL -UseWebLogin -ErrorAction Stop
# Disconnect-PnPOnline
$listTitle = "testTaxo"
$list = Get-PnPList -Identity $listTitle -ThrowExceptionIfListNotFound
$ctx = Get-PnPContext
$page = $null
$pageNumber = 0;
$rowLimit = 100
$startDate = Get-Date
$deletedItemsCount = 0;
Do{
$stringBuilder = New-Object System.Text.StringBuilder
$stringBuilder.Append("<View scope='RecursiveAll'>") | Out-Null
$stringBuilder.Append("<Query><Where></Where>")| Out-Null
$stringBuilder.Append("<Orderby><Fieldref ascending='TRUE' name='ID'></Fieldref></Orderby>")| Out-Null
$stringBuilder.Append("</Query>")| Out-Null
$stringBuilder.Append("<Viewfields>")| Out-Null
$stringBuilder.Append("<Fieldref name='ID' />")| Out-Null
$stringBuilder.Append("</Viewfields>")| Out-Null
$stringBuilder.Append("<Rowlimit paged='TRUE'>$($rowLimit)</Rowlimit>")| Out-Null
$stringBuilder.Append("</View>")| Out-Null
$spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$spqQuery.ViewXml = $stringBuilder.ToString();
$spqQuery.ListItemCollectionPosition = $page
$pageNumber ++;
$spqQuery.ViewXml = $stringBuilder.ToString();
$itemki=$list.GetItems($spqQuery);
$spqQuery.ListItemCollectionPosition = $itemki.ListItemCollectionPosition
$ctx.Load($itemki)
$ctx.ExecuteQuery();
Write-Host "################## PAGE " $($page.PagingInfo) " #########################"
Write-Host "processing query results. Recs: $($itemki.Count)"
$Counter = $itemki.Count;
if($itemki.Count -eq 0){
exit 0
}
do{
$itemki[$Counter - 1].DeleteObject()
$Counter--
$deletedItemsCount++
}while($Counter -gt 0)
Invoke-PnPQuery
Write-Host "deletedItemsCount $($deletedItemsCount)"
$page = $itemki.ListItemCollectionPosition
$comp = $endDate - $startDate
$endDate = Get-Date
Write-Host "time to delete $($rowLimit) elements : Days '$($comp.Days)' Hours '$($comp.Hours)' Minutes '$($comp.Minutes)' Seconds '$($comp.Seconds)'"
}
Until($page -eq $null)
$comp = $endDate - $startDate
Write-Host "time to delete : Days '$($comp.Days)' Hours '$($comp.Hours)' Minutes '$($comp.Minutes)' Seconds '$($comp.Seconds)'"
Export List Fields To Csv With Pnp Powershell
By fredericdietrich On 21/01/2021
Export List Fields To Csv With Pnp Powershell
param($url="https://myTestSite", $listTitle="/Lists/aList")
clear
#connect to your site using windows identity manager
Connect-PnPOnline -Url "$($url)" -Credentials 'myWindowsOrWebIdentity'
$list = Get-PnPList -Identity $listTitle -Includes Fields
function ExctractFieldsNoContentType()
{
param($targetList)
$targetList.Title
$toExport = @()
for($i = 0 ; $i -lt $targetList.Fields.Count ; $i++)
{
$field = $targetList.Fields[$i]
#put in an object some field properties
$aFieldToExport = New-Object -TypeName PSObject -Property @{
'Title' = $field.Title
'InternalName' = $field.InternalName
'TypeAsString' = $field.TypeAsString
'Hidden' = $field.Hidden
'Group' = $field.Group
'Required' = $field.Required
'Description' = $field.Description
}
$toExport += $aFieldToExport
}
#export to csv
$toExport | Export-Csv -Path "$($targetList.Title)_fields.csv" -Encodin:UTF8 -NoTypeInformation -Delimiter ";"
}
ExctractFieldsNoContentType -targetList $list
Disconnect-PnPOnline
manage identities in
CSOM powershell SSL TSL site
By fredericdietrich On 23/11/2019
Exception calling "ExecuteQuery" with "0" argument(s): "The request was aborted: Could not create SSL/TLS secure channel." SOLVED
Exception lors de l'appel de ExecuteQuery" avec 0" argument(s): La demandea été abandonnée: Impossible de créer un canal sécurisé SSL/TLS." : résolu
How to connect to a SSL/ TSL sharepoint site with CSOM in powershell
Start to call a request with your certificate
$CertificateThumbprint = "53836D3C35F949959D7E4038D5D39D7B"
$response = Invoke-WebRequest -Verbose -URI $anUrl -CertificateThumbprint $CertificateThumbprint -UseDefaultCredentials -SessionVariable websession -ErrorAction:Stop
# in this ExecutingWebRequest you should add the certificate to yoour request and add the authentification cookie
$request = $EventArgs.WebRequestExecutor.WebRequest
to list all your available certificate execute following script
Powershell Csom Paged Caml Query
By fredericdietrich On 18/09/2019
$list = Get-PnPList -Identity $listTitle -ThrowExceptionIfListNotFound
$ctx = Get-PnPContext
$page = $null
$pageNumber = 0;
$rowLimit = 200
Do{
$stringBuilder = New-Object System.Text.StringBuilder
$stringBuilder.Append("<View Scope='RecursiveAll'>") | Out-Null
$stringBuilder.Append("<Query><Where></Where>")| Out-Null
$stringBuilder.Append("<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>")| Out-Null
$stringBuilder.Append("</Query>")| Out-Null
$stringBuilder.Append("<ViewFields>")| Out-Null
$stringBuilder.Append("<FieldRef Name='ID' />")| Out-Null
$stringBuilder.Append("<FieldRef Name='Title' />")| Out-Null
$stringBuilder.Append("</ViewFields>")| Out-Null
$stringBuilder.Append("<RowLimit Paged='TRUE'>$($rowLimit)</RowLimit>")| Out-Null
$stringBuilder.Append("</View>")| Out-Null
$spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$spqQuery.ViewXml = $stringBuilder.ToString();
$spqQuery.ListItemCollectionPosition = $page
$pageNumber ++;
$spqQuery.ViewXml = $stringBuilder.ToString();
$itemki=$list.GetItems($spqQuery);
$spqQuery.ListItemCollectionPosition = $itemki.ListItemCollectionPosition
$ctx.Load($itemki)
$ctx.ExecuteQuery();
Write-Host "################## PAGE " $($page.PagingInfo) " #########################"
Write-Host "processing query results. Recs: $($itemki.Count)"
$Counter = 0;
foreach($item in $itemki)
{
Write-Host "$($item["ID"]) title pageNumber '$($pageNumber)' : $($item["Title"])"
}
$page = $itemki.ListItemCollectionPosition
}
Until($page -eq $null)
Add SPList With CSOM
By fredericdietrich On 15/08/2019
CSOM ISSUE format-default
By fredericdietrich On 21/02/2019
format-default : The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.
PowerShell Csom load property Issue
By fredericdietrich On 18/01/2019
Using Microsoft.SharePoint.Client.dll you can have issue after object loading
$file = $oWeb.GetFileByUrl($fileUrl);
$context.Load($file);
$context.ExecuteQuery()
$context.Load($file.ListItemAllFields)
$context.ExecuteQuery()
$file.ListItemAllFields #accessing to this property will thow exception below
format-default : The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.
so you have to store property in a variable like that
$ListItemAllFields = $file.ListItemAllFields
$context.Load($ListItemAllFields)
$context.ExecuteQuery()
Change Files Extentsions
By fredericdietrich On 23/11/2018
Powershell Paged Caml Query
By fredericdietrich On 16/11/2018
Set Content Type Field Property
By fredericdietrich On 22/10/2018
Powershell Upgrade SPFeature
By fredericdietrich On 12/10/2018
Powershell Get SPList Last Modified
By fredericdietrich On 11/10/2018
Add task to reset sharepoint search index with powershell
By fredericdietrich On 05/10/2018
Create a powershell script (task1.ps1) with code below
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null)
{
Add-PSSnapin Microsoft.SharePoint.PowerShell -Verbose -ErrorAction:Stop
}
#the $True parameter denotes: Disable Alerts and Ignore Timeout error.
(Get-SPEnterpriseSearchServiceApplication).reset($true, $true)
Add a new task in your task scheduler
Check the radio button 'Run whether user is logged on or not
Set a name and add a trigger to set scheduling
set the action : program / script
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe
argument with -command followed by the pass to your powershell script
-executionpolicy unrestricted -command D:\PS1\Task1.ps1
Save your task
PowerShell Export List Fields To Xml
By fredericdietrich On 01/10/2018
Sharepoint PowerShell query search service
By fredericdietrich On 29/09/2018
SharePoint PowerShell query search service
Sharepoint Execute Job With Powershell
By fredericdietrich On 28/09/2018