Sunday, February 28, 2016

SharePoint Site Collections & Subsites Report

Requirement:
Get a list of all site collections and their subsites in a HTML formatted report!

PowerShell Script to generate a HTML report for Site Collections & Subsites:
#Add SharePoint Snapin
if ( (Get-PSSnapin -Name "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null ) {
        Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

# Configuration Variables
$WebApplicationURL = "http://intranet.crescent.com"
$ReportOutput = "C:\SiteCollectionsAndSites.html"

#Get all Site Collections from the webapplication
$SitesColl= Get-SPWebApplication $WebApplicationURL | Get-SPSite -Limit All

$HTMLTemplate=@"
<html>
<head>
<!-- Sal - Javascript Function to apply formatting -->
<script type="text/javascript">
function altRows(id){
 if(document.getElementsByTagName){     
  var table = document.getElementById(id);  
  var rows = table.getElementsByTagName("tr");     
  for(i = 0; i < rows.length; i++){          
   if(i % 2 == 0){
    rows[i].className = "evenrowcolor";
   }else{
    rows[i].className = "oddrowcolor";
   }      
  }
 }
}
window.onload=function(){
 altRows('alternatecolor');
}
</script>
  
<!-- CSS Styles for Table TH, TR and TD -->
<style type="text/css">
body{ font-family: Calibri; height: 12pt; }
 
table.altrowstable {
 border-collapse: collapse; font-family: verdana,arial,sans-serif;
 font-size:11px; color:#333333; border-width: 1px; border-color: #a9c6c9;
 border: b1a0c7 0.5pt solid; /*Sal Table format */  
}
table.altrowstable th {
 border-width: 1px; padding: 5px; background-color:#8064a2;
 border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; 
 color: white;  font-size: 11pt;  font-weight: 700;  text-decoration: none;
}
table.altrowstable td {
 border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; color: black; 
 font-size: 11pt; font-weight: 400; text-decoration: none; 
}
.oddrowcolor{ background-color: #e4dfec; }
.evenrowcolor{ background-color:#FFFFFF; }
</style>
</head>
<body>
"@ 

 #Add the HTML File with CSS into the Output report
$Content = $HTMLTemplate > $ReportOutput
    
"<h2> Site Collections & Subsites Report </h2>" >> $ReportOutput  
#Table of Contents
"<h3> Summary of Site Collections</h3> <table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr><th>Site Collection Name </th><th> URL </th><th> No.Of Subsites </th></tr>" >> $ReportOutput

#Loop throuh each site collection 
foreach($Site in $SitesColl) 
{
    "<tr> <td> <a href='#$($Site.Rootweb.Title.ToLower())'>$($Site.Rootweb.Title)</a> </td><td> $($Site.Rootweb.URL)</td> <td> $($Site.AllWebs.Count) </td></tr>" >> $ReportOutput
}
"</table><br/><hr>" >> $ReportOutput

#Get All sub Sites
foreach($Site in $SitesColl) 
{
    "<h4> Subsites of Site Collection: <a name='$($Site.RootWeb.Title.ToLower())' href='$($Site.RootWeb.URL)' target='_blank'>$($Site.RootWeb.Title)</a> </h4> ">> $ReportOutput
    "<table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr><th>Site Name </th><th> Site URL </th><th> Last Modified </th></tr>" >> $ReportOutput
    foreach($Web in $Site.AllWebs)
    { 
      "<tr> <td>$($web.Title)</td><td> <a href='$($web.URL)' target='_blank'>$($web.URL)</a></td> <td> $($web.lastitemmodifieddate) </td></tr>" >> $ReportOutput
    }
    
    "</table></br> " >>$ReportOutput
} #Web
"</body></html>" >>$ReportOutput

Write-host "`n Site Collections & Subsites Report generated successfully at "$ReportOutput

Report Output:
SharePoint Site Collections and Subsites Report


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, February 27, 2016

SharePoint Online: Get All Site Collections using PowerShell

If you want to get a list of SharePoint online site collections, you can go to SharePoint admin center (Typically at: https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/online/SiteCollections.aspx).
Get All Site Collections in SharePoint online using PowerShell

This gets you the list of all site collections in SharePoint Online. Lets see the SharePoint online PowerShell to get all site collections.

PowerShell Script to get all Site Collections in SharePoint Online:
Lets use PowerShell to get a list of Site collections from SharePoint online.
#Load SharePoint Online Assemblies
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"
 
#Variables for Processing
$AdminUrl = "https://crescent-admin.sharepoint.com/"
$UserName= "Salaudeen@crescent.com"
$Password = "Password goes here"
 
#Setup Credentials to connect
$SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $UserName, $SecurePassword

#connect to the service
Connect-SPOService -Url $AdminUrl -Credential $Credentials

#Get All Site Collections
$SiteColl = Get-SPOSite 

#Iterate through each site collection
foreach($Site in $SiteColl)
{
    Write-host $Site.Url
}
Similar to On-Premises SharePoint Management Shell, You can also use "SharePoint Online Management Shell" to connect to Office 365, SharePoint online sites!
Import-Module Microsoft.Online.Sharepoint.PowerShell -DisableNameChecking

$AdminSiteURL="https://crescent-admin.sharepoint.com"

$Credential = Get-credential 
Connect-SPOService -url $AdminSiteURL -Credential $Credential 

Get-SPOSite -Detailed | Format-Table Url, Template, StorageUsageCurrent, StorageQuota, LastContentModifiedDate -AutoSize 

Export All Site Collection inventory to CSV:
$Credential = Get-credential
Connect-SPOService -url "https://crescent-admin.sharepoint.com" -Credential $Credential
Get-SPOSite -Detailed | Export-CSV -LiteralPath C:\SitesInventory.csv -NoTypeInformation

Read more here: How to Use PowerShell with SharePoint Online

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


SharePoint Online: PowerShell to Get All Subsites in a Site Collection

Requirement: 
PowerShell to list all subsites in a SharePoint Online Site collection!

PowerShell to get all sub-sites in a Site Collection:
Here is the SharePoint online PowerShell to get all subsites
#Load SharePoint Online Assemblies
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"
  
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/Sales/"
$UserName="Salaudeen@crescent.com"
$Password ="Password goes here"
 
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
 
#Get the Root Web
Function Get-SPOWeb() {
param(
    $WebURL = $(throw "Please Enter the Site Collection URL")
)

    #Get Web information and subsites
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($webURL)
    $Context.Credentials = $credentials
    $Web = $context.Web
    $Context.Load($web)
    $Context.Load($web.Webs)
 
    $Context.executeQuery()

    #Do something with the current sub-site
    Write-host $Web.URL

    #Iterate through each subsite in the current web
    foreach ($Subweb in $web.Webs)
    {
        #Call the function recursively to process all subsites underneaththe current web
        Get-SPOWeb($Subweb.url)
    }
}

#Call the function
Get-SPOWeb -WebURL $SiteUrl

Tags: sharepoint online powershell get subsites, sharepoint online powershell list all subsites

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Get Site Collection Administrators of All SharePoint Sites using PowerShell

Requirement: 
Get all Site collection Administrators of a SharePoint 2016 site collection.

Solution:
How to get site collection administrators in SharePoint? To get a list of site collection administrators, follow these steps:
  • Navigate to the site collection in interest
  • Click on Site Setting gear >> choose Site Settings Menu Item
  • Under Site settings page, click on "Site Collection Administrator" link in "Users and Permissions" group.
  • Here you'll find all site collection administrators.
Get Site Collection Administrators of All SharePoint Sites using PowerShell
How to Get Site Collection Administrators using PowerShell
Same thing can be done using PowerShell too.
$Site = Get-SPSite "http://intranet.crescent.com"
$Site.RootWeb.SiteAdministrators | Select DisplayName, Email
This gets you the list of site collection administrators using PowerShell!
Important: You need to be either an Site collection administrator or Farm Administrator, in order to access Site collection Administrator link. Otherwise, You won't get that link under site settings page!

PowerShell to Get Site Collection Administrators for All Sites:
How about retrieving site collection administrator data for all site collections in your SharePoint environment? 
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get All Site Collections
$SitesColl = Get-SPSite -Limit All

Foreach($Site in $SitesColl)
{
    Write-host $Site.URL

    Foreach ($SiteAdmin in $Site.RootWeb.SiteAdministrators)
    {
       Write-host "`t$($SiteAdmin.DisplayName) - "$SiteAdmin.Email       
    }
} 
Or the One Liner in List format: Get site collection administrators using PowerShell
Get-SPSite | % {$_.RootWeb.SiteAdministrators} | select @{name='Url';expr={$_.RootWeb.Url}}, DisplayName, Email 


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


SharePoint 2013 Search: Unable to retrieve topology component health states. This may be because the admin component is not up and running

Problem:
SharePoint 2013 Search service application displays the error message "Unable to retrieve topology component health states. This may be because the admin component is not up and running"
Unable to retrieve topology component health states. This may be because the admin component is not up and running
Solution:
After making sure the account configured to run Search service & Search Service Host in Services console, (Service.msc), Provisioned the Usage Application Proxy with PowerShell.

Open SharePoint 2013 Management Shell, and run these commands.
$UsageProxy = Get-SPServiceApplicationProxy | where {$_.TypeName -like "Usage*"}
$UsageProxy.Provision()



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, February 23, 2016

SharePoint Online: Delete Version History using Powershell CSOM

How to delete version history in sharepoint online? 
To Delete all previous versions of a document in SharePoint, follow these steps:
  • Navigate to your SharePoint Library, Select the file and then click on "Version History" from the ribbon menu.sharepoint online delete version history
  • From the version history page, click on "Delete all versions" link, confirm if prompted!sharepoint online delete versions

SharePoint Online: Delete Version History using PowerShell
Case 1: Delete All Versions for a particular file:
#Load SharePoint CSOM Assemblies
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"
  
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/sales/"
$FileURL="/Sites/Sales/ProjectDocuments/ProjectMetrics.xlsx"

#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Cred

    #Get the File
    $File = $Ctx.Web.GetFileByServerRelativeUrl($FileURL)

    #Get all versions of the file
    $Versions = $File.Versions
    $Ctx.Load($Versions)
    $Ctx.ExecuteQuery()

    Write-host -f Yellow "Total Number of Versions Found :" $Versions.count

    #Delete all versions of the file
    $Versions.DeleteAll()
    $Ctx.ExecuteQuery()

    Write-host -f Green "All versions Deleted for given File!"   
}
Catch {
    write-host -f Red "Error deleting versions!" $_.Exception.Message
}

Case 2: Delete All Versions for all Files in the Library:
#Load SharePoint CSOM Assemblies
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"
  
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/sales/"
$LibraryName="Project Documents"

#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Cred

    #Get the web and Library
    $Web=$Ctx.Web
    $List=$web.Lists.GetByTitle($LibraryName)
 
    #Get all items from the library
    $Query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()
    $ListItems = $List.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()
 
    #Loop through each file in the library
    Foreach($Item in $ListItems)
    {       
        #Delete all versions of the file
        $Item.File.Versions.DeleteAll()
        $Ctx.ExecuteQuery()
    }
    Write-host -f Green "All Versions deleted!"
}
Catch {
    write-host -f Red "Error Deleting version History!" $_.Exception.Message
}

Case 3: Leave Last Five versions and Delete the Rest in the Library:
#Load SharePoint CSOM Assemblies
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"
  
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/sales/"
$LibraryName="Project Documents"
$VersionsToKeep=5

#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Cred
  
    #Get the web and Library
    $Web=$Ctx.Web
    $List=$web.Lists.GetByTitle($LibraryName)
 
    #Get all items from the library
    $Query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()
    $ListItems = $List.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()
 
    #Loop through each file in the library
    Foreach($Item in $ListItems)
    {
        #Get all versions of the file
        $Versions = $Item.File.Versions
        $Ctx.Load($Versions)
        $Ctx.Load($Item.File) #To get File Name
        $Ctx.ExecuteQuery()

        Write-host -f Yellow "Total Number of Versions Found in '$($Item.File.Name )' : $($Versions.count)"

            #Check if number of versions are more than limit
            While($Item.File.Versions.Count -gt $VersionsToKeep)
            {
                write-host "Deleting Version:" $Versions[0].VersionLabel
                $Versions[0].DeleteObject()
                $Ctx.ExecuteQuery()
    
                #Reload versions
                $Ctx.Load($Item.File.Versions)
                $Ctx.ExecuteQuery()
            }
    }
}
Catch {
    write-host -f Red "Error deleting versions!" $_.Exception.Message
}
If you want to keep last five versions and delete the rest for a particular file, use:
    #Get the File
    $File = $Ctx.Web.GetFileByServerRelativeUrl($FileURL)

    #Get all versions of the file
    $Versions = $File.Versions
    $Ctx.Load($Versions)
    $Ctx.ExecuteQuery()
    $Versionscount = $Versions.count

    Write-host -f Yellow "Total Number of Versions Found :" $Versionscount

    #Check if number of versions are more than limit
    While($File.Versions.Count -gt $VersionsToKeep)
    {
        write-host "Deleting Version:" $Versions[0].VersionLabel
        $Versions[0].DeleteObject()
        $Ctx.ExecuteQuery()
    
        #Reload versions
        $Ctx.Load($File.Versions)
        $Ctx.ExecuteQuery()
    }


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, February 21, 2016

Create Site Collection in SharePoint using PowerShell

We have to use New-SPSite cmdlet to create a site collection in SharePoint. It takes URL and Owner parameters as mandatory with several other parameters. Here is an example:

PowerShell to create new Site Collection in SharePoint 2016:
To create a site collection using PowerShell in SharePoint 2013, use this script.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Variables
$SiteCollURL = "http://intranet.crescent.com/sites/Sales"
$SiteName = "Sales Team Site"
$SiteOwner = "Crescent\Salaudeen"
$SiteTemplate = "STS#0"  #Team Site Template

#Create new Site Collection
New-SPSite -URL $SiteCollURL -OwnerAlias $SiteOwner -Template $SiteTemplate -Name $SiteName
Create blank site collection in SharePoint 2013 using PowerShell 
Use the  template as "STS#1" to create a blank site collection in SharePoint. We can get all available site templates using cmdlet: Get-SPWebTemplates.

SharePoint 2013: PowerShell to create site collection in specific content database
Use -ContentDatabase parameter in New-SPSite to create your new site collection in an existing content database. E.g.
New-SPSite -Name "Sales" -ContentDatabase SP2013_Sales_Content -url http://Intranet.crescent.com/sites/Sales/ -OwnerAlias "Crescent\SPAdmin" -Template "STS#1"

PowerShell to create site collection without template:
In case, you don't provide template value, still the site collection will be created, but when the site is accessed for the first time,  Users will get a prompt to choose the template for the site collection.
Create Site Collection in SharePoint using PowerShell


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, February 20, 2016

SharePoint Online: Delete Unique Permissions for All Items in a List using PowerShell

Requirement: Reset all customized permissions in all documents of a SharePoint online document library.

By default, Permissions are inherited from their parent in all levels of SharePoint. E.g. Sites inherits permissions from its parent site collection (or parent site), lists and libraries inherits permissions from the site, items in the list inherits permissions from the list. So, If you make any change in permissions at the parent level, any child underneath, automatically inherits the permission changes you made in the parent, unless the child is using its own unique permissions.

At times, you may have to setup unique permissions at granular level and of course, you may have to reset the broken permissions.

How to Delete Unique Permissions in SharePoint?
To reset custom permissions on SharePoint list items or documents,
  • Navigate to the SharePoint library where your documents are stored
  • Select the document >> Click on "Shared With" under Manage group in the ribbon 
  • Click on Advanced >> and click on "Delete Unique Permissions" button from the ribbon. Confirm the prompt once!
delete unique permissions in sharepoint online using powershell

Alright, now the permissions are set to Inherited from the parent library of the document. 
But wait, Picking each and every individual document and repeating these steps to remove unique permissions is tedious, wouldn't you agree? So, I wrote this PowerShell script to Reset Broken Inheritance on all items in a SharePoint List.

PowerShell Script to Delete Unique Permissions for All List Items in SharePoint Online:
#Load SharePoint Online Assemblies
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"
  
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/Sales/"
$ListName= "Documents"
$UserName= "Salaudeen@crescent.com"
$Password ="Password goes here"
 
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
 
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials
  
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)
 
#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$Context.Load($ListItems)
$Context.ExecuteQuery()

Write-host "Total Items Found:"$ListItems.Count
#Iterate through each list item
 $ListItems |  foreach {
    #Delete Unique Permission
    $_.ResetRoleInheritance()
 }
$Context.ExecuteQuery()

Write-host "Broken Permissions are Deleted on All Items!" -ForegroundColor Green
Last but not least: You need SharePoint online Client SDK installed on your client machine to use this code: https://www.microsoft.com/en-us/download/details.aspx?id=42038

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, February 16, 2016

PowerShell-CAML SPQuery to Filter List Items based of Person or Group Field Value

Requirement:
In a project tracking SharePoint list, want to quickly get the list the projects where a particular user is listed under Team members - person or group (People picker) field.

Solution: Use SPQuery with PowerShell as in the below example.

PowerShell to Filter List Items based of Person or Group Field:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration parameters
$SiteURL = "http://portal.crescent.com/projectpipeline/"
$ListName = "Projects"
$FieldName= "TeamMembers"
$UserAccount="Crescent\Omar"

#Get the User Account
$User = Get-SPUser -Identity $UserAccount -Web $SiteURL

#Query to filter List Items which contains user account
$SPQuery = new-object Microsoft.SharePoint.SPQuery
$Query =  "<Where><Eq><FieldRef Name='TeamMembers' LookupId='TRUE'/><Value Type='User'>$($User.ID)</Value></Eq></Where>"
$SPQuery.Query=$Query

#Get site and List objects
$web = Get-SPWeb $SiteURL
$List = $web.Lists.TryGetList($ListName)

#Filter List Items by Query
$ListItems = $List.GetItems($SPQuery)
foreach($item in $ListItems)
{
    write-host $Item["ProjectName"]
}
write-host "Total Number of Projects Found:" $ListItems.Count


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, February 15, 2016

Get Data from SQL Server and Insert Into SharePoint List using PowerShell

Requirement:
We've an External CRM application from which data is to be extracted and inserted into a SharePoint List!

PowerShell to Read Data from SQL Server and Insert into SharePoint:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Sharepoint List
$Web = Get-SPWeb "http://portal.crescent.com/ProjectPipeline/"
$List = $Web.Lists["Projects"]

$ConnectionString = "Server=CrescentSQL;Database=CRM_Integrated_DB;Integrated Security=True"
$SPName = "rpt_Project_pipeline"

#execute the Stored Procedure to Fetch Data
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $ConnectionString
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandType=[System.Data.CommandType]'StoredProcedure'
$sqlCommand.CommandText = $SPName
   
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $sqlCommand
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$sqlCommand.Dispose()

## Fetch data from Stored Procedure
write-host "Data Import Starts..."
ForEach ($Project in $DataSet.Tables[0]) 
{ 
 #Update Existing Project if the match found
    $SPQuery = New-Object Microsoft.SharePoint.SPQuery 
    $SPQuery.Query = "<Where><Eq><FieldRef Name='ProjectName' /><Value Type='Text'>" + $Project.'Project Name' + "</Value></Eq></Where>"
    $ListItems = $List.GetItems($SPQuery) 
    if($ListItems.Count -gt 0)
    {
        foreach($Item in $ListItems)
        { 
            #update existing Project data
            $Item["Company Type"] = $Project.'Company Type'
            $Item["Sector"] = $Project.'Sector'
            $Item["Country"] = $Project.'Country'
            $Item["Deal Lead"] = $Project.'Deal Lead'
            $Item["Investment Date"] = $Project.'Investment Date'
            $Item["Investment Amount"] = $Project.'Investment Amount'
            $Item["Industry"] = $Project.'Industry'

   try
            {
                $Item.Update() 
            }
            catch
            {
                write-host "Item ID: " $Item["ID"] "not updated"
            }
        }    
    } # Update List Item complete
  
   else #Add New Item incase no match for Project Name
   {
        if([string]::IsNullOrEmpty($Project.'Project Name')){
            #write-host "Project Name is empty and data not updated"
        }
        else{
            $newItem = $List.Items.Add()
            $newItem["Project"] = $Project.'Project Name'
            $newItem["Company Name"] = $Project.'Company Name'
            $newItem["Company Type"] = $Project.'Company Type'
            $newItem["Sector"] = $Project.'Sector'
            $newItem["Country"] = $Project.'Country'
            $newItem["Deal Lead"] = $Project.'Deal Lead'
            $newItem["Investment Date"] = $Project.'Investment Date'
            $newItem["Investment Amount"] = $Project.'Investment Amount'
            $newItem["Industry"] = $Project.'Industry'
       
            TRY
            {
                $newItem.Update()
            }
            CATCH
            {
                Write-Host "Project Name: " $Project.'Project Name' "NOT UPDATED FROM TABLE"
            }
        }
    }
}
write-host "Data Import completed!"



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, February 13, 2016

Create Subscription Settings Service Application in SharePoint using PowerShell

What is subscription settings service application in SharePoint? Well, SharePoint Apps platform relies on Microsoft SharePoint Foundation Subscription Settings service application. So, If you want to use Apps in SharePoint, its a prerequisite to create Subscription Settings service application.

How to create subscription settings service application?
Subscription Settings service application can be created only via PowerShell, There is no GUI to create this Service Application! Lets configure the subscription settings service application by using windows PowerShell.

Don't forget to Change configuration parameters such as the service account, database name,etc on the below script as per your environment. Once done,  run this PowerShell script from either PowerShell ISE or through SharePoint Server 2013 Management Shell.

PowerShell to Create Subscription Settings Service Application in SharePoint
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$ServiceAppName = "Subscription Settings Service Application"
$AppPoolAccount = "Crescent\SP16-AppPool"
$AppPoolName = "Service Application App Pool"
$DatabaseServer = "SP16-SQL001"
$DatabaseName = "SP16_SubsSett_ServiceApp"

Try {
    #Set the Error Action
    $ErrorActionPreference = "Stop"

    #Check if Managed account is registered already
    Write-Host -ForegroundColor Yellow "Checking if Application Pool Accounts already exists"
    $AppPoolAccount = Get-SPManagedAccount -Identity $AppPoolAccount -ErrorAction SilentlyContinue
    if($AppPoolAccount -eq $null)
    {
        Write-Host "Please Enter the password for the Service Account..."
        $AppPoolCredentials = Get-Credential $AppPoolAccount
        $AppPoolAccount = New-SPManagedAccount -Credential $AppPoolCredentials
    }
 
    #Check if the application pool exists already
    Write-Host -ForegroundColor Yellow "Checking if the Application Pool already exists"
    $AppPool = Get-SPServiceApplicationPool -Identity $AppPoolName -ErrorAction SilentlyContinue
    if ($AppPool -eq $null)
    {
        Write-Host -ForegroundColor Green "Creating Application Pool"
        $AppPool = New-SPServiceApplicationPool -Name $AppPoolName -Account $AppPoolAccount
    }
 
    #Check if the Service application exists already
    Write-Host -ForegroundColor Yellow "Checking if Subscription Settings Service Application exists already"
    $ServiceApplication = Get-SPServiceApplication -Name $ServiceAppName -ErrorAction SilentlyContinue
    if ($ServiceApplication -eq $null)
    {
        Write-Host -ForegroundColor Green "Creating Subscription Settings Service Application..."
        $ServiceApplication = New-SPSubscriptionSettingsServiceApplication –ApplicationPool $AppPoolName –Name $ServiceAppName –DatabaseName $DatabaseName –DatabaseServer $DatabaseServer 
        $Proxy = New-SPSubscriptionSettingsServiceApplicationProxy –ServiceApplication $ServiceApplication
        Write-Host -ForegroundColor Green "Subscription Settings Service Application created successfully!"
    }

    #Start service instance 
    $ServiceInstance = Get-SPServiceInstance | Where-Object { $_.TypeName -eq "Microsoft SharePoint Foundation Subscription Settings Service"}
 
    #Check the Service status
    if ($ServiceInstance.Status -ne "Online")
    {
        Write-Host -ForegroundColor Yellow "Starting the Subscription Settings Service Instance..."
        Start-SPServiceInstance $ServiceInstance
    }

}
catch {
    Write-Host $_.Exception.Message -ForegroundColor Red
 }
 finally {
    #Reset the Error Action to Default
    $ErrorActionPreference = "Continue"
 } 
This creates subscription settings service application in SharePoint 2013 and SharePoint 2016.
how to create subscription settings service application using PowerShell
Tags: add subscription settings service application, configure subscription settings service application, configure the subscription settings service application by using windows powershell, create subscription settings service application, create subscription settings service application sharepoint 2013, how to create subscription settings service application, powershell script to create subscription settings service application, provision a subscription settings service application, setting up the subscription settings service application in sharepoint 2013,use powershell to create the subscription settings service application

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Deactivate Feature on All Sites in SharePoint using PowerShell

Requirement: 
After deploying Office web apps with edit licenses in SharePoint, we wanted to disable open in client feature on all site collections in SharePoint. Deactivating a feature is straight forward from SharePoint web UI by getting into:
  • Site Settings >> Site Collection Features
  • Locate the target feature and click on "Deactivate" button next to it.
    sharepoint deactivate feature all sites
However, its cumbersome to repeat this activity for larger number of site collections, isn't it? So, use these PowerShell scripts to deactivate features from all sites in SharePoint!

PowerShell to Disable Feature on All Site Collections in SharePoint
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Feature to Deactivate
$FeatureID="8a4b8de2-6fd8-41e9-923c-c7c3c00f8295"

#Get all Site collections
$SitesColl= Get-SPWebApplication | Get-SPSite -Limit ALL
#Get the Feature
$Feature = Get-SPFeature | Where {$_.ID -eq $FeatureID}

#Check if Feature exists
if($Feature -ne $null)
{
    #Iterate through through each site collection and deactivate the feature
    Foreach($Site in $SitesColl)
    {
 #Check if the Feature is activated
       if (Get-SPFeature -Site $Site.Url | Where {$_.ID -eq $FeatureId})
       {
     #DeActivate the Feature
            Disable-SPFeature –identity $FeatureID -URL $Site.URL -Confirm:$false
            Write-Host "Feature has been Deactivated on "$Site.URL -f Green
 }
        else
        {
            Write-host "Feature was not activated on "$Site.URL -f Cyan
        }
    }
}
else    
{
    Write-Host "Feature doesn't exist:"$FeatureID -f Red
}
This script deactivates the given feature id from all site collections of the entire SharePoint environment.


Deactivate feature on all sites in SharePoint using PowerShell:
How about deactivating a feature on all sites (webs) in SharePoint? Use this PowerShell script to disable a feature for all sites:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Feature to Deactivate
$FeatureID="7201d6a4-a5d3-49a1-8c19-19c4bac6e668"

#Get all webs
$WebsColl= Get-SPWebApplication | Get-SPSite -Limit ALL | Get-SPWeb -Limit ALL

#Get the Feature
$Feature = Get-SPFeature | Where {$_.ID -eq $FeatureID}

#Check if Feature exists
if($Feature -ne $null)
{
    #Iterate through through each site collection and deactivate the feature
    Foreach($web in $WebsColl)
    {
 #Check if the Feature is activated
       if (Get-SPFeature -web $web.Url | Where {$_.ID -eq $FeatureId})
       {
     #DeActivate the Feature
            Disable-SPFeature –identity $FeatureID -URL $web.URL -Confirm:$false
     Write-Host "Feature has been Deactivated on "$web.URL -f Green
        }
        else
        {
            Write-host "Feature was not activated on "$web.URL -f Cyan
        }
    }
}
else    
{
    Write-Host "Feature doesn't exist:"$FeatureID -f Red
} 
This PowerShell script disables the given feature id from all subsites in SharePoint.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Activate Feature on All Sites in SharePoint using PowerShell

In some scenarios, you may have to activate a feature for all sites or site collections in SharePoint. In my case today, I had to activate "Open In Client" feature of SharePoint to disable browser based editing for all site collection in a SharePoint web application.

How to Activate a Feature in SharePoint?
To activate a site collection feature from SharePoint web UI, You can navigate to Site settings >> Site Collection Features >> Click on "Activate" next to the relevant feature.
Activate Feature on All Sites in SharePoint using PowerShell
Pretty straight forward and easy enough through the UI, isn't it? Well, If you have to do the same for a dozens of site collection in the web application, PowerShell is the perfect solution.

Activate feature for all site collections in SharePoint
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$WebAppURL="https://portal.crescent.com/"
$FeatureName="OpenInClient" #Feature Folder Name

#Get all site collections of the web app and iterate through
$SiteColl= Get-SPSite -WebApplication $WebAppURL -Limit ALL
Foreach($Site in $SiteColl)
{ 
 #Get the Feature ID from Feature Name
    $Feature = Get-SPFeature -Site $Site.Url | Where {$_.DisplayName -eq $FeatureName}
 
 #If Feature Found
 if($Feature -ne $null)
    {
       #Check if the Feature is activated already
       if (Get-SPFeature -Site $Site.Url | Where {$_.ID -eq $Feature.Id})
       {
         Write-Host $FeatureName "is already activated at " $Site.Url -f Yellow
        }
       else
       {
       #Activate the Feature
       Enable-SPFeature –identity $Feature.ID -URL $Site.URL
       Write-Host "Feature Activated on "$site.URL -f Green   
  }
 }
 else
 {
      Write-Host "Feature Name doesn't exist:"$FeatureName -f Red
 } 
}
This PowerShell script activates feature on all site collections.

You can also use this one-liner to activate feature on all site collections: (No error handling, however!)
Get-SPSite -Limit All | foreach {Enable-SPFeature "0561d315-d5db-4736-929e-26da142812c5" -url $_.URL } 

SharePoint: activate feature on all sites
Consider a scenario, where you have to activate a site level (web) feature on 100's of sites under a site collection, which is quite common. In my case, I had to activate Nintex Workflow 2013 feature on all sub sites within a particular site collection.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$SiteCollURL="http://portal.crescent.com/"
$FeatureID="0561d315-d5db-4736-929e-26da142812c5" 

#Get the Feature
$Feature = Get-SPFeature | Where {$_.ID -eq $FeatureID}

#If Feature Found
if($Feature -ne $null)
{     
    #Get all sites under the collection and loop through
    $Site= Get-SPSite $SiteCollURL
    Foreach($Web in $Site.AllWebs)
    {
  #Check if the Feature is activated already
       if (Get-SPFeature -Web $web.Url | Where {$_.ID -eq $FeatureId})
       {
         Write-Host $FeatureID "is already activated at " $Web.Url -f "Blue"
        }
       else
       {
           #Activate the Feature
           Enable-SPFeature –identity $FeatureID -URL $Web.URL
           Write-Host "Feature has been Activated on "$Web.URL -f "Green"
       }
    }
}
else    
{
    Write-Host "Feature doesn't exist:"$FeatureID -f "Red"
}
This script activates feature for all sites in SharePoint.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Friday, February 12, 2016

How to Create a SharePoint 2013 Farm using PowerShell to Avoid GUIDs

Why to use PowerShell to create SharePoint farm rather the nice SharePoint products configuration wizard? Because, SharePoint Products configuration wizard creates Central Administration database names with GUIDs.

Create SharePoint 2013 farm using PowerShell:
How to start? follow these steps to create SharePoint farm using PowerShell.

Step 1: Install SharePoint binaries on each server to your SharePoint 2013 farm. On completion: you will asked to run the "SharePoint Products Configuration Wizard" - Do not run the wizard!

Step 2: Logon to the server where you would like to host SharePoint Central Administration site. Right click on "SharePoint 2013 Management Shell" and select "Run as administrator". The shell will give "Local farm is not accessible", This is expected as we have not created the farm yet. Proceed executing the cmdlets below to create SharePoint 2013 farm using PowerShell.

SharePoint 2013 create new farm using PowerShell
Set the values at #Configuration Settings section below and run these PowerShell commands. Enter the password for Farm account when prompted.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  
 
#Configuration Settings 
$DatabaseServer = "SP13_SQLSrv" 
$ConfigDatabase = "Intranet_Farm_Config" 
$AdminContentDB = "Intranet_Farm_Content_Admin" 
$Passphrase = "2FJlsXghFsas5vdJJKEXXwWF" 
$FarmAccountName = "Crescent\SP13_Farm"   
  
#Get the Farm Account
$FarmAccount = Get-Credential $FarmAccountName
$Passphrase = (ConvertTo-SecureString $Passphrase -AsPlainText -force) 
 
#Create SharePoint Farm
Write-Host "Creating Configuration Database and Central Admin Content Database..." 
New-SPConfigurationDatabase -DatabaseServer $DatabaseServer -DatabaseName $ConfigDatabase -AdministrationContentDatabaseName $AdminContentDB -Passphrase $Passphrase -FarmCredentials $FarmAccount

Write-Host "Installing SharePoint Resources..."
Initialize-SPResourceSecurity

Write-Host "Installing Farm Services ..."
Install-SPService

Write-Host "Installing SharePoint Features..." 
Install-SPFeature -AllExistingFeatures

Write-Host "Creating Central Administration..."               
New-SPCentralAdministration -Port 8080 -WindowsAuthProvider NTLM
 
Write-Host "Installing Help..."
Install-SPHelpCollection -All   

Write-Host "Installing Application Content..." 
Install-SPApplicationContent
 
Write-Host "SharePoint 2013 Farm Created Successfully!"

Step 3: Connect Rest of the Servers to the Farm:
Once you created the SharePoint farm and SharePoint Central Administration site, You can connect rest of the servers to SharePoint Farm either using the PowerShell cmdlet

PowerShell to Add Servers to the SharePoint 2013 Farm:
Close and reopen the SharePoint 2013 Management Shell and run these PowerShell cmdlets.
Connect-SPConfigurationDatabase -DatabaseName "Intranet_Farm_Config" -DatabaseServer "SP13_SQLSrv" -Passphrase (ConvertTo-SecureString "2FJlsXghFsas5vdJJKEXXwWF" -AsPlaintext -Force)

Write-Host "Installing SharePoint Resources..."
Initialize-SPResourceSecurity

Write-Host "Installing Farm Services ..."
Install-SPService

Write-Host "Installing SharePoint Features..." 
Install-SPFeature -AllExistingFeatures

Write-Host "Installing Help..."
Install-SPHelpCollection -All   

Write-Host "Installing Application Content..." 
Install-SPApplicationContent
 
Write-Host "Added the Server to Farm Successfully!" 
Don't forget the change the parameter values according to your environment!

Alternatively, You can use PSConfig wizard and choose "Connect to an existing server farm" and then complete the wizard!
create new SharePoint Farm using PowerShell
This avoids GUIDs in SharePoint Admin database and gives you the flexibility to name your SharePoint Central Admin database.

In case you already created the SharePoint farm using SharePoint 2013 GUI wizard, and you ended up with database names that have GUID’s use this article to remove the GUIDs from SharePoint Central Admin database: How to remove GUID from SharePoint Central Admin Database

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, February 8, 2016

The Complete SharePoint 2013/2010 Site Collection Permission Report using PowerShell

Another PowerShell report generating requirement: SharePoint Site Collection Permission Report to audit permissions on each and every object under a site collection.

This PowerShell script generates a report in HTML format, which consists of
  • Permission setup on All subsites under a site collection
  • Site collection administrators list
  • Permissions applied on each SharePoint object - Such as web (subsite), List, Folder and List Item.
  • Permissions configured for Users, SharePoint Group and AD Groups
  • SharePoint Groups and members of each group who has access.
How to run this script?
How to run the script? Well, Just copy-paste the given script below to either PowerShell ISE or notepad, save it something like "PermissionRpt.ps1", change the configuration variables such as "Site collection URL, Output Report, etc. accordingly. Save and Hit F5!
sharepoint permissions report

SharePoint 2013/2010 permissions report PowerShell
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$HTMLTemplate=@"
<html>
<head>
<!-- Sal - Javascript Function to apply formatting -->
<script type="text/javascript">
function altRows(id){
 if(document.getElementsByTagName){     
  var table = document.getElementById(id);  
  var rows = table.getElementsByTagName("tr");     
  for(i = 0; i < rows.length; i++){          
   if(i % 2 == 0){
    rows[i].className = "evenrowcolor";
   }else{
    rows[i].className = "oddrowcolor";
   }      
  }
 }
}
window.onload=function(){
 altRows('alternatecolor');
}
</script>
 
<!-- CSS Styles for Table TH, TR and TD -->
<style type="text/css">
body{ font-family: Calibri; height: 12pt; }

table.altrowstable {
 border-collapse: collapse; font-family: verdana,arial,sans-serif;
 font-size:11px; color:#333333; border-width: 1px; border-color: #a9c6c9;
 border: b1a0c7 0.5pt solid; /*Sal Table format */  
}
table.altrowstable th {
 border-width: 1px; padding: 5px; background-color:#8064a2;
 border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; 
 color: white;  font-size: 11pt;  font-weight: 700;  text-decoration: none;
}
table.altrowstable td {
 border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; color: black; 
 font-size: 11pt; font-weight: 400; text-decoration: none; 
}
.oddrowcolor{ background-color: #e4dfec; }
.evenrowcolor{ background-color:#FFFFFF; }
</style>
</head>
<body>
"@

#Function to get permissions of an object Sal. Such as: Web, List, Folder, ListItem
Function Get-Permissions([Microsoft.SharePoint.SPRoleAssignmentCollection]$RoleAssignmentsCollection, $OutputReport)
{
   foreach($RoleAssignment in $RoleAssignmentsCollection) 
    { 
        #Get the Permissions assigned to Group/User
        $UserPermissions=@()
        foreach ($RoleDefinition in $RoleAssignment.RoleDefinitionBindings)
        {
            #Exclude "Limited Access" - We don't need it sal.
            if($RoleDefinition.Name -ne "Limited Access")
            {
                $UserPermissions += $RoleDefinition.Name +";"
            }    
        }
        
        if($UserPermissions)
        {
            #*** Get  User/Group Name *****#
            $UserGroupName=$RoleAssignment.Member.Name
            $UserName=$RoleAssignment.Member.LoginName  
            #**** Get User/Group Type ***** Is it a User or Group (SharePoint/AD)?
            #Is it a AD Domain Group?
            If($RoleAssignment.Member.IsDomainGroup)
                {
                   $Type="Domain Group"
                }
            #Is it a SharePoint Group?            
            Elseif($RoleAssignment.Member.GetType() -eq [Microsoft.SharePoint.SPGroup])
            {
                 $Type="SharePoint Group"
            }
            #it a SharePoint User Account
            else
            {
                   $Type="User"
            }
            #Send the Data to Report 
            " <tr> <td> $($UserGroupName) </td><td> $($Type) </td><td> $($UserName) </td><td>  $($UserPermissions)</td></tr>" >> $OutputReport
        }
    }
}

Function Generate-PermissionRpt()
{
    Param([Parameter(Mandatory=$true)] [string]$SiteCollectionURL,
          [Parameter(Mandatory=$true)] [string]$OutputReport, 
          [Parameter(Mandatory=$true)] [bool]$ScanFolders, 
          [Parameter(Mandatory=$true)] [bool]$ScanItemLevel) 

    #Try to Get the site collection  
    try
    { 
        $Site = Get-SPSite $SiteCollectionURL -ErrorAction SilentlyContinue
    }
    catch
    { 
        write-host Site Collection with URL:$SiteCollectionURL Does not Exists!
        return
    }   
    
    #Append the HTML File with CSS into the Output report
    $Content = $HTMLTemplate > $OutputReport
   
    "<h2> Site Collection Permission Report: $($Site.RootWeb.Title) </h2>" >> $OutputReport 
   
    #Table of Contents
    "<h3> List of Sites</h3> <table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr><th>Site Name </th><th> URL </th><th> Permission Setup </th></tr>" >> $OutputReport
    #Get Users of All Webs : Loop throuh all Sub Sites
    foreach($Web in $Site.AllWebs) 
    {
        
        if($Web.HasUniqueRoleAssignments -eq $true)
        {
            $PermissionSetup ="Unique Permissions"
        }
        else
        {
            $PermissionSetup="Inheriting from Parent"
        }
        
        "<tr> <td> <a href='#$($web.Title.ToLower())'>$($web.Title)</a> </td><td> $($web.URL)</td> <td> $($PermissionSetup)</td></tr>" >> $OutputReport
    }
   
    #Site Collection Administrators Heading 
    "</table><br/><b>Site Collection Administrators</b>" >> $OutputReport
     "<table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
 
    #Write Table Header
    "<th>User Account ID </th> <th>User Name </th></tr>" >> $OutputReport 
   
    #Get All Site Collection Administrators
    $Site.RootWeb.SiteAdministrators | sort $_.Name | ForEach-Object {  
    "<tr><td> $($_.LoginName) </td> <td> $($_.Name)</td></tr> " >> $OutputReport
    }

    $Counter=0;
    #Get Users of All Webs : Loop throuh all Sub Sites
    foreach($Web in $Site.AllWebs)
    { 
        Write-Progress -Activity "Collecting permissions data. Please wait..." -status "Processing Web: $($Web.URL)" -percentComplete ($Counter/$Site.AllWebs.count*100)
    
        #Check if site is using Unique Permissions or Inheriting from its Parent Site!
        if($Web.HasUniqueRoleAssignments -eq $true)
        {
            "</table><br/><hr> <h3>Site: <a name='$($Web.Title.ToLower())' href='$($web.URL)' target='_blank'>$($Web.Title)</a> is using Unique Permissions. </h3>" >> $OutputReport
        }
        else
        {
            "</table><br/><hr> <h3>Site: <a name='$($Web.Title.ToLower())' href='$($web.URL)' target='_blank'>$($Web.Title)</a> is Inheriting Permissions from its Parent Site.</h3>" >> $OutputReport
        }
   
        #Get the Users & Groups from site which has unique permissions - TOP sites always with Unique Permissions
        if($Web.HasUniqueRoleAssignments -eq $True)
        {        
            Write-host Processing Web $Web.URL
            #*** Get all the users granted permissions DIRECTLY to the site ***
            "<b>Site Permissions</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
            "<th>Users/Groups </th> <th> Type </th><th> User Name </th> <th>Permissions</th></tr>" >> $OutputReport

            #Call the function to get Permissions Applied 
            Get-Permissions $Web.RoleAssignments $OutputReport
        
              
            #****** Get Members of Each Group at Web Level *********#
            "</table></br> " >>$OutputReport
            
            #Check if any SharePoint Groups Exists, if yes, Get members of it
            $WebGroupRoleAssignments = $Web.RoleAssignments | Where { $_.Member.GetType() -eq [Microsoft.SharePoint.SPGroup]}
            if($WebGroupRoleAssignments)
            {
                "<b>Group Users</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >>$OutputReport
                foreach($WebRoleAssignment in $WebGroupRoleAssignments) 
                { 
                    "<th colspan='3'><b>Group:</b> $($WebRoleAssignment.Member.Name)</th></tr> " >> $OutputReport 
                    foreach($user in $WebRoleAssignment.member.users)
                    {
                        #Send the Data to Log file
                        " <tr> <td> $($user.Name) </td><td> $($user.LoginName) </td><td> $($user.Email)</td><tr>" >> $OutputReport
                    }
                }
            }
        } #Web.HasUniqueRoleAssignments Over      
      
     #********  Check All List's Permissions ********/
        foreach($List in $Web.lists)
        {
            #Skip the Hidden Lists
            if( ($List.HasUniqueRoleAssignments -eq $True) -and  ($List.Hidden -eq $false))
            {
                "</table><br/><b>List: [ $($List.Title) ] at <a href='$($List.ParentWeb.Url)/$($List.RootFolder.Url)'>$($List.ParentWeb.Url)/$($List.RootFolder.Url)</a> is using Unique Permissions.</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
                "<th>Users/Groups </th><th> Type </th><th> User Name </th><th> Permissions</th></tr>" >> $OutputReport 
                   
                #Call the function to get Permissions Applied 
                Get-Permissions $List.RoleAssignments $OutputReport
            }
            "</table>" >>$OutputReport
            
            #********  Check Folders with Unique Permissions ********/
            if($ScanFolders -eq $True) 
            {
                $UniqueFolders = $List.Folders | where { $_.HasUniqueRoleAssignments -eq $True }     
                #Check if any folder has Unique Permission
                if($UniqueFolders) 
                {
                    #Get Folder permissions
                    foreach($folder in $UniqueFolders)
                    {
                        #Write Table Headers
                        $FolderURL=$folder.ParentList.ParentWeb.URL/$folder.Url
                        "<br/><b>Folder: <a href='$($FolderURL)' target='_blank'>$($Folder.Title)</a> is using Unique Permissions.</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
                        "<th>Users/Groups </th><th> Type </th><th> User Name </th><th> Permissions</th></tr>" >> $OutputReport 

                        #Call the function to get Permissions Applied 
                        Get-Permissions $folder.RoleAssignments $OutputReport 
                        
                        "</table>" >>$OutputReport
                    }
                }
            }
            
            #********  Check Items with Unique Permissions ********/
            if($ScanItemLevel -eq $True) 
            {
                $UniqueItems = $List.Items  | where { $_.HasUniqueRoleAssignments -eq $True }     
                #Check if any Item has Unique Permission Sal
                if($UniqueItems) 
                {
                    #Get Folder permissions
                    foreach($Item in $UniqueItems)
                    {
                        #Get Item's Name if Title is NULL
                        if($Item.Title -ne $null) {$ItemTitle = $Item.Title } else {$ItemTitle= $Item["Name"] }
                        #Write Table Headers
                        $ItemURL= $item.ParentList.ParentWeb.Site.MakeFullUrl($item.ParentList.DefaultDisplayFormUrl)
                        "<br/><b>Item: <a target='_blank' href='$($ItemURL)?ID=$($Item.ID)'>$($ItemTitle)</a> in list/library <a href='$($List.ParentWeb.Url)/$($List.RootFolder.Url)'>$($List.Title) </a> is using Unique Permissions.</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
                        "<th>Users/Groups </th><th> Type </th><th> User Name </th><th> Permissions</th></tr>" >> $OutputReport 

                        #Call the function to get Permissions Applied 
                        Get-Permissions $Item.RoleAssignments $OutputReport 
                        
                        "</table>" >>$OutputReport
                    }
                }
            }
        } #List
    $Counter=$Counter+1;        
    } #Web
"</body></html>" >>$OutputReport
Write-host "`n Permission report generated successfully at "$OutputReport 

}

#**********Configuration Variables************
$OutputReport = "C:\PermissionRpt.htm"
$SiteCollURL="http://portal.crescent.com"
$ScanFolders=$False
$ScanItemLevel=$False

#Call the function to Get Permissions Report
Generate-PermissionRpt $SiteCollURL $OutputReport $ScanFolders $ScanItemLevel

Output:
Script is included with a progress bar indicator. Once execution completed, it generates a HTM file at given location.
You can optionally turn on Folders & Item Level flags to include them in permission reporting. If so, You can expect some delays in script execution!

Here is a sample site collection permission report this script generated! 
sharepoint site collection permission report
This script doesn't count "Limited Permissions"!


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, February 6, 2016

How to Check Replicate Directory Changes Permission for UPS Account

Replicate Directory Changes Permission is required for user profile import account in SharePoint. While my another article How to grant Replicate Directory Changes permission walks through the steps to grant replicate directory permission,  Now the question is: How to check if a particular account has replicate directory changes permission?

PowerShell Script to check if the User Profile Import account has Replicate Directory Changes Permission:
Import-module activedirectory

$UserProfileAccountName = "Crescent\SP016_UPS"

Function Check-ADUserPermission(
    [System.DirectoryServices.DirectoryEntry]$entry, 
    [string]$user, 
    [string]$permission)
{
    $dse = [ADSI]"LDAP://Rootdse"
    $ext = [ADSI]("LDAP://CN=Extended-Rights," + $dse.ConfigurationNamingContext)

    $right = $ext.psbase.Children | 
        ? { $_.DisplayName -eq $permission }

    if($right -ne $null)
    {
        $perms = $entry.psbase.ObjectSecurity.Access |
            ? { $_.IdentityReference -eq $user } |
            ? { $_.ObjectType -eq [GUID]$right.RightsGuid.Value }

        return ($perms -ne $null)
    }
    else
    {
        Write-Warning "Permission '$permission' not found."
        return $false
    }
}

Function Check-ReplicateChanges([string]$userName)
{
    # Globals
    $replicationPermissionName = "Replicating Directory Changes"

    # Main()
    $dse = [ADSI]"LDAP://Rootdse"

    $entries = @(
        [ADSI]("LDAP://" + $dse.defaultNamingContext),

        [ADSI]("LDAP://" + $dse.configurationNamingContext));
    Write-Host " User '$userName': "

    foreach($entry in $entries)
    {
        $result = Check-ADUserPermission $entry $userName $replicationPermissionName
        if($result)
        {
            Write-Host "   has '$replicationPermissionName' permissions on '$($entry.distinguishedName)'" `
        }
        else
        {
            Write-Host "   does NOT have '$replicationPermissionName' permissions on '$($entry.distinguishedName)'" `
        }
    }
}

Check-ReplicateChanges $UserProfileAccountName
Disclaimer: I'm not the author of this script! :-)

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Event ID 8321: A certificate validation operation took X milliseconds and has exceeded the execution time threshold

Problem: 
Event ID 8321 logged multiple times in the SharePoint 2013 Application server's Event Log, with the message "A certificate validation operation took 1500.8983 milliseconds and has exceeded the execution time threshold.  If this continues to occur, it may represent a configuration issue.  Please see http://go.microsoft.com/fwlink/?LinkId=246987 for more details." and SharePoint pages were loading very slow!
A certificate validation operation took 1500.8983 milliseconds and has exceeded the execution time threshold.

Root cause: Typically, this error occurs on SharePoint servers which are running with no internet connectivity where SharePoint Root Authority was not added to Trusted Root Certification Authorities store (which is the default behaviour!) .

Solution:
Install SharePoint Root Authority certificate to the Trusted Root Certification Authorities store. Here is how to fix this error step by step.

Step 1: Export the SharePoint Root Authority certificate using PowerShell
Open SharePoint Management Shell and execute these PowerShell scripts.
$RootCertFile="C:\SPRootCert.cer"
$SPRootCert = (Get-SPCertificateAuthority).RootCertificate
$SProotCert.Export("Cer") | Set-Content $RootCertFile –Encoding Byte

This script produces a certificate at "C:\SPRootCert.cer" file. The next step is to import this certificate into trusted root certificate store.

Step 2: Import the SharePoint Root Authority certificate into the Trusted Root Certificate store.
$RootCertFile="C:\SPRootCert.cer"
Import-Certificate -FilePath $RootCertFile -CertStoreLocation Cert:\LocalMachine\Root

You can verify if the SharePoint root authority certificate is imported to trusted root certificate store by going to:
  • Start >> Run >> MMC >> File >> Add/Remove Snap-in 
  • Click on Certificate >> Add >> Select computer account and click next >> select local computer and click finish.
  • Expend Certificate >> Trusted Root Certification Authorities >> Certificate
  • You should see "SharePoint Root Authority" there!
    fix sharepoint certificate validation operation took exceeded execution time threshold
Repeat these two steps in all SharePoint servers of the farm.

Microsoft KB addressing this issue: https://support.microsoft.com/en-us/help/2625048/site-slowness-because-of-sharepoint-sts-certificate-crl-checking

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


You might also like:

Related Posts Plugin for WordPress, Blogger...