Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.
Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.

April 2015

DataTable in PowerShell for crazy fast filters

Recently I was working with a 60,000 row CSV and applying several filters to find matching rows.   This took between 1.0-1.8 seconds per row.   While OK in small scale (few hundred) the script run duration grew to several hours.    There had to be a better way.

Enter [System.Data.DataTable]

Native PowerShell collections are simple to create and easy to work with.   However, in large scale I find DataTable to perform better.   It requires more up front coding to get ready (columns, schema, populate rows) but then runs crazy fast.   Sample code below.







# Load CSV
$coll = Import-CSV "people.csv"
# Create table
$dtPeople = New-Object System.Data.DataTable("people")
$cols = @("userlogin","username","managerlogin","managername","department")
# Schema (columns)
foreach ($col in $cols) {
	$dtPeople.Columns.Add($col) | Out-Null
# Values (rows)
foreach ($c in $coll) {
	$row = $dtPeople.NewRow()
	foreach ($col in $cols) {
		$row[$col] = $c.$col
	$dtPeople.Rows.Add($row) | Out-Null
# Do we have this user?
$findUser	= "TEST1"
# DataView rapid filter
$dvPeople	= New-Object System.Data.DataView($dtPeople)
$dvPeople.RowFilter = "UserLogin = '$findUser'"
# Result
if ($dvPeople.Count -gt 0) {
	Write-Host "Found" -ForegroundColor Yellow
	$dvPeople | ft
} else {
	Write-Host "Not Found!" -ForegroundColor Red


TEST1,Test Guy1,mgr1,Manager Guy1,IT
TEST2,Test Guy2,mgr1,Manager Guy1,IT

Query remote User Profile with PowerShell and REST API

The below code leverage SharePoint 2013 REST /_api/ to get User Profile detail remotely.   WebClient issues the HTTP GET and saves XML response.   Parsing XML gives full profile detail which can be exported or saved with other functions.   Hope this helps! 



NOTE – Remember to replace “sharepoint2013” with target farm DNS and “domain\” with Active Directory domain.


function getRemoteUserProfile ($user) {
	# Config
	$url = "http://sharepoint2013/_api/SP.UserProfiles.PeopleManager/GetPropertiesFor(accountName=@v)?@v='domain\$user'"
	$file = "c:\temp\response.html"
	# WebClient
	$wc = New-Object System.Net.WebClient
	$wc.UseDefaultCredentials = $true
	$wc.Proxy.Credentials = $wc.Credentials
	$wc.DownloadFile($url, $file)
	# Parse
	$x = Get-Content $file
	$upp = $
	$costcenter = ($upp.element |? {$_.Key -eq "costcenter"}).Value
	$division = ($upp.element |? {$_.Key -eq "division"}).Value
	$department = ($upp.element |? {$_.Key -eq "department"}).Value
	# Display
	$obj = New-Object –Type PSObject –Prop @{'User'=$user;'costcenter'=$costcenter;'division'=$division;'department'=$department}
	return $obj
# Main
getRemoteUserProfile "sptest1"
getRemoteUserProfile "sptest2"
getRemoteUserProfile "sptest3"


While troubleshooting an error with Excel Web Access I found recycle wasn’t sufficient and IISRESET was needed.   However, this raised questions about daily scheduled IIS recycle and if that would be enough to give a stable IIS footprint each business day.   I decided to err on the side of caution and schedule a full IISRESET along with code to ensure all sites and pools started up again OK.   PowerShell below.  


Also, I would recommend installing SPBestWarmUp (  by running “spbestwarmup.ps1 –install”    This creates a Scheduled Task repeating every 15 minutes to simulate user traffic and keep those IIS pools responsive for end users.   No more wait on first visit.   It even warms up Central Admin to help out SharePoint admins.  





# Reset IIS and verify 100% started.  Twice to be sure  =)
Import-Module WebAdministration -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
function IISGo {
	Get-ChildItem IIS:\AppPools |% {Start-WebAppPool $_.Name}
	Get-WebSite | Start-WebSite
Sleep 5
Sleep 5

Test if GAC assembly exists with PowerShell

Recently I was troubleshooting a SharePoint health analyzer message about missing GAC DLL assembly.   Some servers in the farm had the DLL while others did not.  The below PowerShell one-liner helped me run a test on each server to load the GAC DLL and verify if it was installed.   Hope this helps others too!  




[reflection.assembly]::Load("System.Windows.Forms, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089")



© Copyright 2016
@ SPJeff

Return to Top ▲Return to Top ▲