PowerShell script to set registry values based on group membership - inefficient, needs reworking

I inherited this PowerShell logon script from a colleague.  It changes the default save locations of Office apps to specific UNC paths (team sites in Sharepoint) based on AD group membership.  Every AD group related to a SharePoint team is listed in the script.  When launched, the script goes through every AD group, checking one-by-one whether the user is a member of that AD group, and then sets the default save locations of Word, Excel and PowerPoint (registry value) based on the UNC paths listed in the script corresponding to the AD groups.

If the script doesn't find a match at all, it does nothing.
If it finds a match on one of the groups, it sets the registry values accordingly.
If it finds a match on more than one group, then it sets the default save location to K:\My Documents

This was OK when there was a handful of these groups, but now there are over two hundred, and as you can imagine, this script now takes about a minute to complete.

All the groups begin with "EDRMS - " so I was thinking it would be better the other way around, i.e. the script goes through a list of all the AD groups an user is a member of, finds the ones that begin with "EDRMS - ", then checks some sort of lookup table with groups names and UNC paths, then sets the registry values accordingly.  Would anyone be able to change the original script (attached) to do this (or, another method if you think it will result in quicker processing)?  I'm afraid that my PowerShell knowledge isn't up to this job.

What is the best way to store the table of group names and UNC paths?  Would CSV file be a quick and efficient way?  I was thinking an external file of some kind would be good in terms of manageability, because I could then delegate the responsibility of adding a new entries to our 1st line team, rather than needing to have scripting knowledge to do it.

Many thanks.
meirionwylltSenior Desktop EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

No more requirement for the PowerShell AD cmdlets to be installed on the machine where it's executed.
You'll need a separate configuration file in the same folder as the script, with the same name as the script, but the extension ".csv".
"Group", "Path"
"<default>", "K:\My Documents"
"EDRMS - Tim Rheoli Gwybodaeth (rg)", "\\teams@SSL\safle\rg\"
"EDRMS - Tim Datblygur Sefydliad (datsef)", "\\teams@SSL\safle\datsef"

Open in new window

$GroupConfigFile = $MyInvocation.MyCommand.Path -replace '\.ps1', '.csv'
## Create a hash table from the configuration with the group names as keys and the save paths as values
$SavePathMap = @{}
Import-Csv -Path $GroupConfigFile | ForEach-Object {$SavePathMap[$_.Group] = $_.Path}

New-PSDrive -Name 'HKCR' -PSProvider Registry -Root 'HKEY_CLASSES_ROOT' -ErrorAction SilentlyContinue | Out-Null
$OfficeVersion = (Get-ItemProperty 'HKCR:\Word.Application\CurVer' -ErrorAction SilentlyContinue).'(default)'.Split('.')[-1]
If (-not $OfficeVersion) {
	Write-Host "Found no installation of Office, leaving!"
$OfficeVersion = $OfficeVersion + '.0'
Write-Host "Found an installation of Office $($OfficeVersion)"

## Get the user's (expanded) AD group membership:
$MemberOf = & whoami.exe /groups /fo csv | Select-Object -Skip 1 | ConvertFrom-Csv -Header Group, Type, Sid, Attributes | Where-Object {$_.Group -like "${ENV:UserDomain}\*"} | ForEach-Object {$_.Group.Split('\')[1]}
$MatchedGroups = @($SavePathMap.Keys | Where-Object {$MemberOf -contains $_})
If (-not $MatchedGroups) {
	Write-Host "${ENV:UserName} is not a member of a relevant group, leaving!"
Write-Host "${ENV:UserName} is member of $($MatchedGroups.Count) relevant group(s):`r`n$($MatchedGroups -join "`r`n")"

If ($MatchedGroups.Count -eq 1) {
	$OfficeSavePath = $SavePathMap[$MatchedGroups[0]]
} Else {
	$OfficeSavePath = $SavePathMap['<default>']
Write-Host "Office save path: $($OfficeSavePath)"

$OfficeBaseKey = "HKCU:\Software\Microsoft\Office\$($OfficeVersion)"
$OfficeRegistry = @"
	"Application",	"Path",												"Name"
	"Excel",		"$($OfficeBaseKey)\Excel\Options",					"defaultpath"
	"PowerPoint",	"$($OfficeBaseKey)\PowerPoint\RecentFolderList",	"default"
	"Word",			"$($OfficeBaseKey)\Word\Options",					"DOC-PATH"
"@ | ConvertFrom-Csv

ForEach ($Row In $OfficeRegistry) {
	Write-Host "Configuring $($Row.Application)"
	If (-not (Test-Path $Row.Path)) {
		Write-Host "    Creating new registry path '$($Row.Path)'."
		New-Item -Path $Row.Path -Force | Out-Null
	Write-Host "    Setting save path '$($Row.Name)' in '$($Row.Path)'."
	New-ItemProperty -Path $Row.Path -Name $Row.Name -Value $OfficeSavePath -PropertyType ExpandString -Force | Out-Null

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
meirionwylltSenior Desktop EngineerAuthor Commented:
Fantastic, thanks for writing that - some nifty things going on in there.

So, I created a CSV with the same name as the ps1.  I then tested the script by adding my user into the first group on the list and then running the script in PowerShell ISE.  Please see the attachment for what I got back.  I'm not sure if the red text is in itself a cause for concern, but it should have picked up the fact that I've put myself into that group.

I'm guessing I've probably done something wrong with the CSV.  Please see the other screenshot to see how my CSV looks.  I wasn't sure how to import your CSV code into a file so I created it manually.  Is there something I need to do to the CSV to help the script to read it, like with the delimiter or something?  Or is the problem somewhere else?

I'm running this on Windows 10 if that helps.

I'm away now until Monday but will pick this back up then.

Many thanks for your work on this!
The format of your csv file is incorrect. Open it in Notepad (or any other text-based Editor, like Notepad++: https://notepad-plus-plus.org/).
Make sure the format looks like code snippet above.
It must have a header line with two columns "Group" and "Path", and the delimiter must be a comma ",". If you want to use another delimiter, add the -Delimiter "<Delimiter>" argument to Import-Csv in line 4.
For example ";":
Import-Csv -Path $GroupConfigFile -Delimiter ";" | ForEach-Object {$SavePathMap[$_.Group] = $_.Path}

Open in new window

The script can't pick up the group membership, because it'll only show you the matches against the csv, and since the csv is "empty", there's no match.

And note that a change in group membership requires the user to log off and back on. The script evaluates the user's security token, and that will only be set during logon.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

meirionwylltSenior Desktop EngineerAuthor Commented:
Thanks oBdA.

Can I use a tab delimiter?  If I want to pass the updating of this CSV file to 1st line then I want the format of the file to be as simple as possible to reduce human error.  I've seen that  -Delimiter "`t"  will apparently let me do this.  Does this mean that I can use a spreadsheet that looks like the screenshot I sent?  I'm still getting the same errors.  I've tried it with, and without, quotes around the text in all cells.

As for the quotes, is there a way that the script can place the quotes there for me when processing, rather than these being put in the CSV?

Many thanks.
Again, just in case: csv is a text format, so use a text editor (Notepad++ recommended, see above) to edit/create it, not Excel.
You can use basically any delimiter you want, provided you add it to the Import-Csv as well.
As far as PowerShell is concerned, the quotes around the cells are only mandatory if a cell contains the delimiter or a newline.
Since the data you want to import doesn't contain either of those, you can just drop them.
Group	Path
<default>	K:\My Documents
EDRMS - Tim Rheoli Gwybodaeth (rg)	\\teams@SSL\safle\rg\
EDRMS - Tim Datblygur Sefydliad (datsef)	\\teams@SSL\safle\datsef

Open in new window

To test whether your csv is valid, you can just use the Import-Csv directly, instead of running the script:
Import-Csv -Path C:\Temp\Whatever.csv -Delimiter "`t"

Open in new window

Output should be
Group                                                                           Path
-----                                                                           ----
<default>                                                                       K:\My Documents
EDRMS - Tim Rheoli Gwybodaeth (rg)                                              \\teams@SSL\safle\rg\
EDRMS - Tim Datblygur Sefydliad (datsef)                                        \\teams@SSL\safle\datsef

Open in new window

meirionwylltSenior Desktop EngineerAuthor Commented:
Excellent, I tested the import-csv line on its own as you suggested, and noticed that PowerShell picked up the delimiter as a semi-colon - even for CSV files generated from within Excel.  So I added the -Delimiter ";" to the script and then it worked fine, and the group membership was picked up and the correct registry entries set.

Many thanks for your help.
meirionwylltSenior Desktop EngineerAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.