Site usage report script

 Add-PSSnapin "Microsoft.SharePoint.PowerShell"

function ExportRollupAnalyticsData {

    Param(

    [string]$RootSiteUrl,

    [string]$Scope,

    [switch]$IncludeSites,

    [switch]$IncludeWebs,

    [string]$OutputFilePath

    )

    # Delete CSV file if existing

    If (Test-Path $OutputFilePath) {

    Remove-Item $OutputFilePath

    }

    # Write header row to CSV File

    $OutputHeader = "Scope,Name,URL,Most Recent Day with Usage,Hits - All Time,Unique Users - All Time,Hits - Most Recent Day with Usage,Unique Users - Most Recent Day with Usage,Current Date"

    $OutputHeader | Out-File $OutputFilePath -Append 

    # Get Web Application for Root Site

    $RootSite = Get-SPSite $RootSiteUrl

    $WebApp = $rootSite.WebApplication 

    # Get Search Service Application

    $SearchApp = Get-SPEnterpriseSearchServiceApplication

    # Loop through all Site Collections in Web Application

    ForEach($Site in $WebApp.Sites) {    

        

        # Export Site analtyics if -IncludeSites flag is present

        If ($IncludeSites.IsPresent) {

            $Scope = "Site"

            $SiteTitle = $Site.RootWeb.Title.Replace(",", "")

            $SiteUrl = $Site.Url

            $UsageData = $SearchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$Site.ID,[System.Guid]::Empty)

            $LastProcessingTime = $UsageData.LastProcessingTime

            $CurrentDate = $UsageData.CurrentDate

            $TotalHits = $UsageData.TotalHits

            $TotalUniqueUsers = $UsageData.TotalUniqueUsers

            $LastProcessingHits = $UsageData.LastProcessingHits

            $LastProcessingUniqueUsers = $UsageData.LastProcessingUniqueUsers

            # Write Web details to CSV File

            $OutputString = $Scope + "," + $SiteTitle + "," + $SiteUrl + "," + $LastProcessingTime + "," + $TotalHits + "," + $TotalUniqueUsers + "," + $LastProcessingHits + "," + $LastProcessingUniqueUsers + "," + $CurrentDate

            $OutputString | Out-File $OutputFilePath -Append

        }      

        # Export Web analtyics if -IncludeWebs flag is present

        If ($IncludeWebs.IsPresent) {

            

            # Loop through all Webs in Site Collection

            ForEach($Web in $Site.AllWebs) {

                $Scope = "Web"

                $WebTitle = $Web.Title.Replace(",", "")

                $WebUrl = $Web.Url

                $UsageData = $SearchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$Site.ID,$Web.ID)

                $LastProcessingTime = $UsageData.LastProcessingTime

                $CurrentDate = $UsageData.CurrentDate

                $TotalHits = $UsageData.TotalHits

                $TotalUniqueUsers = $UsageData.TotalUniqueUsers

                $LastProcessingHits = $UsageData.LastProcessingHits

                $LastProcessingUniqueUsers = $UsageData.LastProcessingUniqueUsers

                # Write Web details to CSV File

                $OutputString = $Scope + "," + $WebTitle + "," + $WebUrl + "," + $LastProcessingTime + "," + $TotalHits + "," + $TotalUniqueUsers + "," + $LastProcessingHits + "," + $LastProcessingUniqueUsers + "," + $CurrentDate

                $OutputString | Out-File $OutputFilePath -Append 

            }

        }

         

        # Dispose Site Collection Object

        $Site.Dispose()

    }

}

# Sample Usage: Export both SPSite and SPWeb analytics 

ExportRollupAnalyticsData -RootSiteUrl "https://Webapp1.sharepoint.com" -OutputFilePath "D:\Sathyam\UsageReports\Webapp1-analytics-export-sites-webs.csv" -IncludeSites -IncludeWebs

# Sample Usage: Export only SPSite analytics

ExportRollupAnalyticsData -RootSiteUrl "https://Webapp1.sharepoint.com" -OutputFilePath "D:\Sathyam\UsageReports\Webapp1-analytics-export-sites-only.csv" -IncludeSites

# Sample Usage: Export only SPWeb analytics

ExportRollupAnalyticsData -RootSiteUrl "https://Webapp1.sharepoint.com" -OutputFilePath "D:\Sathyam\UsageReports\Webapp1-analytics-export-webs-only.csv" -IncludeWebs

#Get-SPWebApplication

Script to get all subsites size from Sharepoint farm

 Add-PSSnapin "*Sharepoint*"

#Get Size of all Sub-sites in a sharepoint Farm

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

# Function to calculate folder size

Function CalculateFolderSize($Folder)

{

    [long]$FolderSize = 0

    foreach ($File in $Folder.Files)

    {

        #Get File Size

        $FolderSize += $file.TotalLength;

        #Get the Versions Size

        foreach ($FileVersion in $File.Versions)

        {

            $FolderSize += $FileVersion.Size

        }

    }

    #Iterate through all subfolders

    foreach ($SubFolder in $Folder.SubFolders)

    {

        #Call the function recursively

        $FolderSize += CalculateFolderSize $SubFolder

    }

    return $FolderSize

}

$sizelog = "D:\Stage_subsites_Size.csv" 

Add-Content -Path $sizelog -Value "WebappURL,Sitecollection_URL,WebUrl,Size"

$webapps = Get-SPWebApplication

foreach($WebApp in $webapps)

{

Write-Host "Running Web application" $WebApp.Url

 $Sites = Get-SPSite -WebApplication $WebApp -Limit All

 Write-Host "Site collections" $Sites

    

   foreach ($site in $Sites)

   {

   Write-Host "Running site" $site

#$Site1 = new-object Microsoft.SharePoint.SPSite($site)

foreach($Web in $site.AllWebs)

{ 

 Write-Host "Running web" $web

    #Call function to calculate Folder Size

    [long]$WebSize = CalculateFolderSize($Web.RootFolder)

    #Get Recycle Bin Size

    foreach($RecycleBinItem in $Web.RecycleBin)

    {

        $WebSize += $RecycleBinItem.Size

    }

    $Size = [Math]::Round($WebSize/1MB, 2)

    Write-Host $web.Url ":`t" $Size "MB"

    $CSVoutput = $webapp.url + "*" + $site.url +"*"+ $web.Url + "*" + $Size

    $csvoutput | Out-File $sizelog -Append

}}}

Site collection size report

 Add-PSSnapin microsoft.sharepoint.powershell

$SizeLog = "D:\SPSiteSize.csv"

############################################################

$CurrentDate = Get-Date -format d

$WebApps = Get-SPWebApplication

foreach($WebApp in $WebApps)

{

$Sites = Get-SPSite -WebApplication $WebApp -Limit All

foreach($Site in $Sites)

{

$SizeInKB = $Site.Usage.Storage

$SizeInGB = $SizeInKB/1024/1024/1024

$SizeInGB = [math]::Round($SizeInGB,2)

$CSVOutput = $Site.RootWeb.Title + "*" + $Site.URL + "*" + $Site.ContentDatabase.Name + "*" + $SizeInGB + "*" + $CurrentDate

$CSVOutput | Out-File $SizeLog -Append

}

}

$Site.Dispose()

AZ-900 | Microsoft Azure Fundamentals Full Course, Free Practice Tests, Website and Study Guides

 https://www.youtube.com/watch?v=NPEsD6n9A_I&list=PLGjZwEtPN7j-Q59JYso3L4_yoCjj2syrM

How to get workflow manager server information from SharePoint servers.

 Add-PSSnapin microsoft.sharepoint.powershell

$site = Get-SPSite http://sharepoint.contaso.com

$proxy = Get-SPServiceApplicationProxy | ?{$_.TypeName -eq 'Workflow Service Application Proxy'}

$proxy.GetHostname($site)

Banner on sharepoint sites.

 Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

 

Function Add-CustomAction

{

    param

    (

        [parameter(Mandatory=$true, ParameterSetName='Site')]

        [Microsoft.SharePoint.SPSite]$Site,

         

        [parameter(Mandatory=$true, ParameterSetName='Web')]

        [Microsoft.SharePoint.SPWeb]$Web,

         

        [parameter(Mandatory=$true, ParameterSetName='Site')]

        [parameter(Mandatory=$true, ParameterSetName='Web')]

        [string]$Message,

         

        [parameter(Mandatory=$true, ParameterSetName='Site')]

        [parameter(Mandatory=$true, ParameterSetName='Web')]

        [string]$ActionName,

         

        [parameter(Mandatory=$false, ParameterSetName='Web')]

        [switch]$IncludeSubWebs,

 

        [parameter(Mandatory=$true, ParameterSetName='Site')]

        [parameter(Mandatory=$true, ParameterSetName='Web')]

        [string][ValidateSet("Red", "Yellow", "Green", "Blue")]$BackGroundColor

    )

 

    begin

    {

        # To avoid quote conflicts

        $Message = $Message.Replace( "`"", "'")

$startingSequence = 1100

        $JavaScript = @"

 

            function Execute_$($ActionName)_MessageBar_$($PSCmdlet.ParameterSetName)()

            {

                var st = document.getElementById('$ActionName');

                if(st != null)

                {

                    st.style.display = '';

                    return;

                }

 

                st = document.createElement("div");

                st.id = "$($ActionName)_MessageBar_$($PSCmdlet.ParameterSetName)";

                st.innerHTML = "<div class='ms-status-$($BackGroundColor.ToUpper())' style='padding: 12px 14px 6px; border: 1px solid; font-family: \"Segoe UI\", Tahoma, sans-serif; font-size: 13px; min-height: 24px; font-weight: bold; background-color:Yellow;'>$Message</div>";

 

                document.body.insertBefore(st, document.body.childNodes[0]);

            }

 

            // don't show on modal dialog windows

            if(!window.location.search.match("[?&]IsDlg=1"))

            {

                Execute_$($ActionName)_MessageBar_$($PSCmdlet.ParameterSetName)();

            }

"@

    }

    process

    {

        $customAction = $null

         

        if( $PSCmdlet.ParameterSetName -eq "Site" )

        {

            Remove-CustomAction -Site $site -ActionName $ActionName | Out-Null

            $customAction = $Site.UserCustomActions.Add()

        }

        else

        {

            Remove-CustomAction -Web $Web -ActionName $ActionName | Out-Null

            $customAction = $Web.UserCustomActions.Add()

        }

 

        $customAction.Location    = "ScriptLink"

        $customAction.Sequence    = $startingSequence

        $customAction.Title       = $ActionName

        $customAction.ScriptBlock = $JavaScript

        $customAction.Update()

 

        if( $IncludeSubWebs )

        {

            foreach($w in $Web.Webs)

            {

                Add-CustomAction -Web $w -Message $Message -ActionName $ActionName -IncludeSubWebs -BackGroundColor $BackGroundColor

            }

        }

    }

    end

    {

    }

}

 

Function Remove-CustomAction

{

    param 

    (

        [parameter(Mandatory=$true, ParameterSetName='Site')]

        [Microsoft.SharePoint.SPSite]$Site,

         

        [parameter(Mandatory=$true, ParameterSetName='Web')]

        [Microsoft.SharePoint.SPWeb]$Web,

         

        [parameter(Mandatory=$true, ParameterSetName='Site')]

        [parameter(Mandatory=$true, ParameterSetName='Web')]

        [string]$ActionName

    )

 

    begin

    {

        $existingActions = @()

    }

    process

    {

        if( $PSCmdlet.ParameterSetName -eq "Site" )

        {

            $Site.UserCustomActions | ? { $_.Title -eq $ActionName } | % { $existingActions += $_ }

            $existingActions | % { $Site.UserCustomActions.Item($_.Id).Delete() }

        }

        else

        {

            $Web.UserCustomActions | ? { $_.Title -eq $ActionName } | % { $existingActions += $_ }

            $existingActions

            $existingActions | % { $Web.UserCustomActions.Item($_.Id).Delete() }

        }

    }

    end

    {

    }

}

 

$sites = get-spsite "http://sharepoint.com/sites/test" #-Limit all -webapplication http://webapplicationurl/ 

foreach($site in $sites)

{

Add-CustomAction -Site $site -Message “We are planning to migrate this site to Sharepoint 2016 on date ********** The new site URL is 'https://sharepoingonline.com/sites/coc2' " -ActionName "SiteMovedBanner" -BackGroundColor "Red"

#Remove-CustomAction  -Site $site -ActionName "SiteMovedBanner"

}

 

Workflow manager certificate and service bus certificate renewal process

Note: This Article is created based on the experience in recent MI, Microsoft has clearly stated this process has to be done by approaching Microsoft. Please log MS ticket before doing this activity. below steps are supposed to be performed with Microsoft on the call.

Step1 : Workflow manager certificate and service bus certificate have both expired.

Workflow services will be automatically stopped once workflow manger certificate expires.

Step 2:

clock should be turned back to a time when the cert is still available, and run the commands on workflow manger server

 

# Check SB and WFM status

Get-sbfarm

Get-sbfarmstatus

Get-wffarm

Get-wffarmstatus

 

The actions we have taken:

1. Backup all WFM and Service Bus Databases. Take WFM server’s VM snapshot too.

 

2. Make sure that the clock is turned back to a time when the cert is still available.

 

3. Run some commands to generate new certificates.

$CertKey=convertto-securestring "YourSecretPassword" -asplaintext -force

Set-WFCertificateAutoGenerationKey -Key $CertKey

Set-SBCertificateAutogenerationKey -Key $CertKey 

 

4. Open MMC to import certificates to 'Trusted Root Certificate Authorities' -> 'Certificate'.

 

5. Run some commands:

Stop-WFHost

Stop-SBFarm

Update-SBHost

Start-SBFarm

 

# Check SB status after start

Get-SBFarm

Get-SBFarmStatus

 

# Complete the WFM configuration to update nodes

Stop-WFHost

Update-WFHost -CertificateAutoGenerationKey $CertKey   

Start-WFHost

 

# Check WFM status after start

Get-WFFarm

Get-WFFarmStatus

 

6. Change the system time back to the current time. Restart the server.

Status:

Workflow manager certificate expired issue has been resolved. Run commands again.

 

# Check SB status

Get-sbfarm

Get-sbfarmstatus

# Check WFM status

Get-wffarm

Get-wffarmstatus

Passed WFMQuickTest.