Create personal site for specific user with Powershell - SharePoint 2010/2013

This script will allow you to create a personal site for a specific user in SharePoint 2010 or SharePoint 2013. It only needs 1 parameter: Loginname.

Save the script in C:\Temp and run:

. C:\Temp\Create-SPMySite.ps1 -username "domain\user"

param
(
[Parameter(Mandatory=$true)]
[string]$username
)
asnp *sh*

$mysite = (Get-SPSite)[0]

$context = [Microsoft.Office.Server.ServerContext]::GetContext($mysite)
$upm =  New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

#Create user profile
$profile = $upm.ResolveProfile($username)

if(!$profile)
{
Write-Host "$profile does not have a profile. Can't create personal site"
}

elseif($profile)
{
    if($profile.PersonalSite -eq $Null)
    {
     $profile.CreatePersonalSite()
     Write-Host "Personal site created"
    }
    else
    {
    Write-Warning "$username already has a personal site"
    }
}




Ref: https://gallery.technet.microsoft.com/Create-personal-for-4a70e4ad

Reduce the size of SharePoint 2013 Usage and Health database

How to reduce usage and health data collection database in SharePoint?


Run the powershell command: ‘Get-SPUsageDefinition’

This command returns a usage definition object. The default data retention period is 14 days.

Separately change retention for some definition

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

Set-SPUsageDefinition -Identity "Sandboxed Requests" -DaysRetained 3

Set-SPUsageDefinition -Identity "Content Import Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Workflow" -DaysRetained 3

Set-SPUsageDefinition -Identity "Clickthrough Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Content Export Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Page Requests" -DaysRetained 3

Set-SPUsageDefinition -Identity "Feature Use" -DaysRetained 3

Set-SPUsageDefinition -Identity "Search Query Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Site Inventory Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Sandboxed Requests Monitored Data" -DaysRetained 3

Set-SPUsageDefinition -Identity "Timer Jobs" -DaysRetained 3

Set-SPUsageDefinition -Identity "Rating Usage" -DaysRetained 3


Command for all definitions

Get-SPUsageDefinition | ForEach-Object {Set-SPUsageDefinition -Identity $_.name -DaysRetained 3}



Once that's finished, Get-SPUsageDefinition command should confirm that everything has been set back to 3 day.

SharePoint:

After that we need to run the two timer jobs to clean the old data 'Microsoft SharePoint Foundation Usage Data Import' and 'Microsoft SharePoint Foundation Usage Data Processing'.

Go to Sharepoint Central Administration -> Monitoring -> Configure Usage and health data collection-> Log Collection Schedule.

Click on both the Job Definitions one by one and hit 'Run Now' to run the timer jobs.

SQL:

After that, you can use SQL Management Studio to shrink the database back to a more manageable size on disk.



That's it!

Get last access date/time for all SharePoint site collections with Powershell

This script gets list of all administrators and last access date/time for each SharePoint site collection.
The script saves output in tab separated format (.csv) file. 


Save code as .ps1 file and start in Windows PowerShell

#Set file location for saving information. We'll create a tab separated file.
$FileLocation = "C:\Temp\Report.csv"


#Load SharePoint snap-in
Add-PSSnapin Microsoft.SharePoint.PowerShell

#Add color
function Receive-Color
{
    process { Write-Host $_ -ForegroundColor Green }
}

#Fetches webapplications in the farm
$WebApplications = Get-SPWebApplication -IncludeCentralAdministration
Write-Output "URL `t Site Collection Owner `t Site Collection Secondary Owner `t Site Collection Admin `t Last Access date `t ContentModified" | Out-file $FileLocation

foreach($WebApplication in $WebApplications){
    #Fetches site collections list within sharepoint webapplication
    Write-Output ""
    Write-Output "Working on web application $($WebApplication.Url)" | Receive-Color
    $Sites = Get-SPSite -WebApplication $WebApplication -Limit All
    foreach($Site in $Sites)
    {   
    $Admins=""
   #Get all Site Collection Administrators
      foreach ($siteCollAdmin in $Site.RootWeb.SiteAdministrators)
      {
        $Admins+= $siteCollAdmin.LoginName +";"
      }
      foreach($web in $Site.Allwebs)
      {
      $Lastaccessed = $web.LastItemModifiedDate
      $ContentModified = $Site.LastContentModifiedDate
    
      }
    
            #Fetches information for each  site
            Write-Output "$($Site.Url) `t $($Site.Owner.Name) `t $($Site.SecondaryContact.Name) `t $($Admins) `t $($Lastaccessed) `t $($ContentModified)" | Out-File $FileLocation -Append
            $Site.Dispose()
    }
}

#Unload SharePoint snap-in
Remove-PSSnapin Microsoft.SharePoint.PowerShell

Write-Output ""
Write-Output "Script Execution finished" | Receive-Color

 Ref: http://panky-sharma.blogspot.com/2016/08/last-access-date-and-time-for.html
 

Export all SharePoint solutions wsp using PowerShell script

Sometimes you may need to export all SharePoint 2010/2013 farm solutions as a backup process or to deploy them from staging environment to production environment.

Start Windows Management Shell as Administrator.

Add-PSSnapin Microsoft.SharePoint.PowerShell –erroraction SilentlyContinue

## setup our output directory
$dirName = "c:\FolderName"

Write-Host Exporting solutions to $dirName
foreach ($solution in Get-SPSolution)
{
    $id = $Solution.SolutionID
    $title = $Solution.Name
    $filename = $Solution.SolutionFile.Name

    Write-Host "Exporting ‘$title’ to …\$filename" -nonewline
    try {
        $solution.SolutionFile.SaveAs("$dirName\$filename")
        Write-Host " – done" -foreground green
    }
    catch
    {
        Write-Host " – error : $_" -foreground red
    }
}

Create New SharePoint site collection with new content database using powershell

How to create a new site collection in existing SharePoint web application but with new content database using powershell?

Save skript as .ps1 file and start in Windows PowerShell


Add-PSSnapin Microsoft.SharePoint.PowerShell –ErrorAction SilentlyContinue
$server = Read-Host "Enter SQL Server"
$dbname = Read-Host "Enter Database Name"
$webapp = Read-Host "Enter Web Application URL"
$site = Read-Host "Enter New Site Collection URL"
$language = Read-Host "Enter New Site Collection Language ID"
$scname = Read-Host "Enter New Site Collection Name"
$owner1 = Read-Host "Enter Primary Site Collection Admin"
$owner2 = Read-Host "Enter Secondary Site Collection Admin"
New-SPContentDatabase -Name $dbname -DatabaseServer $server -WebApplication $webapp | out-null
New-SPSite -Language $language -Name $scname -URL $site -OwnerAlias $owner1 -SecondaryOwnerAlias $owner2 -ContentDatabase $dbname | out-null
Get-SPContentDatabase -Site $site | Set-SPContentDatabase -MaxSiteCount 1 -WarningSiteCount 0
Write-Host " "
Write-Host "Site Collection at" $site "has been created in the" $dbname "content database" -ForegroundColor Yellow

Check server time for all servers in SharePoint farm

Servers hosting the Central Administration site should be configured to be in the same time zone. There are many settings that rely on specifying a time. If servers that run the Central Administration site are in more than one time zone, there can be confusion and conflicting input.

All servers that run the SharePoint 2010/2013 Timer Service should also be configured to be in the same time zone. Timer services run to complete a host of jobs such as deploy solutions, content deployment, alerts, workflow and more. A discrepancy in the time zones between servers can lead to synchronization issues. For example, deployment jobs may start on one server but not run on another server until much later


Solution

A PowerShell script that will check all of the server times for all servers in the SharePoint farm.
The script needs to run on one of the SharePoint servers in the farm using an account that has admin access on all servers in that farm.


Save skript as .ps1 file and start in Windows PowerShell


Add-PSSnapin Microsoft.SharePoint.PowerShell -EA 0
$servers = (Get-SPServer) | foreach {$_.Address} 
 
foreach($server in $servers)
{
 
    $time = Get-WmiObject Win32_LocalTime -computer $server  -EA 0
  
    $hour = $time.Hour
    $minute = $time.Minute
    $second = $time.Second
    Write-Host "$server current time is $hour : $minute : $second" -ForegroundColor Green
    
}
Write-host "If the server times are not in sync please adjust the time settings. Press any key to continue" -ForegroundColor Yellow
$null = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

Find large lists and large files in SharePoint with PowerShell

Detect large lists in SharePoint

Do you want to find out all the Large lists in your SharePoint site collection? This PowerShell script will help you to get the result.

$rootSiteCollectionUrl = "https://sbportal";
$sa = Start-SPAssignment -Global;
(Get-SPSite $rootSiteCollectionUrl).WebApplication.Sites | Foreach-Object {$_.AllWebs} | Foreach-Object {$_.Lists} | Where-Object {$_.ItemCount -ge 2000} | Format-Table Title,ItemCount,ParentWebUrl
$sa | Stop-SPAssignment;



Detect large files in SharePoint

The following PowerShell script would give you the list of files which are larger than 50MB in a SP web application. Just change the $filesize parameter based on your requirement and you should be good to go.



Add-PSSnapin Microsoft.SharePoint.PowerShell
Start-SPAssignment -Global
#Change the site url below
$Site = Get-SPSite https://sbportal     
$spWeb = $Site.WebApplication
#Enter the target file size in MB
$fileSize = 50
[string]$fileUrl
Write-Host "------Checking the SP web app for large files------"
# Enumerate though all site collections, sites, sub sites and document libraries in a SP web app
if($spWeb -ne $null)
{
foreach ($siteColl in $spWeb.Sites)
{
  foreach($subWeb in $siteColl.AllWebs)
   {
     foreach($List in $subWeb.Lists)
      {
        if($List.BaseType -eq "DocumentLibrary")
        {
          $ItemsColl = $List.Items
             foreach ($item in $ItemsColl)
           {   
             $itemSize = (($item.File.Length)/1024)/1024
              if($itemSize -Ge $fileSize)
             {
               $itemUrl = $item.Web.Url + "/" + $item.Url;
               Write-Host $itemUrl ", File size:: " $('{0:N2}' -f $itemSize) MB -ForegroundColor Green
             }
           }                   
        }
      }    
   }
}
}
Write-Host "---------DONE---------"
Stop-SPAssignment -Global

Make attachment required field in SharePoint list

Make attachment mandatory in SharePoint list we can use simple JavaScript.
Add content editor or script editor web part on "NewForm.aspx" near SharePoint list form web part. Then place the below code in it.

</pre>
<script type="text/javascript" language="javascript">
function PreSaveAction() {

var elm = document.getElementById("idAttachmentsTable");
 if (elm == null || elm.rows.length == 0)
{
 document.getElementById("idAttachmentsRow").style.display='none';
alert("Please attach resume");
return false ;
}
else { return true ;}
}
</script>



If attachment not found, it will give an alert with message “Please attach resume”.

PowerShell Script to export SharePoint List in Excel File

This script exports SharePoint list to csv using PowerShell. The script is suitable if you want to export list from Task Scheduler.

Start PowerShell as Administrator

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "https://project/test"

#Get the Target List
$list = $web.Lists["TestList"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items
 $list.Items | foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "City" -value $_["City"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job" -value $_["Job"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Department" -value $_["Department"]

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.csv" -NoTypeInformation                      

#Dispose the web Object
$web.Dispose()

When you insert in Excel

If you want to filter list export just insert line  Where-Object { $_["City"] -eq "New York"}


#Get the Web
$web = Get-SPWeb -identity "https://sbprojekti/test"

#Get the Target List
$list = $web.Lists["TestList"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items
 $list.Items |  Where-Object { $_["City"] -eq "New York"} |foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "City" -value $_["City"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job" -value $_["Job"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Department" -value $_["Department"]

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.csv" -NoTypeInformation                      

#Dispose the web Object
$web.Dispose()

Restore a deleted SharePoint Site Collection without a recent backup

Accidentally deleted a SharePoint site collection? No problem, you can restore it without resorting to database backups.

Solution:

Start SharePoint Management Shell as Administrator and run command Get-SPdeletedsite. You will see your site collection here with Site ID


Now restore the site collection using this site ID Restore-SPDeletedSite –Identity “ID"


Site Collection is back!

Set standard date format to all datatime columns in SharePoint

After migrating content to SharePoint 2016, all date columns were in Friendly display format. Here is a way to update them across the site collection using PowerShell.

Run Microsoft PowerShell as Administrator


Add-PSSnapin "Microsoft.SharePoint.Powershell" -ErrorAction SilentlyContinue
$WebCollection = Get-SPSite "SiteUrl" -Limit All | Get-SPWeb -Limit All

foreach($site in $WebCollection){

$lists = $site.Lists

foreach($list in @($lists)){

Write-Host "Connected to $list" -ForegroundColor Yellow

foreach($field in @($list.Fields)){

$column = $list.Fields[$field.Title]

    if($column.Type -eq "DateTime"){
                    
   Write-Host "Connected to " $column.Title -ForegroundColor Yellow
   $column.FriendlyDisplayFormat = 1
   $column.update()
               
            }
        }
    }
}




You can also limit the list/library you want to do this on by using a where clause in the lists area like the following:

foreach($list in $lists |?{$_.Title -like "ListName"}){}

Find email listed for access requests in SharePoint

This script will walk through your web application and list the site name, url, and email address for all of your sites in SharePoint. Subsites are included.

When a SharePoint site is created, by default the creator’s email address is automatically populated into the “Manage Access Requests”. However, sometimes the creator isn’t the site owner, and doesn’t handle the day-to-day access requests for the site.

Start PowerShell as Administrator

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue

#Starting web app
$site = “https://siteUrl”

# Function: FindAccessEmail
# Description: Go through a target web application and list the title, url and access request email.
function FindAccessEmail
{
$WebApps = Get-SPWebApplication($site)
foreach($WebApplication in $WebApps)
{
foreach ($Collection in $WebApplication.Sites)
{
foreach($Web in $Collection.AllWebs)
{
$siteDetails = $Web.title+’#’+$Web.url+’#’+$Web.RequestAccessEmail
write-host $siteDetails
Write-Output $siteDetails
}
}
}
}
#Run Script!
FindAccessEmail | Out-File -filepath C:\Temp\AccessRequestEmails.csv

Copy the value of one column to another column in the same SharePoint List using Powershell script

Powershell Script to Copy the value of one column to another column in the same SharePoint List.


$site = new-object Microsoft.SharePoint.SPSite("http://localhost")
$web =  Get-SPWeb -Identity http://localhost
$list =$web.Lists["List/Library Name"]
$items = $list.items
    foreach ($item in $items)
    {
    $sourcevalue = $item["Column 1"]
    $item["Column 2"] = $sourcevalue
    write-host $sourcevalue
    $item.update()
    }

$list.update()

Disable list throttling just for one SharePoint list with PowerShell

You have a list that is in the process of getting cleaned up, but you’ve gotta leave your throttle up for functionalities sake until it’s resolved. The list throttle is a Web Application Level setting, so any list/library in any site in any Site Collection in your web app is affected and you don’t know what other lists have now creeped over the limit and  will break once you reduce the threshold back down to the recommended limit of 5000.

Here is a powershell script that will disable the throttle for just one list, that way all of the lists can continue to adheed to the throttle limit while this one particular list can continue to function around it.


Disable throttle for SharePoint list:

$mywebsite = Get-SPWeb “http://portal/site”
$mySPList = $mywebsite.Lists[“ListName”]
$mySPList.EnableThrottling = $false
$mySPList.Update()




Verify throttle for SharePoint list:

$mySPList.IsThrottled




Enable throttle for SharePoint list:

$mySPList.EnableThrottling = $true
$mySPList.Update()


Ref:https://www.techrevmarrell.com/throttle-throttle-disable-the-throttle/

Find All Alerts for all Users in Entire Site collection - SharePoint

How to see all alerts for all users?


Start Sharepoint Management Shell as Administrator

Add-PSSnapin Microsoft.SharePoint.PowerShell
$SPSiteCollection = Get-SPSite “https://SiteCollectionName”

$object = foreach($SpWeb in $SPSiteCollection.AllWebs)
{
foreach($alert in $SpWeb.Alerts)
{
Write-Output “$($alert.AlertFrequency),$($alert.user),$($alert.ListUrl),$($alert.title),”
}
}
$object | Out-file ‘C:\Temp\output.txt’ -Append


Ref:https://www.techrevmarrell.com/get-all-user-alerts-for-sharepoint-site-collection/

MySite - Newsfeed cache size increase SharePoint 2013

On the Newsfeed section of MySite we can see the posts added by colleagues and events on different entities we follow. Newsfeed functionality is supported by Distributed Cache service. The feeds are stored on distributed cache and displayed from it.
There are some settings on User Profile application related to feed cache

Start Sharepoint Management Shell as Administrator

$upa = Get-SPServiceApplication | where {$_.TypeName -Like "User Profile Service Application"}
$upa.FeedCacheTTLHours = 168
$upa.FeedCacheLastModifiedTimeTtlDeltaHours=168
$upa.FeedCacheObjectCountLimit=500
$upa.FeedCacheRoomForGrowth=200
$upa.Update()





"FeedCacheTTLHours" - The default Time To Live of entries in feed cache in hours.
Default value is 168

"FeedCacheLastModifiedTimeTtlDeltaHours" - Additional time added to keep FeedCacheTTLHours for LMT entries. LMT items are kept for 14 days. 7 days for FeedCacheTTLHours plus 7 days for FeedCacheLastModifiedTimeTtlDeltaHours.

"FeedCacheObjectCountLimit" - The maximum number of posts for a given entity that can exist in feed cache.
Default value is 500.

 "FeedCacheRoomForGrowth" - The amount of posts to delete when the number of posts for a given entity reaches FeedCacheObjectCountLimit.
 Default value is 200

Change certificate - Office Web Apps

Certificate has been expired and now we need to configure new certificate for existing web farm.
How to change office web apps certificate with powershell?


Before PowerShell command import certifikate in Personal Certifikate Store.


Open a Microsoft PowerShell window as Administrator.

Set-OfficeWebappsFarm -CertificateName "CertifikateFriendlyName"


After this command server restart is required.

Get number of subsite in a site collection - SharePoint

How to get number of subsites in a site collection using Powershell?

$site = Get-SPSite http://YourSharePointSite
$site.AllWebs.Count

Get number of all files on SharePoint farm

How to get number of all files in libraries in SharePoint farm

Add-PSSnapin Microsoft.SharePoint.PowerShell

Start-SPAssignment -Global

$OutputFile = “C:\Temp\DocCount.csv”

$results = @()

$webApps = Get-SPWebApplication

foreach($webApp in $webApps)

{

    foreach($siteColl in $webApp.Sites)

    {

        foreach($web in $siteColl.AllWebs)

        {

            $webUrl = $web.url

            $docLibs = $web.Lists | Where-Object {$_.baseType -eq “DocumentLibrary”}

            $docLibs | Add-Member -MemberType ScriptProperty -Name WebUrl -Value {$webUrl}

            $results += ($docLibs | Select-Object -Property WebUrl, Title, ItemCount)

        }

    }

}

$results | Export-Csv -Path $OutputFile -NoTypeInformation

   

Stop-SPAssignment -Global

Delete list items older than date - SharePoint, PowerShell

You need to delete SharePoint items older than date? It's easy with PowerShell.


Start Sharepoint Management Shell as Administrator

 Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
     
        #Configure target site and list.
     
        $list = $($(Get-SPWeb -Identity 'https://portal/test').Lists['ListName'])
     
        #Index count for list items.
     
        $index = $list.ItemCount
     
        #Index counter for paging.
     
        $page = 0
     
        #Configure how many items to delete per batch.
     
        $pagesize = 3000
     
        #Configure how may seconds to pause between batches.
     
        $sleep = 1
     
        #Turn verbose output on/off
     
        $verbose = $true
     
        While($index -ge 0){
     
        if($verbose){
     
        $("Check item at index $($index).")
     
        }
     
        if($page -lt $pagesize){
     
        try{
     
        if($($list.Items[$index])['DateField'] -lt [DateTime]::Parse("11/7/2017")){
     
        $list.Items[$index].Delete()
     
        write-host "Deleting item at index $($index)." -foregroundcolor "green"
     
        }
     
        }
     
        catch [System.Exception]{
     
        if($verbose){
        $("Skipping item at index $($index).")
     
        }
     
        }
     
        $index--
     
        $page++
     
        }
     
        else{
     
        if($verbose){
     
        $("Sleeping for $($sleep) seconds.")
     
        }
     
        [System.Threading.Thread]::Sleep($sleep * 1000)
     
        $page = 0
     
        }
     
        }