Tuesday, June 15, 2021

Create a VNet with a Site-to-Site (S2S) VPN connection using PowerShell

 A Site-to-Site VPN gateway connection is used to connect your on-premises network to an Azure virtual network over an IPsec/IKE (IKEv1 or IKEv2) VPN tunnel. This type of connection requires a VPN device located on-premises that has an externally facing public IP address assigned to it

1. Create a virtual network and a gateway subnet

If you don't already have a virtual network, create one. When creating a virtual network, make sure that the address spaces you specify don't overlap any of the address spaces that you have on your on-premises network.

Create a resource group:

New-AzResourceGroup -Name TestRG1 -Location 'East US'

Create your virtual network.

    Set the variables.

$subnet1 = New-AzVirtualNetworkSubnetConfig -Name 'GatewaySubnet' -AddressPrefix $subnet2 = New-AzVirtualNetworkSubnetConfig -Name 'Frontend' -AddressPrefix

    Create the VNet.

New-AzVirtualNetwork -Name VNet1 -ResourceGroupName TestRG1 ` -Location 'East US' -AddressPrefix -Subnet $subnet1, $subnet2

To add a gateway subnet to a virtual network you have already created

   Set the variables.
  $vnet = Get-AzVirtualNetwork -ResourceGroupName TestRG1 -Name VNet1

   Create the gateway subnet.
Add-AzVirtualNetworkSubnetConfig -Name 'GatewaySubnet' -AddressPrefix -VirtualNetwork $vnet

    Set the configuration.
Set-AzVirtualNetwork -VirtualNetwork $vnet

2. Create the local network gateway

The local network gateway (LNG) typically refers to your on-premises location. It is not the same as a virtual network gateway. You give the site a name by which Azure can refer to it, then specify the IP address of the on-premises VPN device to which you will create a connection. You also specify the IP address prefixes that will be routed through the VPN gateway to the VPN device. The address prefixes you specify are the prefixes located on your on-premises network. If your on-premises network changes, you can easily update the prefixes.

Use the following values:

  • The GatewayIPAddress is the IP address of your on-premises VPN device.
  • The AddressPrefix is your on-premises address space.

To add a local network gateway with a single address prefix:

New-AzLocalNetworkGateway -Name Site1 -ResourceGroupName TestRG1 ` -Location 'East US' -GatewayIpAddress '' -AddressPrefix ''

To add a local network gateway with multiple address prefixes:

New-AzLocalNetworkGateway -Name Site1 -ResourceGroupName TestRG1 ` -Location 'East US' -GatewayIpAddress '' -AddressPrefix @('','')

To mdify IP address prefixes for your local network gateway:

Sometimes your local network gateway prefixes change. The steps you take to modify your IP address prefixes depend on whether you have created a VPN gateway connection.

3. Request a Public IP address

A VPN gateway must have a Public IP address. You first request the IP address resource, and then refer to it when creating your virtual network gateway. The IP address is dynamically assigned to the resource when the VPN gateway is created.

VPN Gateway currently only supports Dynamic Public IP address allocation. You cannot request a Static Public IP address assignment. However, this does not mean that the IP address will change after it has been assigned to your VPN gateway. The only time the Public IP address changes is when the gateway is deleted and re-created. It doesn't change across resizing, resetting, or other internal maintenance/upgrades of your VPN gateway.

Request a Public IP address that will be assigned to your virtual network VPN gateway.

$gwpip= New-AzPublicIpAddress -Name VNet1GWPIP -ResourceGroupName TestRG1 -Location 'East US' -AllocationMethod Dynamic

4. Create the gateway IP addressing configuration

The gateway configuration defines the subnet (the 'GatewaySubnet') and the public IP address to use. Use the following example to create your gateway configuration:

$vnet = Get-AzVirtualNetwork -Name VNet1 -ResourceGroupName TestRG1 $subnet = Get-AzVirtualNetworkSubnetConfig -Name 'GatewaySubnet' -VirtualNetwork $vnet $gwipconfig = New-AzVirtualNetworkGatewayIpConfig -Name gwipconfig1 -SubnetId $subnet.Id -PublicIpAddressId $gwpip.Id

5. Create the VPN gateway

Create the virtual network VPN gateway.

Use the following values:

  • The -GatewayType for a Site-to-Site configuration is Vpn. The gateway type is always specific to the configuration that you are implementing. For example, other gateway configurations may require -GatewayType ExpressRoute.
  • The -VpnType can be RouteBased (referred to as a Dynamic Gateway in some documentation), or PolicyBased (referred to as a Static Gateway in some documentation). 
  • Select the Gateway SKU that you want to use. There are configuration limitations for certain SKUs. If you get an error when creating the VPN gateway regarding the -GatewaySku, verify that you have installed the latest version of the PowerShell cmdlets
New-AzVirtualNetworkGateway -Name VNet1GW -ResourceGroupName TestRG1 ` -Location 'East US' -IpConfigurations $gwipconfig -GatewayType Vpn ` -VpnType RouteBased -GatewaySku VpnGw1

6. Configure your VPN device

Site-to-Site connections to an on-premises network require a VPN device. In this step, you configure your VPN device. When configuring your VPN device, you need the following items:

  • A shared key. This is the same shared key that you specify when creating your Site-to-Site VPN connection. In our examples, we use a basic shared key. We recommend that you generate a more complex key to use.

  • The Public IP address of your virtual network gateway. You can view the public IP address by using the Azure portal, PowerShell, or CLI. To find the Public IP address of your virtual network gateway using PowerShell, use the following example. In this example, VNet1GWPIP is the name of the public IP address resource that you created in an earlier step.

Get-AzPublicIpAddress -Name VNet1GWPIP -ResourceGroupName TestRG1

7. Create the VPN connection

Next, create the Site-to-Site VPN connection between your virtual network gateway and your VPN device. Be sure to replace the values with your own. The shared key must match the value you used for your VPN device configuration. Notice that the '-ConnectionType' for Site-to-Site is IPsec.

Set the variables.
$gateway1 = Get-AzVirtualNetworkGateway -Name VNet1GW -ResourceGroupName TestRG1 $local = Get-AzLocalNetworkGateway -Name Site1 -ResourceGroupName TestRG1

Create the connection.
New-AzVirtualNetworkGatewayConnection -Name VNet1toSite1 -ResourceGroupName TestRG1 ` -Location 'East US' -VirtualNetworkGateway1 $gateway1 -LocalNetworkGateway2 $local ` -ConnectionType IPsec -RoutingWeight 10 -SharedKey 'abc123'

8. Verify the VPN connection

There are a few different ways to verify your VPN connection.

You can verify that your connection succeeded by using the 'Get-AzVirtualNetworkGatewayConnection' cmdlet, with or without '-Debug'.

  1. Use the following cmdlet example, configuring the values to match your own. If prompted, select 'A' in order to run 'All'. In the example, '-Name' refers to the name of the connection that you want to test. :-  Get-AzVirtualNetworkGatewayConnection -Name VNet1toSite1 -ResourceGroupName TestRG1

  2. After the cmdlet has finished, view the values. In the example below, the connection status shows as 'Connected' and you can see ingress and egress bytes. "connectionStatus": "Connected", "ingressBytesTransferred": 33509044, "egressBytesTransferred": 4142431

To connect to a virtual machine

You can connect to a VM that is deployed to your VNet by creating a Remote Desktop Connection to your VM. The best way to initially verify that you can connect to your VM is to connect by using its private IP address, rather than computer name. That way, you are testing to see if you can connect, not whether name resolution is configured properly.

  1. Locate the private IP address. You can find the private IP address of a VM by either looking at the properties for the VM in the Azure portal, or by using PowerShell.

    • Azure portal - Locate your virtual machine in the Azure portal. View the properties for the VM. The private IP address is listed.

    • PowerShell - Use the example to view a list of VMs and private IP addresses from your resource groups. You don't need to modify this example before using it.

    • $VMs = Get-AzVM $Nics = Get-AzNetworkInterface | Where VirtualMachine -ne $null foreach($Nic in $Nics) { $VM = $VMs | Where-Object -Property Id -eq $Nic.VirtualMachine.Id $Prv = $Nic.IpConfigurations | Select-Object -ExpandProperty PrivateIpAddress $Alloc = $Nic.IpConfigurations | Select-Object -ExpandProperty PrivateIpAllocationMethod Write-Output "$($VM.Name): $Prv,$Alloc" }

  2. Verify that you are connected to your VNet using the Point-to-Site VPN connection.

  3. Open Remote Desktop Connection by typing "RDP" or "Remote Desktop Connection" in the search box on the taskbar, then select Remote Desktop Connection. You can also open Remote Desktop Connection using the 'mstsc' command in PowerShell.

  4. In Remote Desktop Connection, enter the private IP address of the VM. You can click "Show Options" to adjust additional settings, then connect.

  5. Troubleshoot a connection

    If you are having trouble connecting to a virtual machine over your VPN connection, check the following:

    • Verify that your VPN connection is successful.

    • Verify that you are connecting to the private IP address for the VM.

    • If you can connect to the VM using the private IP address, but not the computer name, verify that you have configured DNS properly

To modify IP address prefixes for a local network gateway

If the IP address prefixes that you want routed to your on-premises location change, you can modify the local network gateway. When using these examples, modify the values to match your environment.

To add additional address prefixes:

  1. Set the variable for the LocalNetworkGateway. $local = Get-AzLocalNetworkGateway -Name Site1 -ResourceGroupName TestRG1

  2. Set the gateway with the updated prefixes.  Set-AzLocalNetworkGateway -LocalNetworkGateway $local `

    -AddressPrefix @('','')

To modify the gateway IP address for a local network gateway

If the VPN device that you want to connect to has changed its public IP address, you need to modify the local network gateway to reflect that change. When modifying this value, you can also modify the address prefixes at the same time. Be sure to use the existing name of your local network gateway in order to overwrite the current settings. If you use a different name, you create a new local network gateway, instead of overwriting the existing one.

New-AzLocalNetworkGateway -Name Site1 ` -Location "East US" -AddressPrefix @('','') ` -GatewayIpAddress "" -ResourceGroupName TestRG1

To delete a gateway connection

If you don't know the name of your connection, you can find it by using the 'Get-AzVirtualNetworkGatewayConnection' cmdlet.

Remove-AzVirtualNetworkGatewayConnection -Name VNet1toSite1 ` -ResourceGroupName TestRG1

Friday, June 4, 2021

Redirect azure website to SharePoint Online or any other url

Below is the url rewrite rule added in azure webapp config to redirect the site to any new location, let it be a SPO site or any other web url. The sample below redirects to the microsoft password reset page.

<?xml version="1.0" encoding="UTF-8"?>





         <rule name="SimpleURL" patternSyntax="ECMAScript" stopProcessing="true">

                                                          <match url="(.*)"/>

                                                          <action type="Redirect" url="https://aka.ms/sspr/?whr=domain" redirectType="Permanent" appendQueryString="true" />






Powershell script to get SharePoint online site access report

 Check SharePoint site access permissions.

.PARAMETER Credential
Standard PSCredential object.
Identity (user@domain.com) of user to check.
.PARAMETER PermissionToCheck
Full list of permissions to check per site. Default is ViewPages. Additional
parameters include "All" and "AllViewPermissions."
Tenant name as either 'tenant.onmicrosoft.com' or 'tenant.'

param (

    # Credential object

    [Parameter(Mandatory = $true)]



    # Target user to report on



              'DeleteListItems', 'ApproveItems', 'OpenItems', 'ViewVersions', 'DeleteVersions',

              'CancelCheckout', 'ManagePersonalViews', 'ManageLists', 'ViewFormPages', 'AnonymousSearchAccessList',

              'Open', 'ViewPages', 'AddAndCustomizePages', 'ApplyThemeAndBorder', 'ApplyStyleSheets', 'ViewUsageData',

              'CreateSSCSite', 'ManageSubwebs', 'CreateGroups', 'ManagePermissions', 'BrowseDirectories', 'BrowseUserInfo',

              'AddDelPrivateWebParts', 'UpdatePersonalWebParts', 'ManageWeb', 'AnonymousSearchAccessWebLists', 'UseClientIntegration',

              'UseRemoteAPIs', 'ManageAlerts', 'CreateAlerts', 'EditMyUserInfo', 'EnumeratePermissions', 'FullMask','All','AllViewPermissions')]

              [array]$PermissionToCheck = "ViewPages",

    $LogFile = (Get-Date -Format yyyy-MM-dd) + "_SiteAccessReport.txt",

    [Parameter(mandatory = $true)]



function Write-Log([string[]]$Message, [string]$LogFile = $Script:LogFile, [switch]$ConsoleOutput)


    $Message = $Message + $Input

    If ($Message -ne $null -and $Message.Length -gt 0)


        if ($LogFile -ne $null -and $LogFile -ne [System.String]::Empty)


            Out-File -Append -FilePath $LogFile -InputObject "$Message"


        if ($ConsoleOutput -eq $true)


            Write-Host "$Message"




function LoadSharePointLibraries


    If (Test-Path 'c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll')


        Write-Host -ForegroundColor Green "Found SharePoint Server Client Components installation."

        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"

        Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"


    ElseIf ($filename = (Get-ChildItem 'C:\Program Files' -Recurse -ea silentlycontinue | where { $_.name -eq 'Microsoft.SharePoint.Client.DocumentManagement.dll' })[0])


        $Directory = ($filename.DirectoryName)[0]

        Write-Host -ForegroundColor Green "Found SharePoint Server Client Components at $Directory."

        Add-Type -Path "$Directory\Microsoft.SharePoint.Client.dll"

        Add-Type -Path "$Directory\Microsoft.SharePoint.Client.Runtime.dll"

        Add-Type -Path "$Directory\Microsoft.SharePoint.Client.Taxonomy.dll"

        Add-Type -Path "$Directory\Microsoft.SharePoint.Client.UserProfiles.dll"



    ElseIf (!(Test-Path 'C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'))


        Write-Host -ForegroundColor Yellow "This script requires the SharePoint Server Client Components. Attempting to download and install."

        wget 'https://download.microsoft.com/download/E/1/9/E1987F6C-4D0A-4918-AEFE-12105B59FF6A/sharepointclientcomponents_15-4711-1001_x64_en-us.msi' -OutFile ./SharePointClientComponents_15.msi

        wget 'https://download.microsoft.com/download/F/A/3/FA3B7088-624A-49A6-826E-5EF2CE9095DA/sharepointclientcomponents_16-4351-1000_x64_en-us.msi' -OutFile ./SharePointClientComponents_16.msi

        msiexec /i SharePointClientComponents_15.msi /qb

        msiexec /i SharePointClientComponents_16.msi /qb

        Sleep 60

        If (Test-Path 'c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll')


            Write-Host -ForegroundColor Green "Found SharePoint Server Client Components."

            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"

            Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"




            Write-Host -NoNewLine -ForegroundColor Red "Please download the SharePoint Server Client Components from "

            Write-Host -NoNewLine -ForegroundColor Yellow "https://download.microsoft.com/download/F/A/3/FA3B7088-624A-49A6-826E-5EF2CE9095DA/sharepointclientcomponents_16-4351-1000_x64_en-us.msi "

            Write-Host -ForegroundColor Red "and try again."





    If (!(Get-Module -ListAvailable "*online.sharepoint*"))


        Write-Host -ForegroundColor Yellow "This script requires the SharePoint Online Management Shell. Attempting to download and install."

        wget 'https://download.microsoft.com/download/0/2/E/02E7E5BA-2190-44A8-B407-BC73CA0D6B87/SharePointOnlineManagementShell_6802-1200_x64_en-us.msi' -OutFile ./SharePointOnlineManagementShell.msi

        msiexec /i SharePointOnlineManagementShell.msi /qb

        Write-Host -ForegroundColor Yellow "Please close and reopen the Windows Azure PowerShell module and re-run this script."


    If (!(Get-InstalledModule -MinimumVersion 3.11.1907.0 SharePointPnPPowerShellOnline -ea SilentlyContinue))


        Install-Module SharePointPnPPowerShellOnline -MinimumVersion 3.0 -Force




# Validate Permissions

switch -regex ($PermissionToCheck)


    '^(?i)all$' {

        $PermissionToCheck = [array]$PermissionToCheck = @('EmptyMask', 'ViewListItems', 'AddListItems', 'EditListItems',

            'DeleteListItems', 'ApproveItems', 'OpenItems', 'ViewVersions', 'DeleteVersions',

            'CancelCheckout', 'ManagePersonalViews', 'ManageLists', 'ViewFormPages', 'AnonymousSearchAccessList',

            'Open', 'ViewPages', 'AddAndCustomizePages', 'ApplyThemeAndBorder', 'ApplyStyleSheets', 'ViewUsageData',

            'CreateSSCSite', 'ManageSubwebs', 'CreateGroups', 'ManagePermissions', 'BrowseDirectories', 'BrowseUserInfo',

            'AddDelPrivateWebParts', 'UpdatePersonalWebParts', 'ManageWeb', 'AnonymousSearchAccessWebLists', 'UseClientIntegration',

            'UseRemoteAPIs', 'ManageAlerts', 'CreateAlerts', 'EditMyUserInfo', 'EnumeratePermissions', 'FullMask'); break


    '^(?i)allviewpermissions$' {

        $PermissionToCheck = [array]$PermissionToCheck = @('ViewListItems', 'ViewVersions', 'ViewPages', 'ViewUsageData',

            'BrowseDirectories', 'BrowseUserInfo', 'EnumeratePermissions'); break


    '^(?i)viewpages$' { $PermissionToCheck = 'ViewPages'; break}



        if ($PermissionToCheck) { }

        Else { $PermissionToCheck = $PSBoundParameters.Values | ? { $PSBoundParameters.Keys -eq "PermissionToCheck" } }



# Validate identity submitted is a valid email address/upn format

Try { $Test = New-Object Net.Mail.MailAddress($Identity) -ea stop }

Catch { "ERROR: Not a valid identity address (user@domain.com)"; break }

# Validate tenant name

If ($tenant -like "*.onmicrosoft.com") { $tenant = $tenant.split(".")[0] }

$AdminURL = "https://$tenant-admin.sharepoint.com"

# Verify if log file exists; if not, create

If (!(Test-Path $LogFile))


    Write-Log -Message "Identity,Url,Permissions" -LogFile $LogFile


# Connect-PnpOnline only doesn't prompt for creds if you pass it to Invoke-Expression

$cmd = "Connect-PnpOnline -Url $($AdminUrl) -credentials `$Credential"

Invoke-Expression $cmd

# Establish user identity in SPO format

$user = "i:0#.f|membership|$($Identity)"

[array]$Urls = Get-PnPTenantSite | Select -ExpandProperty Url

$i = 1

foreach ($Url in $Urls)


    Write-Progress -Activity "SharePoint Site Permissions Report" -Percent (($i/$Urls.Count)*100) -CurrentOperation "Checking site $($Url)"

    Connect-PnPOnline -Url $Url -credentials $Credential

    $web = Get-PnPWeb

    $UserEffectivePermission = $web.GetUserEffectivePermissions($user)

    try { Invoke-PnPQuery -ea stop }

    catch { Write-Log -LogFile ErrorLog.txt -Message "Error running Invoke-PnP against $($Url)."}


    $EffectivePermissions = @()

    foreach ($Perm in $PermissionToCheck)


        try { $HasAccess = $UserEffectivePermission.Value.Has($Perm) }

        catch { Write-Log -LogFile ErrorLog.txt -Message "Error evaluating permission $($perm) against $($Url)."}

        If ($HasAccess -eq $true) { $EffectivePermissions += $perm }



    if ($EffectivePermissions)


        $PermissionArray = $EffectivePermissions -join ";"

        Write-Log -Message "$($Identity),$($Url),$($PermissionArray)" -LogFile $LogFile




Updating SharePoint UserProfile Properties

In case the SharePoint user profile property for a specific user or set of users then you can use the below powershell script to update the user profile property directly from sharepoint. 

$web=get-spweb -identity http://<weburl>

$user=$web.EnsureUser("<staffid with domain- user claims>")

$user | fl     -- this will show the user id and pass that id to below script in GetitembyID method

$web=get-spweb -identity  http://<weburl>

$list=$web.lists["user information list"]


$item["JobTitle"]="<Title to update>"

$item["Department"]="<New Dept>"




Restore a search service application to another SharePoint farm

 The below script helps in restoring a search service application to another sharepoint farm. Before running below script, you need to take a back up of existing Search DB's ( with or without crawl db) to the new farms SQL server.

$saAppPoolName = "SearchService_AdminAppPool"

# Search Specifics, we are single server farm

$searchServerName = (Get-ChildItem env:computername).value

#Web Front End servers

$hostA = ""Server1"

$hostB = ""Server2"

#Servers hosting Search Components

$hostD = "Server1"

$hostE = "Server2" #In case of running components on multiple servers.

$IndexLocation = "F:\Apps\SearchIndex"

$serviceAppName = "Search Service Application"

$searchDBName = "Search Admin DB"

# Grab the Appplication Pool for Service Application Endpoint

$saAppPool = Get-SPServiceApplicationPool $saAppPoolName

# Start Search Service Instances

Write-Host "Starting Search Service Instances..."

#Start-SPEnterpriseSearchServiceInstance $searchServerName

#Start-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance $searchServerName

# Create the Search Service Application and Proxy

Write-Host "Creating Search Service Application and Proxy..."

$searchInstance = Get-SPEnterpriseSearchServiceInstance -local

$searchServiceApp = Restore-SPEnterpriseSearchServiceApplication -Name $serviceAppName -ApplicationPool $saAppPoolName -AdminSearchServiceInstance $searchInstance -DatabaseName $searchDBName

$searchProxy = New-SPEnterpriseSearchServiceApplicationProxy -Name "Service Application and Proxy" -SearchApplication $searchServiceApp

# Clone the default Topology (which is empty) and create a new one and then activate it

Write-Host "Configuring Search Component Topology..."

$clone = $searchServiceApp.ActiveTopology.Clone()

#$searchServiceInstance = Get-SPEnterpriseSearchServiceInstance

$searchServiceInstance1 = Get-SPEnterpriseSearchServiceInstance -Identity $hostD

$searchServiceInstance2 = Get-SPEnterpriseSearchServiceInstance -Identity $hostE

#We need only two admin component

New-SPEnterpriseSearchAdminComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance1

New-SPEnterpriseSearchAdminComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance2

#We need two content processing components

New-SPEnterpriseSearchContentProcessingComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance1

#New-SPEnterpriseSearchContentProcessingComponent -SearchTopology $clone -SearchServiceInstance $searchServiceInstance2

#We need two analytics processing components

New-SPEnterpriseSearchAnalyticsProcessingComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance1

#New-SPEnterpriseSearchAnalyticsProcessingComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance2

#We need two crawl components

New-SPEnterpriseSearchCrawlComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance1

#New-SPEnterpriseSearchCrawlComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance2

#We need two query processing components

New-SPEnterpriseSearchQueryProcessingComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance1

New-SPEnterpriseSearchQueryProcessingComponent -SearchTopology  $clone -SearchServiceInstance $searchServiceInstance2

New-SPEnterpriseSearchIndexComponent -SearchTopology $clone -SearchServiceInstance $searchServiceInstance1 -RootDirectory $IndexLocation -IndexPartition 0

New-SPEnterpriseSearchIndexComponent -SearchTopology $clone -SearchServiceInstance $searchServiceInstance2 -RootDirectory $IndexLocation -IndexPartition 0

#New-SPEnterpriseSearchAdminComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance

#New-SPEnterpriseSearchContentProcessingComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance

#New-SPEnterpriseSearchAnalyticsProcessingComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance

#New-SPEnterpriseSearchCrawlComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance

#New-SPEnterpriseSearchIndexComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance

#New-SPEnterpriseSearchQueryProcessingComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance


Get-SPEnterpriseSearchTopology -SearchApplication $ssa

# Additional

$ssa = Get-SPEnterpriseSearchServiceApplication "Search Service Application" 

 $admin = Get-SPEnterpriseSearchAdministrationComponent -SearchApplication $ssa

 $admin | Set-SPEnterpriseSearchAdministrationComponent -SearchServiceInstance $searchServiceInstance1 -Force 

 $si = Get-SPEnterpriseSearchServiceInstance -Identity a007f95c-e67e-4150-ab8f-7fff8a71d5b6

$varSearchApp = get-spenterprisesearchserviceapplication

Set-SPEnterpriseSearchAdministrationComponent -SearchApplication $varSearchApp -SearchServiceInstance $si

Feel free to reply in case of any queries

Monday, March 30, 2020

Read email body and headers and add it to an excel file- Power Automate

   This blogs explains how you can read the email contents and add to an excel file in SharePoint online using power automate (flow)

Below are the actions involve in this.

  • First you need to add a trigger on the flow to start when an emails receives in you mailbox.
  • Add a "Html to text" action, Content field set to Body dynamic content of the trigger.

  •  Add a "Compose" action for each value you want to extract from your email, Inputs set to following formula (Sample email below)
    To grab any of those fields in Flow you use a formula like this (example grabs the email)
    'Leave Type:'),

    The breakdow of above formula is given below.
    The subsstring() function has 3 inputs, 1. the source string, 2. the start index, which is the number of characters into the string to start looking and 3. the length.

    The objective therefore, is to derive 2. and 3. so we can grab the relevant text no matter how long is it.

    So firstly we need to know how many characters into the source text to start. This is relatively easy:
    he indexOf() function finds the number of characters into a text string that a string occurs. Here we are calculating the indexOf() the string "Name: " it doesn't matter where in the original message body this occurs, indexOf() will find it and return a number.

    Now we don't actually want the string "Name: " in the output of this expression so we now add() a number to the output of indexOf() to get the number of characters into the message body the end of "Name: " occurs. We don't calculate this dynamically; it's 7, including the space.

    So by using indexOf() to find the place in the source string where "Name: " exists, then adding that number of characters to it, we get the point we want to start reading the email address.

    Next we have to calculate the length of the string, for the 3rd parameter of substring():
    'Leave Type:'),
    To do this we use the sub() function to subtract the number we just calculated from the indexOf() the place we want to stop looking. Thus a lot (the second parameter of sub()) of the expression above is similar to the one before - calculating the number of characters into the source string that our relevant text starts.

    The first parameter of sub() is the place where our relevant text ends - which in this case is the start of the string "Leave Type: ". We don't need to do anything complex with this because it's the start of the string we want the index of, not the end.

    So we now have our 3 parameters for substring(), the source text, which is body('Html_to_text') in this example, the number of characters into that where we find the start of our substring and lastly length, which is calculated by subtracting the former from the number of characters into the source text where we want to stop looking.

    Finally, in my example I've wrapped trim() around the whole thing to eliminate any trailing whitespace.
    • Add a row to table action from Excel online business to the flow and map each columns and the excel table.
    • dd

    Send an email to the mailbox and you will see the selected rows added to your excel.