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!