Jan 182018
Please Share...

PowerShell provides multiple ways to access Excel. My favorite so far is via COMObject Excel.Application. This object provides direct access to Excel workbooks, worksheets. In this short article we’ll see how to accomplish this using PowerShell and Excel’s COMObject.

Instantiating Excel COM Object using New-Object PowerShell CmdLet

Using following line of code to instantiate Excel COM object.

$excelObj = New-Object -ComObject Excel.Application
Creating an Excel workbook
$excelWB = $excelObj.WorkBooks.Add()
Get reference to a worksheet
$excelWS = $excelWB.WorkSheets.Item(1)
Get reference to an excel worksheet cell

Change attributes of a Cell using following code…

$excelWS.Cells.Item(1, 1) = "Test"
$excelWS.Cells.Item(1, 1).Font.Bold = $true
Sample code to fill out an Excel worksheet

I’m here using Get-Process cmdlet to generate some data in tabular format. But basically, below script uses above logic to fill out the sheet…

$excelObj = New-Object -ComObject Excel.Application
$excelWB = $excelObj.workbooks.Add()
$excelWS = $excelWB.WorkSheets.Item(1)

#Get processes running on my machine
$procs = Get-Process

#Get all column names, process class' property name
$ColumnNames = $procs[0].GetType().GetMembers() | Where-Object{$_.MemberType -eq "Property" -and $_.MemberType -ne "Module"} | %{$_.Name}

#Make main excel window visible
$excelObj.Visible = $true

#Add excel column title
$cIndex = 0; #Column index
$ColumnNames | %{$cIndex++;$excelWS.Cells.Item(1, $cIndex).Font.Bold=$true;$excelWS.Cells.Item(1, $cIndex)=$_}

#Add process information into excel rows
$rIndex = 1;$procs | %{$cIndex = 0;$rIndex++;$proc=$_;$ColumnNames | %{$cIndex++;$excelWS.Cells.Item($rIndex, $cIndex) = "$($proc.$($_))"}}

Another option

PowerShell now does come with built in Excel support if you’re ok to install following module…

Install-Module ImportExcel

With this module in place you can run commands like…

Get-Process | Where Company | Export-Excel C:\Temp\ps.xlsx -Show -IncludePivotTable -PivotRows Company -PivotData @{Handles=”sum”} -IncludePivotChart -ChartType PieExploded3D

This will create an excel workbook with data in a pivot table. For this case we’ve set PivotData to sum of handles based on product company.



Please Share...
Jan 082018
Please Share...


While debugging a crash on a customer SharePoint farm, we had to do constant checks on all SharePoint servers for latest entries in the ‘System’ eventlog. So wrote this PowerShell one-liner, using Get-EventLog, to help him out…


Get-SPServer | Where-Object{$_.Role -ne "Invalid" } | %{ Write-Host "++++++++++++++++++ComputerName: $($_.Name.Split('.')[0])+++++++++++++++++++++++";
Get-EventLog -ComputerName $_.Name -LogName System -Newest 10} | ft -AutoSize

Command explained

  1. Get-SPServer: return all servers in the farm.
  2. Checking role of each server to make sure we’re accessing just the SharePoint servers.
  3. Drawing a header right before dumping out event log entries. If name is FQDN then we just take the first part, not necessary though.
  4. Call Get-EventLog by passing in computer name and log category as ‘System’, we’re retrieving newest 10 entries. Feel free to change this.

If you run into any issues or if you have any better way of doing this please let me know.

Sample output

PS C:\Windows\system32> Get-SPServer | Where-Object{$_.Role -ne "Invalid" } | %{ Write-Host "++++++++++++++++++ComputerName: $($_.Name.Split('.')[0])+++++++++++++++++++++++";
Get-EventLog -ComputerName $_.Name -LogName System -Newest 10} | ft -AutoSize
++++++++++++++++++ComputerName: SP+++++++++++++++++++++++

Index Time         EntryType   Source                  InstanceID Message                                                                 
----- ----         ---------   ------                  ---------- -------                                                                 
19665 Jan 08 12:00 Information Service Control Manager 1073748860 The Software Protection service entered the stopped state.              
19664 Jan 08 12:00 Information Service Control Manager 1073748860 The Software Protection service entered the running state.              
19663 Jan 08 12:00 Information EventLog                2147489661 The system uptime is 1026236 seconds.                                   
19662 Jan 08 10:21 Information Service Control Manager 1073748860 The Windows Modules Installer service entered the stopped state.        
19661 Jan 08 10:19 Information Service Control Manager 1073748860 The Windows Update service entered the stopped state.                   
19660 Jan 08 10:19 Information Service Control Manager 1073748860 The Windows Modules Installer service entered the running state.        
19659 Jan 08 10:14 Information Service Control Manager 1073748860 The Windows Store Service (WSService) service entered the stopped state.
19658 Jan 08 10:09 Information Service Control Manager 1073748860 The Windows Store Service (WSService) service entered the running state.
19657 Jan 08 10:07 Information Service Control Manager 1073748860 The Windows Update service entered the running state.                   
19656 Jan 08 07:18 Information Service Control Manager 1073748860 The Windows Modules Installer service entered the stopped state.
Please Share...
Dec 232017
Please Share...

Use following PowerShell commands to list all printers installed on any machine…

First command uses WMI (Windows Management Instrumentation)…

Get-WmiObject -Class "Win32_Printer" -ComputerName "localhost" | ft -Property Name, DriverName, SystemName, ShareName -AutoSize
Command Output
Name                           DriverName                                  SystemName        ShareName   
----                           ----------                                  ----------        ---------   
Send To OneNote 2016           Send to Microsoft OneNote 16 Driver         MYMACHINE                    
Microsoft XPS Document Writer  Microsoft XPS Document Writer v4            MYMACHINE                    
Microsoft Print to PDF         Microsoft Print To PDF                      MYMACHINE                    
Fax                            Microsoft Shared Fax Driver                 MYMACHINE                     

The next command uses built-in PowerShell command Get-Printer

Get-Printer -ComputerName mymachine
Command Output
Name                           ComputerName    Type         DriverName                PortName        Shared   Published  DeviceType     
----                           ------------    ----         ----------                --------        ------   ---------  ----------     
Send To OneNote 2016           mymachine       Local        Send to Microsoft OneN... xxx:            False    False      Print          
Microsoft XPS Document Writer  mymachine       Local        Microsoft XPS Document... xxxxxxxxxx:     False    False      Print          
Microsoft Print to PDF         mymachine       Local        Microsoft Print To PDF    xxxxxxxxxx:     False    False      Print          
Fax                            mymachine       Local        Microsoft Shared Fax D... xxxxxx:         False    False      Print
Please note that I’ve removed some printers from this list due to security issues.
Please Share...
Jun 202017
Please Share...

What’s a Heap?

Before understanding HeapCorruption here’s a quick review of the Heap. The HeapCreate function creates a private heap object from which the calling process can allocate memory blocks by using the HeapAlloc function. HeapCreate specifies both an initial size and a maximum size for the heap. The initial size determines the number of committed, read/write pages initially allocated for the heap. The maximum size determines the total number of reserved pages. These pages create a contiguous block in the virtual address space of a process into which the heap can grow. Additional pages are automatically committed from this reserved space if requests by HeapAlloc exceed the current size of committed pages, assuming that the physical storage for it is available. Once the pages are committed, they are not decommitted until the process is terminated or until the heap is destroyed by calling the HeapDestroy function.

We don’t directly interact with this function but internally the boiler plate application code does this, for e.g. CRT heap. When we use ‘new’, ‘malloc’, internally somewhere there’s a call happening to HeapAlloc. When we call ‘delete’ or ‘free’ internally somewhere there’s a call happening to HeapFree.

So what’s HeapCorruption?

Any inconsistency that’s caused to a process heap because of application code can be called a HeapCorruption. For e.g. Since most of the time the heap memory blocks are in the form of a linked list, linked to each other, so if we end up overwriting these links then we call that a heap corruption because the heap cannot be traversed anymore from one linked list node to the other.

We could see an application crashing with following error…

0:143> .lastevent
Last event: 616c.a550: Unknown exception – code c0000374 (first/second chance not available)
debugger time: Tue Jun 13 12:41:19.531 2017 (UTC – 5:00)

0:143> !error c0000374
Error code: (NTSTATUS) 0xc0000374 (3221226356) – A heap has been corrupted.

But in my case even though application crashed due to c0000374 and when I check the reason for HeapCorruption, I got the following…


So essentially the heap is not corrupted but we passed an invalid argument for heap free function hence this failure. So how do I verify that this is indeed the reason? So, we pick up the address that was passed on to HeapFree function and then analyze it using the !address command…

0:143> !address 0x0000003c`5cec97a8

Usage: Stack
Base Address: 0000003c`5ceae000
End Address: 0000003c`5ced0000
Region Size: 00000000`00022000 ( 136.000 kB)
State: 00001000 MEM_COMMIT
Protect: 00000004 PAGE_READWRITE
Type: 00020000 MEM_PRIVATE
Allocation Base: 0000003c`5ce50000
Allocation Protect: 00000004 PAGE_READWRITE
More info: ~143k
Content source: 1 (target), length: 6858

If we look at above output, usage shows as stack memory, which should never be freed via Heap functions but instead they’re automatically released at the end of usage scope. Lets now look at another address which is located on the Heap…

0:143> !address 0x0000003c`6acbfc00

Usage: Heap
Base Address: 0000003c`6a930000
End Address: 0000003c`6af17000
Region Size: 00000000`005e7000 ( 5.902 MB)
State: 00001000 MEM_COMMIT
Protect: 00000004 PAGE_READWRITE
Type: 00020000 MEM_PRIVATE
Allocation Base: 0000003c`6a220000
Allocation Protect: 00000004 PAGE_READWRITE
More info: heap owning the address: !heap 0x3c74e70000
More info: heap segment
More info: heap entry containing the address: !heap -x 0x3c6acbfc00

Content source: 1 (target), length: 257400

The above memory block is owned by a heap hence this can be freed via HeapFree Windows API and we should not get the HEAP_FAILURE_INVALID_ARGUMENT error. The error  ‘HEAP_FAILURE_INVALID_ARGUMENT’ can be further proved by following output from an internal extension command output…

*                                                            *
*                    HEAP ERROR DETECTED                     *
*                                                            *


Heap address: 0000003c74e70000
Error address: 0000003c5cec97a8

Details: The caller tried to a free a block at an invalid (unaligned) address.

Please Share...
Jun 132017
Please Share...

What’s a SearchCenter?

A Search Center site, or Search Center, provides an interface for users to submit search queries and view search results. A Search Center site is the top-level site of a site collection that a farm administrator creates by using the Enterprise Search Center template or Basic Search Center template. Sample search center screenshot…

Sample SearchCenter Shot

Sample SearchCenter Shot

How to Create a Search Center Site Collection… (from MSDN)

  • Verify that the user account that is performing this procedure is a member of the Farm Administrators group.
  • On the home page of the Central Administration website, in the Application Management section, click Create site collections.
  • On the Create Site Collection page, do the following:
    • In the Web Application section, select a web application to contain the new site collection. To use a web application other than the one that is displayed, click the web application that is displayed, and then click Change Web Application.
    • In the Title and Description section, in the Title box, type the name for the new Search Center site. Optionally, type a description in the Description box.
    • In the Web Site Address section, for the part of the URL immediately after the web application address, select /sites/, or select a managed path that was previously defined, and then type the final part of the URL.

      Note the address of the new Search Center for future reference.

    • In the Template Selection section, do the following:
      • In the Select the experience version drop-down list, select 2013 to create a Search Center site that provides the SharePoint Server 2013 user experience, or select 2010 to create a Search Center site that provides the SharePoint 2010 Products user experience.

        For more information, see Search user interface improvements in What’s new in search in SharePoint Server 2013.

      • In the Select a template subsection, click the Enterprise tab, and then do one of the following:
        • If you are using SharePoint Foundation 2013, select the Basic Search Center template.
        • Otherwise, if you are using SharePoint Server 2013, select the Enterprise Search Center template.
    • In the Primary Site Collection Administrator section, in the User name box, type the user name of the primary site collection administrator for this site collection in the form domain\user name.
    • (Optional) In the Secondary Site Collection Administrator section, type the user name of a secondary site collection administrator in the form domain\user name.
    • In the Quota Template section, select No Quota.

      A Search Center site is not intended to be a data repository. Therefore, you do not have to select a quota template.

    • Click OK.
  • On the Top-Level Site Successfully Created page, click the link to the Search Center site that you created.

How to configure the Global SharePoint SearchCenter URL?

Once this site is created we need to tell SharePoint SSA to use this site to submit search queries and view search results. Please note that even UPA uses this SearchCenter for user profile look up so it is important that we set this up the right way to be consumed by UPA as well. From what I know there are couple of ways to do this.

Via PowerShell

$ssa = Get-SPEnterpriseSearchServiceApplication
$ssa.SearchCenterUrl = “SearchCenterURL/pages”

Note that you might have to IISReset after this. If you’re doing this via PowerShell I’ve noticed that change will take effect faster than via UI.

Via UI

Open search service application (SSA). Click on value of “Global Search Center URL” to enter new URL….

You should see a popup similar to following dialog popup. Enter new search center URL here.

How to configure the SharePoint SearchCenter URL for a site/site collection?

Please note that above is for global search center URL but you can have site collection or site level search center URL which will override the global search center settings from what I know so far. For setting site collection level SearchCenter URL, access Site Settings->Site Collection Administration->Search Settings, you should see following popup…

For setting site level SearchCenter URL access Site Settings->Search->Search Settings, a dialog similar to the one above should pop up. Once you’ve filled out necessary values you’re good to go.

SearchCenter setting for UPA

So if SearchCenter is not setup, my site settings for your UPA service application will be empty, but if you set it via above powershell or via UI you should following empty field get populated, manually populating this field doesn’t stick.

Once the following PowerShell executes, we see following screenshot now…


This time the field is filled out, so when you’re viewing user profile and when clicking on user name and other things pertaining to a user you’ll land on the above SearchCenter page.

‘/Pages’ is important

Remember to append /Pages to the end of the SearchCenter URL. Reason being if you browse to the SearchCenter site and look at SiteContents. You’ll see a pages library which contains all the pages you’ll see as part of the SearchCenter. So if ‘/pages/’ is not appended to the end of the URL you’re bound to get a 404.

Ideally a SearchCenter URL should look as follows: http://sp:port/sites/searchcenter/pages



Please Share...
Jun 242016
Please Share...

What’s ServerRender?

ServerRender is a property of a webpart which disables client-side rendering for a webpart. You’ll lose some cool features of a webpart once you enable this feature for e.g.

  1. Inline editing of a list item, or in other words “Quick Edit”.
  2. Look and feel of the list changes to the traditional view, from a excel like view.

But this feature is quite handy when it comes to workaround some bugs related to client-side JavaScript.


How do we enable this feature via SharePoint UI?

  1. GoTo Site settings->Edit page
  2. Select list web part and then select WEB PART on the ribbon bar, then select the list web parts “Web Part Properties” on this ribbon.
  3. On the properties window that pops out on the right side, enable “Server Render”, at the bottom of this screenshot…


How do we enable this feature via PowerShell?

I had to dig around a bit to figure this out. Here’s how we do this via PowerShell…

#Web URL, change to your web's URL
$WebUrl = "http://sp/sites/TaskList"
#ListName, change to your list's name
$ListName = "MyTasks"
$web = Get-SPWeb $webUrl

#Get all web parts in this collection, please note this is for default view, if you've got other views please use URL for that view
$WebParts = $web.GetWebPartCollection($web.Lists[$ListName].DefaultViewUrl, [Microsoft.SharePoint.WebPartPages.Storage]::Shared)
#Dump names of all web parts on this page

#Assuming you just have one web part on this page.

Please Share...
Sep 232015
Please Share...

I’ve been working on a case where customer accidently deleted a bunch of timer jobs. He came to us with a question on how to restore the timer jobs back. We found a way to restore the timer jobs but just a few of them. So if the timer jobs are out of the box (OOB) jobs we can restore them in this manner. If there are other timer jobs you’ll have to just re-deploy the timer job solution again.

Farm Level Timer Jobs

From code I see that SPTimerService.EnsureDefaultJobs should restore the following farm level timer jobs…

Web Application Level Timer Jobs

There’s another one SPWebApplication.EnsureDefaultJobs which restores following web application level timer jobs.

Sample Script

So the question is how to execute these functions via PowerShell…

# Ensures the default farm level timer job definitions exist
Add-PSSnapin Microsoft.Sharepoint.Powershell
$farm = Get-SPFarm

#Ensures the default timer job definitions exist for the specified web application
$webapp = Get-SPWebApplication http://webapp/

Thanks to my colleague Stephen Woodard and Trevor Barkhouse for pointing me to this public function.


As always please make sure you test this out well before implementing this in production.

Please Share...
Sep 222015
Please Share...


Just be careful with the SharePoint 2013 August 2015 CU. One of the security fixes in this cumulative update package has a bug: https://support.microsoft.com/en-us/kb/3085501. Because this is a security fix most companies will push this fix to their servers.

The bug pops up when you try to attach a file to a list item or a calendar item. Here’s how the error message will look like…

Message from webpage
File names can’t contain the following characters: &”?<>#{}%~/\

Attaching a file to a list item is a feature that is rarely used so I guess customer’s affected will be just a minority. Microsoft is planning to fix this issue in October CU.


There are some workarounds listed in below blog link. Please make sure you back up the JavaScript files before modifying it.

Another workaround is to do the following…

  1. Create the list item without adding any attachments
  2. Go back to the list and select the list item you created in item 1.
  3. Ribbon->Items->Attach File and provide the attachment.

This will work for list items, but I’m not sure if this will work for calendar items.

Please Share...
Aug 142015
Please Share...

In this post we’ll be discussing on how to manipulate strings using PowerShell. PowerShell comes with the following string manipulation operators…

  • split – case sensitive split
  • isplit – case insensitive split
  • csplit – case sensitive split
  • join – joins an array into one string
  • replace – case sensitive split
  • ireplace – case insensitive split
  • creplace – case sensitive split
Split Demo Using Split Function Call
#store comma delimited wild animals list into a string
$wildanimals = "Tiger,Lion,Elephant,Bear,Fox,Jackal,Hyena"
#split the string using Split function based on ","
Split Demo Using -Split Operator
#split array using -split operator
$wildanimals -split ","
#split into three strings, this will leave the third string unsplit
$wildanimals -split ",", 3
#replace all "," with " "
$wildanimals = ($wildanimals -replace ",", " ")
#alternate syntax, with no split criteria the default is to use " " as split criteria
-split $wildanimals
#restore "," back instead of " "
$wildanimals = ($wildanimals -replace " ", ",")
Join Demo
#store into an array
$wildanimalsarray = $wildanimals -split ","
#join operator takes an array and makes it a string, each element separated by ","
$wildanimalsarray -join ","
Replace Demo
#replace all instances of Elephant with Zebra
$wildanimals -replace "Elephant","Zebra"

The case sensitive and insensitive operators are similar in usage except they are sensitive/insensitive to case. Hope this helps you.

Please Share...
Jul 232015
Please Share...
Very important:

Please make sure necessary backups are in place before you perform this operation. First test in your dev/test environment and only then use in your production environment.

Ideally a feature will be scoped to a Farm, WebApplication, Site, or Web, but orphaned features won’t be having a scope. The command to list all orphaned features in a SharePoint farm is as follows…

PowerShell command to list all orphaned features…
Get-SPFeature | ? { $_.Scope -eq $null }
Sample output…
PS C:\windows\system32> Get-SPFeature | ? { $_.Scope -eq $null }

DisplayName                    Id                                       CompatibilityLevel   Scope                         
-----------                    --                                       ------------------   -----                         
ReportServerCentralAdmin       5f2e3537-91b5-4341-86ff-90c6a2f99aae     14                                                 
ReportServerStapling           6bcbccc3-ff47-47d3-9468-572bf2ab9657     14                                                 
PowerView                      bf8b58f5-ebae-4a70-9848-622beaaf2043     14                                                 
ReportServerCentralAdmin       5f2e3537-91b5-4341-86ff-90c6a2f99aae     15                                                 
ReportServerStapling           6bcbccc3-ff47-47d3-9468-572bf2ab9657     15                                                 
PowerView                      bf8b58f5-ebae-4a70-9848-622beaaf2043     15
PowerShell command to delete an orphaned feature…
foreach($f in @(Get-SPFeature | ? { $_.DisplayName -eq "ReportServerCentralAdmin" }))

Why did we need a foreach? Because a feature could be listed twice with different “CompatibilityLevel” values. So in this case “ReportServerCentralAdmin” is listed twice with “CompatibilityLevel” set to 14 and 15. So we delete both via $f.Delete().

Please Share...