Efficient Lookups in PowerShell - Nero Blanco IT Migration Specialists

Efficient Lookups in PowerShell - Nero Blanco IT Migration Specialists">

Twan van Beers
21 November, 2019

When writing scripts in PowerShell we often need to do a lookup in another dataset. Of course one way is to use an external tool like Excel with VLOOKUP or SQL Server with its joins and produce a data set that can be used in PowerShell without any lookups, but sometimes this just isn’t practical.

I often have a dataset in csv format, and in this example assume I have a two column csv; GroupSMTPAddress and MemberSMTPAddress.

After importing the dataset into a variable

$GroupMembers = Import-CSV .\groupmembers.csv

Then I can do things like

$Member = $GroupsMembers | ? { $_.MemberSMTPAddress -eq 'me@here.com' }

All well and good and it returns me an array of matched rows. The problem arises when you have tens of thousands of rows in this csv or large numbers of columns, since reading through that even in memory isn’t that fast. (Especially if you need to do that lookup thousands of times)

So a better way…

$GroupMembers = Import-CSV .\groupmembers.csv | group -AsHashTable -Property MemberSMTPAddress

So what this is doing is creating a hash table with the property as the key and the row as a hashtable.

Now when you want all the groups that me@here.com is a member of you can simply do

$GroupMembers.'me@here.com'

and you get the same array as before, but in a faction of the time!!! The me@here.com could be in a variable too

$MemberEmail = 'me@here.com'

$GroupMembers.$MemberEmail

And there you have it. Efficient lookups in PowerShell!

Twan van Beers

Twan is a senior consultant with over 30 years of experience. He has a wide range of skills including Messaging, Active Directory, SQL, Networking and Firewalls. Twan loves to write scripts and get deep and dirty into debugging code, in order to understand and resolve the most complex of problems.