List and Export External Users to CSV


This windows application allows SharePoint Online Administrators to list and export all external users to CSV.  You can run the script to iterate all site collections or against the list provided as CSV.  You can double click on a row to open external user management in internet explorer.  Next steps for me would be to allow you to remove the user without going to the site.

I have tested this tool with tenants of 4K and 10K sites without any issue.

Note: The Root Site Collection URL must be SharePoint Admin Url. Not the root site collection url as asked

#To Generate the Input CSV

$SitesToAudit

= Get-SPOSite -Limit All | Where-Object {$_.SharingCapability –ne “Disabled”}

$SitesToAudit

| Select Url | Export-Csv -Path C:\Scripts\InputCSV.csv -NoTypeInformation -Verbose -Force

image

https://gallery.technet.microsoft.com/office/List-and-Export-SharePoint-d670dc40

Enable Auditing – SharePoint Online Site Collection


Auditing is normally a required feature for most organization which wants to track user actions on a SharePoint site.  The functionality can be enabled at site collection level.  But most organizations try to enable it when they already have large number of of site collections.  Doing it manually by going to Site settings – Site collection audit settings would be a tough job if you have large number of site collections.  I wrote the script which may help someone.

The script uses both Client Side Object Model and SharePoint Online Management Shell cmdlets.  Make sure you change the location of SharePoint Client OM dlls.  I have set it as C:\CSOM.  You do not need to specify the Admin Url as I am creating it from User name.

function Enable-SPOAuditing
{
    <#
        .SYNOPSIS
        Enables or disables Auditing on SharePoint ONline Sites
        .DESCRIPTION
        Enables or disables Auditing on SharePoint ONline Sites
        .EXAMPLE
        Enable-SPOAuditing
        explains how to use the command
        can be multiple lines
        .EXAMPLE
        Enable-SPOAuditing
        another example
        can have as many examples as you like
    #>
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory=$true)]
        [System.String]
        $SPOSiteUrl,
        [Parameter(Mandatory=$true)]
        [System.String]
        $UserName,
        [Parameter(Mandatory=$true)]
        [System.Security.SecureString]
        $Password
    )
    $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($SPOSiteUrl)
    $spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $Password)
    #SPO Client Object Model Context
    $spoCtx.Credentials = $spoCredentials
    $spoSite = $spoCtx.Site
    $spoCtx.Load($spoSite)
    $Audit = $spoSite.Audit
    $spoCtx.Load($Audit)
    $spoCtx.ExecuteQuery
    $All = [Microsoft.SharePoint.Client.AuditMaskType]::All;
    $None = [Microsoft.SharePoint.Client.AuditMaskType]::None;
    $CheckIn = [Microsoft.SharePoint.Client.AuditMaskType]::CheckIn;
    $CheckOut = [Microsoft.SharePoint.Client.AuditMaskType]::CheckOut;
    $ChildDelete = [Microsoft.SharePoint.Client.AuditMaskType]::ChildDelete;
    $CheckIn = [Microsoft.SharePoint.Client.AuditMaskType]::CopyCheckIn;
    $Move = [Microsoft.SharePoint.Client.AuditMaskType]::Move;
    $ObjectDelete = [Microsoft.SharePoint.Client.AuditMaskType]::ObjectDelete;
    $ProfileChange = [Microsoft.SharePoint.Client.AuditMaskType]::ProfileChange;
    $SchemaChange = [Microsoft.SharePoint.Client.AuditMaskType]::SchemaChange;
    $Search = [Microsoft.SharePoint.Client.AuditMaskType]::Search;
    $SecurityChange = [Microsoft.SharePoint.Client.AuditMaskType]::SecurityChange;
    $Undelete = [Microsoft.SharePoint.Client.AuditMaskType]::Undelete;
    $Update = [Microsoft.SharePoint.Client.AuditMaskType]::Update;
    $View = [Microsoft.SharePoint.Client.AuditMaskType]::View;
    $Workflow = [Microsoft.SharePoint.Client.AuditMaskType]::Workflow;
    $Audit.AuditFlags = $Search, $Update, $Undelete, $Workflow, $SecurityChange
    $Audit.Update()
    $spoSite.AuditLogTrimmingRetention = 60
    $spoSite.TrimAuditLog = $true
    #Enable All Options Auditing
    $Audit.AuditFlags = $All
    $Audit.Update()
    #Disable Auditing
    $Audit.AuditFlags = $None
    $Audit.Update()
    $spoCtx.ExecuteQuery()
}

$User = “firstnane.lastname@domain.onmicrosoft.com”
$Password = ConvertTo-SecureString “pass@word1” -AsPlainText –Force

#Or Use Get-Credentials to create the object.
$Creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Password

$CSOMAssembliesPath=”C:\CSOM”

$ClientDllPath         = $CSOMAssembliesPath +  “\Microsoft.SharePoint.Client.dll”
$ClientRunTimeDllPath  = $CSOMAssembliesPath +  “\Microsoft.SharePoint.Client.Runtime.dll”       
Add-Type -Path $ClientDllPath -ErrorAction SilentlyContinue
Add-Type -Path $ClientRunTimeDllPath -ErrorAction SilentlyContinue

$AdminUrl = $User
$AtSepPostion = $AdminUrl.IndexOf(‘@’) + 1
$DotPostion = $AdminUrl.LastIndexOf(“.onmicrosoft”)
$DomainName = $AdminUrl.Substring($AtSepPostion, $DotPostion – $AtSepPostion)
$FinalAdminUrl = “https://$DomainName-admin.sharepoint.com”

Connect-SPOService -Url $FinalAdminUrl -Credential $Creds
#To Test

#$Sites = Get-SPOSite -Limit 1

$Sites = Get-SPOSite -Limit All | Select Title, Url, Owner

foreach($Site in $Sites)
{
    Enable-SPOAuditing -SPOSiteUrl $Site.Url -UserName $User -Password $Password
}

Microsoft Office Developer Tools Preview 2 for Visual Studio 2015


https://www.microsoft.com/en-us/download/details.aspx?id=51683

This preview ships with new templates that support SharePoint 2016 for SharePoint solutions and add-ins.

Microsoft Office Developer Tools Preview 2 for Visual Studio 2015 enables you to develop SharePoint solutions and add-ins for SharePoint 2016.
This preview also includes the latest features in Microsoft Office Developer Tools Update 2 for Visual Studio 2015–you can create new Office add-in projects using templates specific to an Office application (Word, Excel, Outlook, and PowerPoint). These new templates support new Office features such as add-in commands, Fabric UI, and new functionalities in Office.js.
This preview must be installed on top of an earlier version of the Microsoft Office Developer Tools for Visual Studio 2015. Before installing this preview, go to aka.ms/officedevtoolsforvs2015 to get the tools.

Extract All User Properties from Active Directory to CSV


Below script allows you to use CSV to extract profile properties from active directory and then export to CSV.

Import-Module ActiveDirectory -ErrorAction SilentlyContinue
$Data = Import-Csv -Path “C:\Scripts\NoSPProfileAccounts.csv”
$Users = $null
$Users = @();
if($Data)
{
    $Data | ForEach-Object { Write-Host $_.Account; $User = Get-ADUser -properties * -Filter “UserPrincipalName -eq ‘$($_.Account)'”;$Users +=$User;}
}
$Users | Export-Csv -NoTypeInformation -Path “C:\Scripts\Users.csv”
$Users | Out-GridView

Installing SharePoint 2016 Part 8


Creating Project Server 2016 Web App

Around 10 years ago, I was on a full time job of working as Project Server consultant and developer working with project managers and team leads in writing project plan and then creating SQL reports that helped the company making informed decision on the workforce. Project server has changed allot since then but with SharePoint 2016 a lot has changed now.

Installation

Step 1 – Download Project Server 2016 installation files. Stop! Just Kidding. Project Server 2016 is now part of SharePoint 2016 setup. You just need the License key of Project Server and a single line of PowerShell will enable the project server on the server.

You can get the SharePoint Server 2016 ISO and License keys for both Project Server and SharePoint Server from http://www.microsoft.com/en-us/download/details.aspx?id=51493

Enabling Project Server 2016

Open PowerShell ISE as administrator – add the SharePoint snapin and run the following cmdlet.

Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue
Enable-ProjectServerLicense -Key “Y2WC2-K7NFX-KWCVC-T4Q8P-4RG9W”

clip_image002

Creating Project Server Service Application

This is simple process using PowerShell or User Interface.

$ApplicationPool = Get-SPServiceApplicationPool “SharePoint Web Services Default”

$ProjectServiceApp = New-SPProjectServiceApplication -Name “Project Service Application” -ApplicationPool $ApplicationPool -Verbose

New-SPProjectServiceApplicationProxy -Name “Project Service Application Proxy” -ServiceApplication $ProjectServiceApp -Verbose

clip_image004

Creating Project Server Web App

First choose the web application where you want to create the site. We will do all the steps with PowerShell.

If you are not familiar with Project Server then you need to know that the database structure of Project Server is different than a SharePoint content database. It has its own tables and schema. Traditionally Project Server used to have 4 Databases which were later merged to single. Content database is always there to store the Project sites which store project documents.

So as a step one we will create a new content database and set it site limit to one so no one can create the site again.

$WebApplication = Get-SPWebApplication https://portal16.contoso.com

$ProjectDB = New-SPContentDatabase -Name “WSS_Content_ProjectSite” -WebApplication $WebApplication -MaxSiteCount 1 -WarningSiteCount 0

clip_image006

Note: You can also create new SPSite object directly in a new content database but if you are on a heavily used SharePoint Farm it is highly likely that another site collection will be create in the same DB.

You can create the project web app like https://portal.com/sites/pwa but traditionally it is created as https://portal.com/pwa. So I will first add a new managed path as explicit inclusion.

New-SPManagedPath “PWA” -Explicit -WebApplication $WebApplication -Verbose

To create the Web app run the following cmdlet.

$Site = “https://portal16.contoso.com/PWA&#8221;

$Template = “pwa#0”

$Name = “Project Web App”

New-SPSite -Url $Site -Template $Template -Name $Name -Description $Name -ContentDatabase $ProjectDB -OwnerAlias contoso\spadmin

Wait for few minutes to complete the process. We are not done yet. We have just created a project web app site collection. We now need to provision the database schema by running the following

Enable-SPFeature pwasite -Url $Site -Verbose

Start-Sleep 300

I also kept my ULSViewer started and filtered to see “Product” eq “Project Server”.

clip_image008

Wait for few minutes to see some activity in Project Server e.g. Queue etc. This will ensure that all components are project server have come online.

clip_image010

Let’s browse the site.

clip_image012

Here you go.

clip_image014

Installing SharePoint 2016 – Part 7


Fixing the SSL Troubles

After I completed the app configuration above in part 6, I opened my portal web site and saw an issue. The portal.contoso.com was giving a certificate error.

image

I went to IIS and saw that the app certificate is assigned to my portal web application. It is a normal configuration of IIS which set the SSL Certificate as default for all web site using portal 443.

image

Please note that if you try to edit the binding without the additional IP address you will see the below message and certificate will be assigned to all SSL web apps.

image

Now there are two steps to fix it.

1. Add an * in the No Host Header bindings to specify wildcard (Server 2012/R2 or Later)

2. Add an additional IP Address on the WFE and change the No Host Header web application binding on that IP address and Apps SSL Certificate (2008 R2)

Note: Some people also use SNI but it is not clear if it is fully supported.

Let’s Fix it.

First I will Open my Network Adapter configuration and add an additional IP Address.

Open Network and Sharing Center – Change Adapter Settings – Right Click on your Adapter – Properties – Internet Protocol version 4 – Properties –

image

Advanced – IP Settings – Add – Add the additional IP and Subnet mask

Before you click OK. Open command prompt and ping that IP Address to confirm that it is not used already.

image

Click Add once confirmed

image

Then OK – OK – Close

Ping the IP address again to confirm.

image

Right Click on No Host Header Web site from IIS console and choose Edit Bindings

image

Select the binding and click Edit – From IP Address drop down select the new IP Address – Make sure App Certificate is selected and click OK.

image

image

Next I will edit the bindings of MySite and Portal web apps to use original wildcard certificate.

image

image

Final step would be to update the AAA Host Name entry for our Development VM to point to new IP Address.

Login to Domain Controller – DNS Manager – Forward Lookup Zone – Console.com

image

Double Click on your VM entry and change the IP Address

image

Click Apply OK.

Go back to SharePoint VM – Open command prompt and type ipconfig /flushdns

image

Now ping your VM name to confirm the new IP address.

Close all browsers and open a fresh one and browse the portal

image

Certificate error is now gone. Click on the SharePoint hosted app

image

On my VM I will be asked for authentic ation again. On a client computer it does a single sign on.

image

image

Issue resolved.

Automatic Creation of Managed and Crawl Properties


SharePoint 2013/2016 automatically generates managed and crawl properties for site columns. This makes the life of site collection administrator lot easier. In this post I will put a real example of how to test it.

Step 1

Create Site columns and content type and add it to a list. I used the PowerShell below to do that

Add-PSSnapIn “Microsoft.SharePoint.Powershell”

#Get the site collection and web object

$siteColl = Get-SPSite -Identity “https://portal16.contoso.com/&#8221;

function Create-SiteColumnText ($siteColl, $CustomColumn)

{

$rootWeb = $siteColl.RootWeb

#Assign fieldXMLString variable with field XML for site column

$fieldXMLString = “<Field Type=’Text’ Name=’$CustomColumn’

Description=’Indicate date of last and next meeting and names of current members.’

DisplayName=’$CustomColumn’

StaticName=’$CustomColumn’

Group=’Custom Columns’

Hidden=’FALSE’

Required=’FALSE’

Sealed=’FALSE’

ShowInDisplayForm=’TRUE’

ShowInEditForm=’TRUE’

ShowInListSettings=’TRUE’

ShowInNewForm=’TRUE’></Field>”

#See field XML on console

#write-host $fieldXMLString

#Create site column from XML string

$rootWeb.Fields.AddFieldAsXml($fieldXMLString)

Write-Host “Column Added”

}

function Create-SiteColumnChoice ($siteColl, $CustomColumn)

{

$rootWeb = $siteColl.RootWeb

#Assign fieldXMLString variable with field XML for site column

$fieldXMLString = “<Field Type=’Choice’

Name=’$CustomColumn’

Description=’Choice.’

Format=’Dropdown’

DisplayName=’$CustomColumn’

StaticName=’$CustomColumn’

Group=’Custom Columns’

Hidden=’FALSE’

Required=’FALSE’

Sealed=’FALSE’

ShowInDisplayForm=’TRUE’

ShowInEditForm=’TRUE’

ShowInListSettings=’TRUE’

ShowInNewForm=’TRUE’>

<Default>Specification</Default>

<CHOICES>

<CHOICE>Specification</CHOICE>

<CHOICE>Development</CHOICE>

<CHOICE>Test</CHOICE>

<CHOICE>Documentation</CHOICE>

</CHOICES>

</Field>”

#See field XML on console

#write-host $fieldXMLString

#Create site column from XML string

$rootWeb.Fields.AddFieldAsXml($fieldXMLString)

Write-Host “Column Added”

}

Create-SiteColumnText -siteColl $siteColl -CustomColumn “MyCustomColumn1”

Create-SiteColumnText -siteColl $siteColl -CustomColumn “MyCustomColumn2”

Create-SiteColumnText -siteColl $siteColl -CustomColumn “MyCustomColumn3”

Create-SiteColumnChoice -siteColl $siteColl -CustomColumn “MyCustomColumn4”

$web = $siteColl.RootWeb

$ctypeName = “MyCustomContentType”

$ctypeParent = $web.availablecontenttypes[“Item”]

$ctype = new-object Microsoft.SharePoint.SPContentType($ctypeParent, $web.contenttypes, $ctypeName)

$web.contenttypes.add($ctype)

$field = $web.fields.getfield(“MyCustomColumn1”)

$fieldLink = new-object Microsoft.SharePoint.SPFieldLink($field)

$ctype.fieldlinks.add($fieldLink)

$field = $web.fields.getfield(“MyCustomColumn2”)

$fieldLink = new-object Microsoft.SharePoint.SPFieldLink($field)

$ctype.fieldlinks.add($fieldLink)

$field = $web.fields.getfield(“MyCustomColumn3”)

$fieldLink = new-object Microsoft.SharePoint.SPFieldLink($field)

$ctype.fieldlinks.add($fieldLink)

$field = $web.fields.getfield(“MyCustomColumn4”)

$fieldLink = new-object Microsoft.SharePoint.SPFieldLink($field)

$ctype.fieldlinks.add($fieldLink)

$ctype.Update()

$ListTitle = “CustomList1”

$Web.Lists.Add($ListTitle, $ListTitle, 100)

$Library =$Web.Lists[$ListTitle]

$Library.ContentTypesEnabled = $true

$Library.ContentTypes.Add($ctype);

$Library.ContentTypes.Delete($Library.ContentTypes[0].Id)

$Library.ContentTypes.Count

$Library.Update()

$Array = @(“Specification”, “Development”, “Test”, “Documentation”)

for ($i = 1; $i -lt 5; $i++)

{

$Item = $Library.AddItem()

$Item[“Title”] = “Item $i”

$Item[“MyCustomColumn1”] = “Item $i Column $i”

$Item[“MyCustomColumn2”] = “Item $i Column $i”

$Item[“MyCustomColumn3”] = “Item $i Column $i”

$j = $i

$Item[“MyCustomColumn4”] = $Array[$j-1]

$Item.Update()

Write-Host “Done…” -ForegroundColor Green

}

clip_image002

Next Step would be to run a full crawl.

Open Central Administration – Application management – Manage service application – Search Service Application – Content Sources – Choose Full crawl from All Sites Content Source. You can run increment crawl in case your farm is large but my farm only has few items.

clip_image004

Click on Crawl Log – URL View – Type the URL of the custom list and click Search

clip_image006

You see that your items are indexed. Now wait for crawl status to idle.

clip_image008

Now go back to portal site – Open Site Settings – Under Site Collection Administrator click Search Schema

clip_image010

Type MyCustom in filter textbox and Click green arrow.

clip_image012

You would see that these columns are greyed out with clear message when you edit them.

clip_image014

“Automatically created managed property.MyCustomColumnX2OWSTEXT is an automatically created managed property. You can’t see any of its existing settings or which crawled properties are mapped to it, but you can map more crawled properties to it. If you save the managed property without making any changes, all settings will be deleted.”

Once you make a change in the property, the link become’s active.

clip_image016

These properties can now be used in Content Search Web part, reminders and display templates

You can make Search Rest to read the values as well

https://portal16.contoso.com/_api/search/query?querytext=’Item 1 Column 1’&selectproperties=’Path,Url,Title,Author,MyCustomColumn1OWSTEXT’

image