Recreate my site for a specific user - SharePoint

 How to recreate my site for specific user?

After delete the site collection you also must clear a value in the User Profile.

• Navigate to the User Profile Service in Central Admin (Manage Service Applications),
• Click "Manage User Profiles"
• Find the profile of the user by searching
• Click on it and select "Edit User Profile"
• Find "Personal site:" and empty the field
• Find "Feed service provider defined identifier" and empty that field as well
• Click "Save and Close"

Now, when the user tries to access his/her mysite, a new one will be created.


Ref: https://sharepoint.stackexchange.com/questions/97858/how-do-i-recreate-a-my-site-for-a-specific-user

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

Transfer Logins to Another SQL Server or Instance

You run the script, it creates a stored procedure called sp_help_revlogin on your SQL Server. When you run the stored procedure, it outputs CREATE LOGIN statements for all your server logins including their passwords and “sids”. You can then take that script, run it on your new SQL Server to set up the server logins there

Server DB1

Open a new Query Editor window, and then run the following script.


USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO




Run the following statement in the same or a new query window:

EXEC sp_help_revlogin


The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

Server DB2

Open a new Query Editor window, and then run the output script that's generated of the preceding procedure.



Ref: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

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!

Add a document library\list webpart on another site (IsDlg=1) Sharepoint

In this post you can see how to add a document library\list webpart on another site and hide some thing in ribbon.


I suggest you display document library in another site using Page View web part. The link set for Page View web part is <your library URL>/Forms/AllItems.aspx?IsDlg=1.

Add Page View web part and add link <your library URL>/Forms/AllItems.aspx?IsDlg=1

We can hide the ribbon, and other stuff on the page.

Hide ribbon

You will need to put this code on original location of document library.

Add Content Editor web part and put code in.

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>

<script type="text/javascript">

_spBodyOnLoadFunctionNames.push("hideItAll");

function hideItAll(){
 if(window.location.search.toLowerCase().indexOf("isdlg=1") > 0){
 $("#s4-ribbonrow").hide(); //ribbon bar
 }
}
</script>

Hide "new document"

Add code also on original location of document library


<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript">

_spBodyOnLoadFunctionNames.push("hideItAll");
function hideItAll(){
 if(window.location.search.toLowerCase().indexOf("isdlg=1") > 0){
 $("#s4-ribbonrow").hide(); //ribbon bar

 //because the bar joins us late in the game, we need to throw CSS in to hide it
 $("head").append("<style>#Hero-WPQ2 { display:none }</style>");

 }
}
</script>

Ref:
https://social.technet.microsoft.com/Forums/office/en-US/6bc6f9eb-2dbc-427b-a11d-f9cbe0988978/adding-a-document-library-webpart-on-another-site-with-columns?forum=sharepointgeneral
https://davidlozzi.com/2014/10/28/sharepoint-hiding-ribbon-and-more-with-isdlg/

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
 

After installing September windows patches, SharePoint Workflows stop working

A recent .NET security patch, KB 4457916, broke Workflows in SharePoint.
All SharePoint out of the box Workflows fail to execute and publish.

Errors

Unable to publish SharePoint designer workflows

(-1, -1) Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.)
(0, 0) Activity 'ID5' validation failed: Property "Condition" has invalid value. Condition expression is invalid. The condition expression can not be null.)

Unable to start  SharePoint workflows

Solution

The solution is to add explicitly the types to all web applications' web.config:

              <authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeBinaryOperatorExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodePrimitiveExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeMethodInvokeExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeMethodReferenceExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeFieldReferenceExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeThisReferenceExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodePropertyReferenceExpression" Authorized="True" />



Or (for SharePoint 2007 and 2010):

              <authorizedType Assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeBinaryOperatorExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodePrimitiveExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeMethodInvokeExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeMethodReferenceExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeFieldReferenceExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodeThisReferenceExpression" Authorized="True" />

              <authorizedType Assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" NameSpace="System.CodeDom" TypeName="CodePropertyReferenceExpression" Authorized="True" />

 After that, workflow should be working fine!


This blog post on MSDN provides the solution

After installing .NET security patches to address CVE-2018-8421, SharePoint workflows stop working (KB 4457916)

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

Show names of attachments from a SharePoint list item in a column

If you want to display the name of the attachment and click on the name to open the document in list view, here is a solution.

• Enable the "Attachments" field in list view.

• Save the following code as a js file(showAttachments.js) and then upload the file into Site Assets document library.

(function () {
    (window.jQuery || document.write('<script src="//code.jquery.com/jquery-3.1.0.min.js"><\/script>'));
    //Create object that have the context information about the field that we want to change it output render 
    var linkFiledContext = {};
    linkFiledContext.Templates = {};
    linkFiledContext.Templates.Fields = {       
        "Attachments": { "View": AttachmentsFiledTemplate }
    };
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(linkFiledContext);
})();

// This function provides the rendering logic for list view
function AttachmentsFiledTemplate(ctx) {
    var itemId = ctx.CurrentItem.ID;
    var listName = ctx.ListTitle;      
    return getAttachments(listName,itemId);
}

function getAttachments(listName,itemId) {
 
    var url = _spPageContextInfo.webAbsoluteUrl;
    var requestUri = url + "/_api/web/lists/getbytitle('" + listName + "')/items(" + itemId + ")/AttachmentFiles";
    var str = "";
    // execute AJAX request
    $.ajax({
        url: requestUri,
        type: "GET",
        headers: { "ACCEPT": "application/json;odata=verbose" },
        async: false,
        success: function (data) {
            for (var i = 0; i < data.d.results.length; i++) {
                str += "<a href='" + data.d.results[i].ServerRelativeUrl + "'>" + data.d.results[i].FileName + "</a>";
                if (i != data.d.results.length - 1) {
                    str += "<br/>";
                }               
            }         
        },
        error: function (err) {
            //alert(err);
        }
    });
    return str;
}

• Add the following reference in JSLINK in list view web part

~site/SiteAssets/showAttachments.js

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

Resize Promoted Links in SharePoint

A simple way to change size of Promoted Links in SharePoint using CSS.
Add the following CSS inside a Content Editor web part.


<style type="text/css">
/* The height of the Promoted Link Webpart */
.ms-promlink-body {
     height: 100px;
}

/*  Tile and border size. Delta between ms-tileview-tile-root and size of the promoted link tiles is the space between tiles  */
.ms-tileview-tile-root {
     width: 105px! important;
     height: 105px !important;
}

/*  size of the promoted link tiles */
.ms-tileview-tile-content, .ms-tileview-tile-detailsBox {
     width: 100px !important;
     height: 100px !important;
}

/*  size of the image within the promoted link tile */
.ms-tileview-tile-content > a > img {
     width: 100% !important;
     height: 100% !important;
}

/* Changes the Promoted Link Title when you hover over it*/
.ms-tileview-tile-detailsListMedium {
     font-weight: bold;
     height: 100px;
     padding: 4px 7px 7px;
     font-size: 12px;
     line-height: 14px;
}

/* Changes the Promoted Link Description when you hover over it*/
.ms-tileview-tile-descriptionMedium {
     font-weight: normal;
     padding-top: 10px;
     font-size: 11px;
     /*  If you want to change the font size, change above */
}

/*  Remove this if you only what to show the images and not the text */
/*  Adjust the text in the Collapsed Promoted Link */
.ms-tileview-tile-titleTextMediumCollapsed {
     font-weight: normal;
     background-color: rgba(0, 0, 0, 0.6);
     width: 100px;
     height: 40px;
     position: absolute;
     top: -20px;
     left: 0px;
     text-align: center;
     vertical-align: middle;
     font-size: 11px;
     /*  If you want to change the font size, change above */
     line-height: 20px;
     word-wrap: break-word;
}

</style>

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”.

Adapt SharePoint Calendar Size using CSS

We add SharePoint Calendar Web part on the Home page, calendar size is too big as shown below. It covers the most of the area of the Home page.

Size of the Calendar can be adapted using CSS.

Add Content Editor Web part near Calendar Web part and insert code

<style type="text/css">
.ms-acal-item{height: 10px !important;}
.ms-acal-sdiv,
.ms-acal-mdiv,
.ms-acal-ctrlitem,
.ms-acal-month-weeksel,
.ms-acal-title,
.ms-acal-month-top span{display: none;}
.ms-acal-summary-itemrow TD DIV
{
height: 25px !important;
}
</style>

Note: Height gets reduces by style and width gets adjusted according to the space available.
Width can adapt in "Edit Web part" in Appearance section.

Traffic light on calculated column - SharePoint

A traffic light column is a great addition to any SharePoint list, it allows you to quickly recognise high priority tasks.

Here is an elegant way to easily display traffic lights on your SharePoint list, without the need for icons or similar. We can use his calculated column formula in HTMLCC, without having to worry about the JavaScript side of things.


Create column "Priority"

Create column "Traffic lights"

Insert code:

=IF(ISERR(RIGHT(LEFT([Priority],2),1)-1),"","<DIV style='font-weight:bold; font-size:24px; color:"&CHOOSE(RIGHT(LEFT([Priority],2),1),"red","orange","green")&";'>&bull;</DIV>")

Choose Number (1, 1.0, 100)

That's it!

Copy a SharePoint Designer List Workflow to Another Site

I had to migrate a SharePoint Designer 2013 workflow to an environment. The workflow was exported to visio as a .vwi file. Unfortunately when the I tried to import the file using the “Import as visio” option from SharePoint Designer, the message was appearing

“This workflow cannot be imported because it was created in SharePoint Designer for a different site, or the original workflow has been moved or deleted.”

Solution:

• Open the site you want to copy the workflow from and click Workflows under Site Objects from the left navigation
• Select the workflow and click Export to Visio from the designer ribbon to save the file
• Change the extension of the exported workflow from .vwi to .zip
• Open the .zip file and remove workflow.xoml.wfconfig.xml file

• Rename .zip file back to .vwi

• From SharePoint Designer 2013, open the site where the workflow will be migrated

• Click on Workflows tab

• Click Import from Visio and browse to select the file and click Next

• If the workflow is a reusable workflow, pick the reusable workflow option else if the workflow is a list workflow , pick the “List Workflow” option and select the list. 

• Click Save.
• Click Publish to make the workflow available to the site

Ref: https://aniketahmed.wordpress.com/2013/08/02/305/

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()

Alternating row shading in a SharePoint list

The list view display of items in a SharePoint List can be updated to have alternating row colour and border set by using CSS.
Alternating list row colors is quick fix that makes your data easier on your viewers’ eyes and helps with user adoption, especially those coming from programs and platforms that had alternating row colors built-in.

Add a content editor Web Part in the page, add the following css code in it:


<style type="text/css">
.ms-listviewtable > tbody > tr {
background: white;
}

.ms-listviewtable > tbody > tr td{
border-bottom: 1px dotted black !important; }

.ms-listviewtable > tbody > tr.ms-alternating {
background: lightgrey
}
</style>

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()

SharePoint Script Editor and Chrome ERR_BLOCKED_BY_XSS_AUDITOR

In recent Chrome browser, it has become little annoying to work with SharePoint tool due to Chrome throwing ERR_BLOCKED_BY_XSS_AUDITOR error when working with the Script Editor web part. This is due to XSS auditor on Chrome blocking JavaScript included in the Script Editor Web Part. Issue occurs on SharePoint Online and on-premises SharePoint 2016 and 2013 and occurs regardless if site is accessed via HTTP or HTTPS.


Problem:

This page isn’t working
    Chrome detected unusual code on this page and blocked it to protect your personal information (for example, passwords, phone numbers, and credit cards).
        Try visiting the site's homepage.
    ERR_BLOCKED_BY_XSS_AUDITOR

Solution:

You can disable the XSS Auditor by appending the following value to web.config on your on-premises SharePoint 2013 or 2016 web site.

  <system.webServer>
    <httpProtocol>
      <customHeaders>
        <add name="X-XSS-Protection" value="0" />
      </customHeaders>


Ref:https://blog.jussipalo.com/2017/03/sharepoint-workaround-for-script-editor.html

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/

SQL script - schema changes history

This SQL script reads events from the default trace, if it is enabled.
It's displays events for when any object changes occured (CREATE, DROP, ALTER).


declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
declare @temp_trace table (
 obj_name nvarchar(256) collate database_default
, database_name nvarchar(256) collate database_default
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256) collate database_default
, login_name nvarchar(256) collate database_default
, application_name nvarchar(256) collate database_default
, ddl_operation nvarchar(40) collate database_default
);

select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX('%\%', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT(@curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

insert into @temp_trace
select ObjectName
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, ApplicationName
, 'temp'
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass in (46,47,164) and EventSubclass = 0 and
DatabaseID <> 2

update @temp_trace set ddl_operation = 'CREATE' where
event_class = 46
update @temp_trace set ddl_operation = 'DROP' where
event_class = 47
update @temp_trace set ddl_operation = 'ALTER' where
event_class = 164

select @d1 = min(start_time) from @temp_trace
set @diff= datediff(hh,@d1,getdate())
set @diff=@diff/24;

select @diff as difference
, @d1 as date
, object_type as obj_type_desc
, *
from @temp_trace where object_type not in (21587)
order by start_time desc

Here is ObjectType Reference from Default Trace

1 Index
2 Database
3 User Object
4 CHECK Constraint
5 Default or DEFAULT Constraint
6 FOREIGN KEY Constraint
7 PRIMARY KEY Constraint
8 Stored Procedure
9 User-Defined Function (UDF)
10 Rule
11 Replication Filter Stored Procedure
12 System Table
13 Trigger
14 Inline Function
15 Table Valued UDF
16 UNIQUE Constraint
17 User Table
18 View
19 Extended Stored Procedure
20 Ad hoc Query
21 Prepared Query
8259 Check Constraint
8260 Default (constraint or standalone)
8262 Foreign-key Constraint
8272 Stored Procedure
8274 Rule
8275 System Table
8276 Trigger on Server
8277 (User-defined) Table
8278 View
8280 Extended Stored Procedure
16724 CLR Trigger
16964 Database
16975 Object
17222 FullText Catalog
17232 CLR Stored Procedure
17235 Schema
17475 Credential
17491 DDL Event
17741 Management Event
17747 Security Event
17749 User Event
17985 CLR Aggregate Function
17993 Inline Table-valued SQL Function
18000 Partition Function
18002 Replication Filter Procedure
18004 Table-valued SQL Function
18259 Server Role
18263 Microsoft Windows Group
19265 Asymmetric Key
19277 Master Key
19280 Primary Key
19283 ObfusKey
19521 Asymmetric Key Login
19523 Certificate Login
19538 Role
19539 SQL Login
19543 Windows Login
20034 Remote Service Binding
20036 Event Notification on Database
20037 Event Notification
20038 Scalar SQL Function
20047 Event Notification on Object
20051 Synonym
20549 End Point
20801 Adhoc Queries which may be cached
20816 Prepared Queries which may be cached
20819 Service Broker Service Queue
20821 Unique Constraint
21057 Application Role
21059 Certificate
21075 Server
21076 Transact-SQL Trigger
21313 Assembly
21318 CLR Scalar Function
21321 Inline scalar SQL Function
21328 Partition Scheme
21333 User
21571 Service Broker Service Contract
21572 Trigger on Database
21574 CLR Table-valued Function
21577 Internal Table (For example, XML Node Table, Queue Table.)
21581 Service Broker Message Type
21586 Service Broker Route
21587 Statistics
21825 User
21827 User
21831 User
21843 User
21847 User
22099 Service Broker Service
22601 Index
22604 Certificate Login
22611 XMLSchema
22868 Type

Display Folder Child Count and Item Child Count Columns - SharePoint

Edit the view on a SharePoint Document Library and mark the checkboxes next to Folder Child Count and Item Child Count Columns

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

User with Full Control cannot Create Subsites in SharePoint

When a user with full control want to create subsite in the site collection he get message:
“Sorry, you don’t have access to this page”

Taxonomy Hidden List list was not inheriting security and was found to be stripped bare of any user or group listings.

SharePoint Taxonomy Hidden List is a storage area for specific term store information for the SharePoint managed metadata service.

The list can be reached through the UI using the following URL [site]/lists/taxonomyhiddenlist.

The solution is simple, give the SharePoint Taxonomy Hidden List read access for all Authenticated Users operating within the Site Collection.