updated

Remove Unique Permissions

Remove Unique Permissions

Long ago, it was a common requirement for a client to add unique permission for SharePoint files or list items.

Can you guess the function that adds unique permission?

BreakRoleIneritance

However, it decreased the performance and that can be a very bad choice.

Below is a tool I did that removes  the unique permissions for a SharePoint library in a SharePoint site for Online version.

Demo

Tool Link:
https://payhip.com/b/GgQO

If you want the above tool for a discount price, apply the code

6307EIT6ZW

Please make sure you test on a test library before running on production.

If you have many such requests, you may contact for a free trail run of one PowerShell script on your environment by submitting a request on
https://forms.office.com/Pages/ResponsePage.aspx?id=VeQH6wiZhkyj0EhqSzeQ2AF7ASEEeOpCqBFwiSc3gXVUM09GTTJEODQ5UTVLOUJFRkZFTUdYRVhXOC4u

If you need similar request for another version of SharePoint, please contact on www.thangu.com

How to create a certificate for EXE on Windows 10

Steps:

1. Download the Windows 10 SDK
https://developer.microsoft.com/en-US/windows/downloads/windows-10-sdk/

2. Open Command Prompt CMD as Administrator

Go to the folder with Sign Tool
C:\Program Files (x86)\Windows Kits\10\App Certification Kit


signtool sign /a MyFile.exe  TO get a valid certificate try the link https://www.certum.eu/en/cert_offer_en_open_source_cs/

Get Windows Details

Get Windows Details

Below is a short script to get Windows Edition

$windowsDetails=Get-WmiObject -Class Win32_OperatingSystem |select Caption

If you are interested in more Windows related commands, see here 

Here is a sample video of the script that allows you to display the report as shown below:

http://screencast-o-matic.com/watch/cYnt0Bw9i0

You can access the running tool from here:

https://payhip.com/b/VJbG

If you have a custom requirement like this, mention in the comment box and I will try to update the scripts to your custom requirement when I am free.

If you have any other custom report requirements for other objects like Office 365,SharePoint, SQL or any other System, you may contact https://calendly.com/spthangu for Professional consulting for small requirements that take 5mins to 1 hour max for a small charge of 10$-50$.

PowerShell Here String

Here String

This is a short blog on Here String. I decided to write down about this since I wanted to share the experiences with PowerShell features, that I heavily use.

I commonly use the following type of code to store large strings in a variable.

 

$data=@"
{
  "@odata.id": "https://graph.microsoft.com/v1.0/directoryObjects/$($userID)"
}
"@

Why do I use the above instead of using a normal string?

$data="{
  "@odata.id": "https://graph.microsoft.com/v1.0/directoryObjects/$($userID)"
}"

Reasons:

1.The above code throws error due to the presence of string with double quotes

2. Using back ` is terrible for strings with many double quotes .

What was I doing to allow double quotes and have large strings declared in a variable?

I used @ at the end and beginning of the string and the string was able to store large data.

I did not know that the term used for this feature was Here string until I read various msdn blogs on PowerShell and came to know the Here String term.

What is Here String?

A simple String that allows you to declare multi ling string along with Quotes and variable values.

Make sure that you have a line break after the first @", else the following command throws error:

$HereString=@"My
"life"
is
in
my hands"@

The following code works correctly

$when="Always"
$HereString=@"
My
"life"
is
in
my hands $when
"@

Below the output:

My
"life"
is
in
my hands Always

Office 365 and PowerShell Mails

PowerShell and Office 365

Office 365 is a great product and there are nice ways to manage them with PowerShell itself.

Lets get started by connecting.

#Connect to Exchange
#Set-ExecutionPolicy RemoteSigned
#https://docs.microsoft.com/en-us/powershell/exchange/exchange-online/connect-to-exchange-online-powershell/connect-to-exchange-online-powershell?view=exchange-ps
$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session -DisableNameChecking

Once, you connect, see the Mailbox

Get-Mailbox

I

SharePoint CSOM Dashboard- Thangu

SharePoint CSOM to add data from CSV

PowerShell and Excel

PowerShell is very simple and powerful. Microsoft Office products like Word, Excel, PowerPoint has changed the world and become a part of every household.Advanced COM programming is possible, which I use for advanced operations on Word. Lets see how PowerShell is helpful with  single line code with Excel.

Command Available
Export-CSV
$data| Export-csv -Path $csvPath -Append

Import-CSV
$newFruits=Import-Csv -Path $csvPath

Let is say you have a Fruits data along with color information, we will display the fruits information in Excel as shown below:

$yourFolder="C:\demo\thangu"
$csvPath=$yourFolder+"\fruitsCsv.csv"
#New powerShell Table
$hashContent = [PSCustomObject]@{Name="Apple";Color="Red"}
$hashContent | Export-csv -Path $csvPath -Force
$hashContent = [PSCustomObject]@{Name="Mango";Color="Yellow"}
$hashContent | Export-csv -Path $csvPath -Append

$newFruits=Import-Csv -Path $csvPath

Sample Application to move data to external system(SharePoint)

SharePoint List Addition
Lets try to add some items from CSV to SharePoint List having fruits data.I used CSOM to read the data from CSV file created above and used the following CSOM code to move the data from CSV to Fruits list.

foreach($fruit in $newFruits){
$newItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$newItem=$psList.AddItem($newItemInfo)
$newItem["Title"] = $fruit.Name
$newItem["Color"] = $fruit.Color
$newItem.Update()
$Context.ExecuteQuery()
}

Resting in Peace with SharePoint Rest Apis

Introduction

Are you tired of writing many lines of code to do CRUD functionality? Lets take the simple case of trying to Read a SharePoint List and add items to a SharePoint list.You would have to write two functions using CSOM/JSOM/SSOM:

1. ReadList() 
2. AddListItem()

With the advent of REST in SharePoint, you can focus on your functionality and not on the code/language. All you have to do is to construct the URL based on your needs and reuse the REST CRUD code.

SharePoint team has done a lot of coding and made common functionality available for access from a URL.

You append the SharePoint site URL with REST Url.

Construction of REST Url

The REST Url begins with /_api/

After the api, you specify the Object. Example web, site. For nested objects /web/lists

Lets begin with a simple Url that reads the Site Url.

First, you get the Site Collection  Url and then append that with the Rest Url

Example:

https://abc.sharepoint.com/sites/dev/_api/site/Url

If you open the above Url, you would see a output like below:

<d:Url>https://abc.sharepoint.com</d:Url>

The results are mostly accessed by data.d or data.d.results for multiple items

If you are new to REST, here is a quick explanation on REST basics. REST technology allows developers to write functions using any technology of their choice and publish that as a URL which does the following tasks for a resource/object:

GET- Read the object and returns the results mostly in JSON/Xml format

POST- Get the data details and Add Items

PUT - Update the Object 

DELETE - Delete the Object

SharePoint and PowerShell code to do REST

Lets use PowerShell to get connected to SharePoint URL and do various Read operations. The cool thing is there is only one PowerShell code and the URL alone changes. In regular CSOM coding, you need to remember the Class and the methods to get your job done. in REST, the code remains the same and the URL alone changes.

You can download the code from here.

Here is a quick summary of the PowerShell code to do the CRUD operations.

1. Get connected to SharePoint using SharePoint credentials
2. Add the necessary headers to $headers based on CRUD operation
   1. GET- "Accept", "application/json;odata=verbose"
   2. POST- "X-RequestDigest", $formDigest from /_api/contextinfo
   3. PUT- "X-HTTP-Method", "PUT" or "MERGE"
   4. PUT- "X-HTTP-Method", "DELETE"
3. Invoke-RestMethod -Uri $fullUrl -Headers $headers -Method Post -WebSession $webSession -ContentType "application/json;odata=verbose"

Once, you run the above code, you can start using Read-SPObject,Create-SPObject,Update-SPObject on various SharePoint objects like Site, Lists,Files and many more based on your needs.

Add List Items using REST

Lets start with our original need to read a SharePoint List and add Items to a SharePoint list say PSRestList. 

$targetSite="https://xxx.sharepoint.com/sites/dev"
$User="admin@xxx.onmicrosoft.com"
$restUrl="/_api/web/lists/getbytitle('PSRestList')"
$listsPSRest=Read-SPObject -targetSite $targetSite -User $User -restUrl

Below are the details needed to add a item to a SharePoint list. Note that the data part contains the List Name, List Columns like Title.

#Add List Item
$listName="PSRestList"
$data=@"
{ '__metadata': { 'type': 'SP.Data.$($listName)ListItem' }, 'Title': 'OneTwoThree','CurrencyThangu':'2500' }
"@
$restUrl="/_api/web/lists/GetByTitle('"+$listName+"')/items"
$listItemNew=Create-SPObject -targetSite $targetSite -User $User -restUrl $restUrl -data $data

Summary

As a quick summary, you learnt the following:

1. Basics of REST and its benefits
2. Construction of SharePoint REST Url
3. PowerShell code that Invokes the SharePoint URL
4. Adding a SharePoint List Item using the REST Url.

If you like to learn by watching videos, here is a sample REST demo.

JSON Requests in PowerShell

PowerShell is extremely powerful. I am a trainer and consultant in various technologies like PowerShell, SharePoint, Javascript etc and no matter what the technology is, what is crucial for any technology? DATA

Data can be exchanged in various formats like Xml, Variables in parameters in URL.

What is the most recent way to exchange data? JSON

JSON is a different way of expressing the data. Lets take the example of object Apple.
The Apple is a fruit red in color.

Fruit named Apple
In computer language, we can express that as

var fruit;
fruit="apple";

For additional details, JSON comes to the rescue where color description can be given.

fruits="{'Name':'Apple','Color':'Red'}"

In Powershell, we can read JSON easily using ConvertTo and ConvertFrom Json function.PowerShell is very simple compared to other programming languages. One line of code!

Below is a script that converts the fruits JSON and displays that in beautiful table format.
$fruits="{'Name':'Apple','Color':'Red'}","{'Name':'Mango','Color':'Yellow'}"

$fruits |ConvertFrom-Json

In real world, I heavily use SharePoint and there are times when the JSON results are not converted sometimes. In that case, I have to do minor tweaks to display the JSON data in powershell. Below is a code that reads REST results from SharePoint, but the tabular data is not displayed.

$listName="PSRestList"
$restUrl="/_api/web/lists/GetByTitle('"+$listName+"')/items"
$listItems=Read-SPObject -targetSite $targetSite -User $User -restUrl $restUrl
$listItems

How to handle the error? By replacing the conflicting columns with another column.
#ID error
$results = $listItems.ToString().Replace("ID", "_ID") | ConvertFrom-Json
$results.d.results|select Title

Updating Registry Values

PowerShell is simply superb. Windows changed the world by giving a computer for every Home. I love Windows and prefer Windows over Mac, since looks are always deceptive.

As a kid, I used to play on Windows games like Xatax and enjoyed BASICs programming in school. I had to type in the command prompt to login to Windows. Windows can be modified using Registry values. Just as A,B,C is fundamental to English. Registry values are low level Windows Fundamentals. This contains many Windows Configuration details.

You can open the Registry Editor by typing "regedit" in the command prompt(Start->Run). You are advised not to change any settings unless you are an administrator. In case , you are changing, take a backup and then change.The registry contains Key Value pairs.They are grouped under various folder like locations like HKEY_CURRENT_USER, HKEY_CURRENT_CONFIG. They are called hives, because the original Windows developer of NT version hated bees.

How can we view the registry values in PowerShell,example of HKEY_CURRENT_USER?

Make sure you have permission to view the folder:

READ:

Get-ChildItem -Path Registry::HKEY_CURRENT_USER

CREATE:

1. Take a backup of Registry using File->Export 

Save in a location like c:\backup\registry\backup.reg

You can restore in case of any errors by using File->Import from above location.

2. Suppose you want to add a "Open with notepad" icon, you need to add a registry value to 

"Open with notepad" under shell location

Change the value to notepad.exe %1

Below are the commands:

Push-Location
Set-Location -Path Registry::HKEY_CLASSES_ROOT

Test-Path ".\*\shell\Open With Notepad"

Set-Location -LiteralPath "Registry::HKEY_CLASSES_ROOT\*\shell"

New-Item -Name "Open With Notepad"

Set-Location -LiteralPath  "Registry::HKEY_CLASSES_ROOT\*\shell\Open With Notepad"
New-Item -Name "command"

UPDATE

Use Set-Item to update the key value

Set-Item  -LiteralPath "Registry::HKEY_CLASSES_ROOT\*\shell\Open With Notepad\command" -Value "notepad %1"

Reference:

https://docs.microsoft.com/en-us/powershell/scripting/getting-started/cookbooks/working-with-registry-keys?view=powershell-6

https://www.howtogeek.com/370022/windows-registry-demystified-what-you-can-do-with-it/

SharePoint Calculated Columns

SharePoint Calculated Columns are very interesting.

References:
https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx

SharePoint and PowerShell PnP View Default Value of a Field

In this page, the steps are given to get the default value of a Choice Column in a list in SharePoint.

Open PowerShell and execute the following commands. Change the URL to your sharepoint URL and execute with your credentials

Install-Module -Name "SharePointPnPPowerShellOnline"
#Please change Demo to yourname in the url
$siteurl = "https://xxx.sharepoint.com/sites/XXX/Demo"

Connect-SPOnline -Url $siteurl -Credentials (Get-Credential)
#View all Fields for Student List
Get-PnPField -List "FruitsDemo1" -Identity "Color" |select DefaultValue

SharePoint PowerShell PnP

SharePoint and PowerShell PnP is soo cool.

Below are some of the snippets I have been playing with. Some of them are helpful for solving business requests asked by developers in the MSDN forums.

Small lines of code to get huge business requirements done in a fraction of a second...

1. Connection Code
$siteurl = "https://sharepointisgreat.sharepoint.com/sites/Dev"
$User = "admin@sharepointisgreat.onmicrosoft.com"
Connect-SPOnline -Url $siteurl -Credentials (Get-Credential)

2. List Operations
#Searching for command to add list
Get-Command *list*
help New-SPOLIst -Examples
New-SPOList -Title "SimplePSList" -Template GenericList -Url "SimplePSList"

#Add list item
help Add-SPOListItem -Examples
Add-SPOListItem -List "SimplePSList" -Values @{"Title"="Item1"}

#Update list item
help Set-SPOListItem -Examples
Set-SPOListItem -List "SimplePSList" -Identity 1 -Values @{"Title"="Item1-Updated"}

#Remove list item
help Remove-SPOListItem -Examples
Remove-SPOListItem -List "SimplePSList" -Identity 1

3. Adding Managed Metadata Column
#View all Taxonomy
Export-SPOTaxonomy -Path C:\assignments\Taxonomy.txt .. Get the term set path
$guid = [Guid]"ea45415d-f5a6-4344-b8a7-9b6c0d7fbf77"#New-Guid

#Add Field with GUID
Add-SPOTaxonomyField -List "StudentMMDemo" -DisplayName "Veggie" -InternalName "Veggie" -id $guid -TermSetPath "Edureka|Food|Vegetables"


#remove
Remove-SPOField -List "StudentMMDemo" -Identity "Veggie"

4. Add a Web Part to a Page /SitePages/samplePowerShell.aspx
$PageUrl = "/SitePages/samplePowerShell.aspx"
Add-SPOWebPartToWebPartPage -ServerRelativePageUrl $PageUrl -Path "C:\demo\SSOM\PowerShellCEWP.dwp" -ZoneId 0 -ZoneIndex 0  

5. Send Mail
Send-SPOMail -To $User -Subject "Mail From PowerShell" -Body "This is a sample mail from PowerShell code"

6. Add a file to a Document Library in SharePoint.Can also be used to move master pages.
Add-SPOFile -Folder "Shared Documents" -Path "C:\demo\UploadSP\Moving Full Trust Code to the Cloud.docx"

7. View Users
$siteurl = "https://sharepointisgreat-admin.sharepoint.com"
$User = "admin@sharepointisgreat.onmicrosoft.com"

Connect-SPOService -Url $siteurl -Credential (Get-Credential)
Get-SPOUser -Site $siteurl |select -Property *Login*

Thank You Jeff for the PowerShell Jump Start tutorials

"Please paste the following in PowerShell ISE and execute"
$newline = "`r`n"
$message = ""
$message = $message +"`r`n"+[char[]][int[]](67,79,78,71,82,65,84,85,76,65,84,73,79,78,83)

$message = $message +"`r`n"+[char[]][int[]](69,84,69,82,78,65,76,32,83,85,67,67,69,83,83)

$message = $message +"`r`n"+"abcbestabcwishes".Replace("abc"," ")

$message = $message +"`r`n"+"You are soo brigxt ! Otxers need sunglasses!".Replace('x','h')

$message = $message +"`r`n"+"Wish u days full of qoney!".Replace('q','m')

$message = $message +"`r`n"+"May you always mile".Insert(15,'s')

$message = $message +"`r`n"+[char[]][int[]](72,97,112,112,121,32,70,111,114,101,118,101,114)

$message = $message +"`r`n"+[char[]][int[]](66,111,114,110,32,116,111,32,87,105,110)

$message = $message +"`r`n"+[char[]][int[]](69,116,101,114,110,97,108,32,83,116,114,101,110,110,103,116,104)

$message = $message +"`r`n"+[char[]][int[]](84,104,97,110,107,32,89,111,117)

Write-Host $message

PowerShell Real Time Examples

Below are some of the Real Time Examples of how SharePoint can saved, analysed or managed with the help of PowerShell:

1. Getting the Active Directory properties of SharePoint User.

SharePoint stores the user details in the form of claim token which needs to be decoded when we are trying to pull the details of user like the Site Collection of a Site Collection Administrator or Owner.
Here is the sample script which came to the rescue , instead of writing huge lines of code in C# in Visual Studio. Get-SPUser is fantastic if the User Profile stores all the AD details.

$ownerSP=Get-SPSiteAdministration -Url "http://w15-sp"|select OwnerLoginName
$m = [Microsoft.SharePoint.Administration.Claims.ClaimProviderManager]::Local
$ownerAD=$m.DecodeClaim($ownerSP.OwnerLoginName)
Get-ADUser -Identity $ownerAD -Properties * | select department,county

Add Operations in SharePoint On Premise with POwerShell

Help Add-SPProjectLogLevelManager -examples
#Add Project entity to Project Sharepoint Site
Add-SPProjectLogLevelManager -Url http://w15-sp/pwa -EntityType Project -EntityUID "0798BF6A-F248-4F0F-8957-4D73993265BC"-LogLevel Verbose -Comments "xyz"
Get-SPProjectLogLevelManager -Url http://w15-sp/pwa
help Add-SPRoutingMachineInfo -examples
#Add routing target to specified identity to farm
$web = Get-SPWebApplication -Identity "http://w15-sp"
$rm = Get-SPRequestManagementSettings -Identity $web
Add-SPRoutingMachineInfo -RequestManagementSettings $rm -Name "w15-dc" -Availability Available
Get-SPRoutingMachineInfo -RequestManagementSettings $rm

#Adds a new machine pool.
 $web=Get-SPWebApplication -Identity "http://w15-sp"
   
 $rm=Get-SPRequestManagementSettings -Identity $web
   
 Add-SPRoutingMachinePool -RequestManagementSettings $rm -Name "w15-dc"
 Get-SPRoutingMachinePool -RequestManagementSettings $rm
   
   
help Add-SPRoutingRule -examples
# This examples adds a routing rule to the farm by using the $rm 
#   variables.
Add-SPRoutingRule -RequestManagementSettings $rm -Name "SampleRule"
Get-SPRoutingRule -RequestManagementSettings $rm

Note: The following content is in progress and please DO NOT use in production

Add-SPRoutingRule -RequestManagementSettings $rm -Name "SampleRule"

Get-SPRoutingRule -RequestManagementSettings $rm

#Add Scaleout database(IN Progress)

$appService = Get-SPServiceApplication |?{$_.DisplayName -like  "*App Management*"}

Add-SPScaleOutDatabase -ServiceApplication $appService -DatabaseName "sample"

 $appServiceProxy=Get-SPServiceApplicationProxy| ?{$_.DisplayName -like  "*App Management*"}

 Add-SPServiceApplicationProxyGroupMember -Identity 66c958d1-d10a-4880-bfc3-8b2646b8e4cd -Member fc413acb-f0b1-4617-b2f3-f2ef93c7f8bb

 Get-SPServiceApplicationProxyGroup

#Add new account

Add-SPSecureStoreSystemAccount -AccountName "contoso\garthf"

Get-SPSecureStoreSystemAccount

#Adds a user to the SharePoint_Shell_Access role for the specified database.

<#This example adds a new user named User1 to the SharePoint_Shell_Access role in 

    the farm configuration database only, and also ensures the user is added to the 

    WSS_Admin_WPG local group on each server in the farm.

    #>

Add-SPShellAdmin -UserName "contoso\garthf"

    

#This example adds a new user named garthf to the SharePoint_Shell_Access role in 

    #the farm configuration database only, and also ensures the user is added to the 

    #WSS_Admin_WPG local group on each server in the farm.

Add-SPShellAdmin -UserName CONTOSO\garthf

    

#This example adds a new user named User1 to the SharePoint_Shell_Access role in 

 # both the specified content database and the configuration database by passing a 

 #  database GUID to the cmdlet.   

$spDatabase = Get-SPContentDatabase |?{$_.Name –like “*Project*”}    

Add-SPShellAdmin -UserName CONTOSO\User1 -database $spDatabase.ID 

    

#This example adds a new user named garthf to the SharePoint_Shell_Access role in 

  #  both the specified Central Administration content database and the configuration 

  #  database.    

Get-SPDatabase | Where-Object {$_.WebApplication -like 

    "SPAdministrationWebApplication"} | Add-SPShellAdmin CONTOSO\garthf

    

   # This example adds a new user named garthf  to the SharePoint_Shell_Access role of 

    #both the specified content database and the configuration database by passing the 

    #name of the database to the cmdlet.

Get-SPDatabase | ?{$_.Name -eq "WSS_Content"} | Add-SPShellAdmin -Username 

    CONTOSO\garthf

Add/Update Operations on User Profile

#Turn on Security Trimming in Service Application
$upProxy = Get-SPServiceApplicationProxy |?{$_.DisplayName -like "*Profile*"}
Add-SPPluggableSecurityTrimmer -UserProfileApplicationProxyId $upProxy.ID
Get-SPPluggableSecurityTrimmer -UserProfileApplicationProxyId $upProxy.ID
Get-Help Add-SPPluggableSecurityTrimmer -examples

#Add Profile Leader
Add-SPProfileLeader -ProfileServiceApplicationProxy $upProxy -Name "contoso\alexd"
Get-SPProfileLeader -ProfileServiceApplicationProxy $upProxy
#Add User Profiel from different domain
Get-Help Add-SPProfileSyncConnection -examples
Add-SPProfileSyncConnection -ProfileServiceApplication $upProxy`
    -ConnectionForestName "fabrikam.com" -ConnectionDomain "Fabrikam" -ConnectionUserName "Testupa" `
    -ConnectionPassword convertto-securestring "Password1" -asplaintext -force `
    -ConnectionSynchronizationOU "OU=SharePoint Users,DC=fabrikam,DC=com"`

Adds a user agent to a farm

Add-PSSnapin "Microsoft.SharePoint.PowerShell"                                  

Get-Help Add-SPInfoPathUserAgent -examples                                      

Get-SPInfoPathUserAgent                                                        

Add-SPInfoPathUserAgent -Name "something"

#View new agent

Get-SPInfoPathUserAgent

Display all the lists in a SharePoint Site collection

This is a very simple set of code in PowerShell to find all the lists in SharePoint which are not default/hidden for all web sites under a site collection.


http://contoso.sharepoint.com


$spSite = Get-SPSite -Identity http://contoso.sharepoint.com

$spWebs = $spSite.AllWebs




Get-Date


foreach($spWeb in $spWebs)



{

$spWebTitle = $spWeb.Title

"Displaying Lists in Site::"+$spWebTitle

$spLists = $spWeb.Lists

foreach ($spList in $spLists)



{

$spList.Title



}

"List Count in site ::"+$spWebTitle + "::" + $spLists.Count

#Get-Date;



}