Sunday, 9 April 2017

PowerShell remoting - create folder on remote servers


Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File C:\Scripts\<username>\<username>_pwd.txt


$SPserver = "<servername>"

$username = "\"
$password = Get-Content C:\Scripts\<username>\<username>_pwd.txt | ConvertTo-SecureString

Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password


#Create remote session to SP server
$session=New-PSSession -ComputerName $SPserver -Credential $credentials

Create DIR

Invoke-Command -Session $session -ArgumentList $output -ScriptBlock  { mkdir E:\<FolderName> }

#Removing remote sessions
Get-PSSession |Remove-PSSession

Open a SharePoint list using PowerShell

Keep using this so put it up here for reference:

#Open SharePoint List
$spWeb =
Get-SPWeb $SPServer
$spData = $spWeb.

Export SharePoint Group members to .csv

Export SP Group members to .csv 

# Script for extracting members of SP groups in a site to a .csv file

This is how it looks in ISE:

Here is the Powershell:

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

function global:Get-SPSite($url)
    return new-Object Microsoft.SharePoint.SPSite($url)

function global:Get-SPWeb($url)
  $site= New-Object Microsoft.SharePoint.SPSite($url)
        if($site -ne $null)
    return $web

     $site = Get-SPSite $URL
     #Write the Header to "Tab Separated Text File"
        "Site Name`t  URL `t Group Name `t User Account `t User Name `t E-Mail" | out-file "c:\Scripts\UsersandGroupsRpt.txt"
     #Iterate through all Webs
      foreach ($web in $site.AllWebs)
        #Write the Header to "Tab Separated Text File"
        "$($web.title) `t $($web.URL) `t  `t  `t `t " | out-file "c:\Scripts\UsersandGroupsRpt.txt" -append
         #Get all Groups and Iterate through  
         foreach ($group in $Web.groups)
                "`t  `t $($Group.Name) `t   `t `t " | out-file "c:\Scripts\UsersandGroupsRpt.txt" -append
                #Iterate through Each User in the group
                       foreach ($user in $group.users)
                           #Exclude Built-in User Accounts
                    if(($User.LoginName.ToLower() -ne "nt authority\authenticated users") -and ($User.LoginName.ToLower() -ne "sharepoint\system") -and ($User.LoginName.ToLower() -ne "nt authority\local service"))
                                "`t  `t  `t  $($user.LoginName)  `t  $($ `t  $($user.Email)" | out-file "c:\Scripts\UsersandGroupsRpt.txt" -append

    write-host "Report Generated at c:\Scripts\UsersandGroupsRpt.txt"

Wednesday, 15 March 2017

Powershell script: bulk add users to SharePoint group

Note - You may need to check the Claims prefix

Here's the PowerShell:

<# Instructions:

Create a Users.txt in this format (all in one column):


Populate the variables


## Adding snap-in
Add-PSSnapin "Microsoft.SharePoint.PowerShell"

# Variables

$group = "External Readers"
$loc = "C:\scripts"        
$Users = Get-Content "$loc\Users.txt"        
$web = Get-SPWeb -identity $siteCollUrl

# code

foreach ($User in $Users) {
    Set-SPUser -Identity $User -Web $siteCollUrl -Group $group         

# Dispose the web object


Tuesday, 14 February 2017

Translating SharePoint Audit log reports:

Translating Site ID and PrincipleID

I produced a Audit report for Security events and though I can see that some security events occurred on the relevant dates I couldn't find a way to translate the Site ID and the Principle ID in the Site ID and Event Data columns. 

It can be done! Enter PowerShell!!!!

Audit Log exported to Excel and downloaded: It just looks horrible

Translate the siteID:

$site = get-spsite -limit all
$site | select ID, url | ft -AutoSize | out-file C:\Scripts\siteID.txt

Then I did a find in the .txt file to match the site ID with the URL


Translate the PrincipleID:
$site = new-object Microsoft.SharePoint.SPSite("http://SPWebApplication");  
$ServiceContext = [Microsoft.SharePoint.SPServiceContext]::GetContext($site);  
$ProfileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)    
$AllProfiles = $ProfileManager.GetEnumerator() 

$AllProfiles | select displayname,recordID | ft -autosize | out-file c:\scripts\profiles.txt

Then do a Find on the Principle ID and you will get the Display name mapping:

Translating groupid and userid



I’ve run a custom security report and the results show 5 permissions edits:

The Event Data column shows the user affected in group\user format

To find:





This shows you either the user's information or the group's members depending on the entered id 

/_layouts/userdisp.aspx?id=218 - shows a user id
/_layouts/userdisp.aspx?id=8 - shows group membership

Note: The ids are unique within site collections only. So ...?id=42 within site collection A might be a different user or group in site collection B.

That's all folks - Thanks for visiting!

Sunday, 29 January 2017

Clusters, listeners, always on, VNNs, VCO, WSFC, AGLs, VIPs, WTF? How does all this stuff fit together?

I put together some notes on Clusters, listeners, always on etc just to clear things up in my own mind:

The cluster name object (CNO) is an Active Directory computer object linked with the network resource Cluster Name (the Windows Cluster)

The Availability Group Listeners are registered as Cluster Resources - and in Active Directory as Virtual Computer Objects (VCOs)

The cluster name account requires the "Create Computer objects" permission when you create a clustered service or an application. When the cluster name account does not have
this permission, SQL Server cannot create the availability group listener

When you create a new availability group listener it becomes a resource in a cluster with an associated virtual network name (VNN), Virtual IP (VIP) - and Availability Group

Listeners are registered as Cluster Resources
This is how The Listener is related to the cluster – it is registered as a cluster resource when it is created
The listener leverages the Windows Failover Cluster (WSFC) for its functionality; you cannot create the listener without the cluster already existing as the cluster itself creates the listener. As such, it is Active Directory (AD) that hosts it and the nodes that use the cluster can use any services that cluster has, in this case the listener.

Essentially someone creates the cluster, and if there are proper AD permissions then it creates the Availability Group (AG) within the cluster, and then the listener within the AG when those are created

The Availability Group Listener (AGL) is part of the AG's Windows Server Failover Cluster (WSFC) Resource. It is a Virtual Network Name with n number of IP addresses. Being in a cluster resource it will only run on a single node of the WSFC and that node will be the primary node of the AG. It is not a thread within SQL Server. The WSFC handles the failover of the AG resource and the AG databases will not come online without the AG resource residing on its node of the WSFC.
The listener just handles connection forwarding. When connecting to the read/write replica the connection will hit the listener and then the listener will tell the client to connect to the primary replica's IP address. In the case of read-only routing it will follow the below work flow