Massive 'if' statement script, is there a better way?

Edit: Huh… was doing some edits and my original post seems to have disappeared.

Here’s a suggestion for another way. I like using dictionary (or hashtable, depending on your point of view :slight_smile: lookups for this sort of thing instead of writing tons of if or case statements.

The first step I’d do is generate a dictionary of the departments with another dictionary as each key’s value. Something like this:

$depts = @{}

65..90 | %{$depts.Add("Dept$([char]$_)", @{})}

foreach($team in $depts.GetEnumerator()) { 
    65..90 | %{$team.Value.Add("Job$([char]$_)", @())}
}

That will produce a dictionary like this, with each value in the main dictionary being another dictionary with an array as its value:

Name                           Value
----                           -----
DeptR                          {JobC, JobB, JobW, JobX...}
DeptS                          {JobC, JobB, JobW, JobX...}
DeptP                          {JobC, JobB, JobW, JobX...}
DeptC                          {JobC, JobB, JobW, JobX...}
DeptB                          {JobB, JobW, JobD, JobQ...}
DeptE                          {JobC, JobB, JobW, JobX...}
DeptG                          {JobC, JobB, JobW, JobX...}
DeptQ                          {JobC, JobB, JobW, JobX...}
DeptX                          {JobC, JobB, JobW, JobX...}
DeptT                          {JobC, JobB, JobW, JobX...}
DeptF                          {JobC, JobB, JobW, JobX...}
DeptY                          {JobC, JobB, JobW, JobX...}
DeptU                          {JobC, JobB, JobW, JobX...}
DeptA                          {JobB, JobW, JobD, JobQ...}
DeptD                          {JobC, JobB, JobW, JobX...}
DeptI                          {JobC, JobB, JobW, JobX...}
DeptK                          {JobC, JobB, JobW, JobX...}
DeptJ                          {JobC, JobB, JobW, JobX...}
DeptM                          {JobC, JobB, JobW, JobX...}
DeptL                          {JobC, JobB, JobW, JobX...}
DeptZ                          {JobC, JobB, JobW, JobX...}
DeptO                          {JobC, JobB, JobW, JobX...}
DeptN                          {JobC, JobB, JobW, JobX...}
DeptH                          {JobC, JobB, JobW, JobX...}
DeptV                          {JobC, JobB, JobW, JobX...}
DeptW                          {JobC, JobB, JobW, JobX...}

Then, using Kyven’s CSV for some data, associate each user with a department and title:

$csv = import-csv test.csv

foreach ($user in $csv) {
    $props = @{"Department" = $user.Department; "Location" = $user.Location; "Username" = $user.Username}
    $depts[$user.Department][$user.Title] += New-Object -TypeName PSObject -Property $props
}

Then you can look up each user based on their department and title:

> $depts[$user.Department][$user.Title]

Username Department Location
-------- ---------- --------
user1    DeptA      Dallas

In this case I used an array to stash the user objects in, but you could just as easily build strings or something else as the value for the $user.Title key.

You might build pre-defined lists of departments and titles to build your dictionaries. You go even further and generate your keys and values based on the CSV attributes, then insert the users that way rather than generating the combinations.

Ideas to consider…

Kevyn,

Sorry in the delay getting back to you. I was a) getting my head around this and b) doing other things.

First, thank you very much for your time. Cannot be overstated the appreciation I have for this forum and in particular your efforts with this. I knew very well that there were better ways to achieve this mailer, and you hit it on the head. So, again, thank you.

After seeing that I needed another data set ($TeamsData, which I called $recipientList), it all became much more clear.

I still struggle with a couple of lines in our ForEach loop…

ForEach ($r in $recipientList) {

$results = $newUsers | Where-Object {($_.department -like "$($r.School)*")}

    if($results) {

        ForEach ($res in $results) {

            $bodyText += "`r`nUsername: $($res.samaccountname)`r`n`nTitle: $($res.title)`r`n`nBuilding: $($res.department)`n`n`n"

            }

            $sendTo = $r.Recipient -split ","

            Send-MailMessage @email -body $body.Replace("REPLACE",$bodyText) -To $sendTo

            }

        Remove-Variable results,bodyText,sendTo -ErrorAction SilentlyContinue

}

In line 3, I understand it to be placing department matches between $newUsers and $recipientList into $results, correct?

What I do not understand is why $results always appears to be empty.

The next thing I do not understand is the ‘grouping’ of each result in $bodyText. For instance, how is this loop deciding:

  • These 3 department/school matches go into $bodyText and are emailed to $r.recipient

  • These 2 department/school matches go into $bodyText and are emailed to $r.recipient

Etc. When I read this loop, it seems like either all $results would go into $bodyText and they would not be divided by department the way they are, or that Send-MailMessage would send an email for every match, instead of 1 email per matched group.

I really hope that makes sense. Any advice for helping me better understand that?

And thank you again!

Hey Creed. Not a problem. Glad I could help.

Some of your code, other than just variable names(You $recipientList instead of my $TeamsData), so let me try to explain my code better and hopefully that will answer your questions. Ok, here goes.

First you have this piece. I just took what you had. Just putting it in here for completeness.

$EmailArgs = @{
                SmtpServer = "myserver"
                From = "New_Staff@my.org"
                Subject = "New Staff Stuff"
              }

$MsgBody = @"
`r`n     
New Staff Accounts:

REPLACE

Do not reply to this email address.

Thank you!
    
"@

Then there is the following two pieces of data. In the posting of my code, on 9/14/17, I mention specific examples of what is in these two files. I go into more detail below.

#First is the "user data" file.  In your initial post, you mentioned 4 "teams", as examples, that had 4 unique combinations of Department & Title that determined which "team" a user fell into.  When I wrote this, I assumed that you had Department & Title as two of the columns/headers in the file containing the user information (Originally you imported it into $users).  In your above reply, you look to be using $newUsers as the variable you imported the data into.  I have it as $UserData (Again, see the original posting of my code for what I used in my example file.  It has the columns of UserName, Department, Title, & Location).  It sounds like you have more.

#Secondly, is the "teams data" file.  In my original code posting, I show what my TeamsData.txt file looked like.  It is meant to contain the users that should be e-mailed for each "team" as well as the filtering criteria to be used to determine who is on each "team".  Per your original example code, I used Department & Title.  In your latest code, I see you're using a property/file column called School to filter by.  If this is the property you plan to filter by, instead of Department & Title, then make sure the column is listed in both files.

$UserData = Import-Csv -Path C:\Data\UserData.txt
$TeamsData = Import-Csv -Path C:\Data\TeamsData.txt

Ok. Now for the rest.

#This outer ForEach is going through the list of teams you have defined in the "teams data" file, whatever you want to call that file and whatever variable name you want to import it into.  We're using it to see who, if anybody, fits into each "team".  There may not be anybody who does for a particular team.

ForEach($Team in $TeamsData)
{
  #The $TeamResults variable can be empty because not every team you have defined in the "teams data" file, which should be all of your possible teams, will have a user, from the "user data" file, that fits into it.  For example, your "user data" file may not contain a user who fits into the "TeamA" example of your original post (i.e. Department = DeptA* & Title = JobA).

  $TeamResults = $UserData | Where{($_.Department -like "$($Team.Department)*") -and ($_.Title -match "$($Team.Title)")}

  #Check to see if there was at least one user who fits the criteria of the particular "team" we're looking at in this instance of running the "$TeamsData" ForEach loop (Ex: TeamA).

  If($TeamResults)
  {
    #If there was at least one user found (i.e. they fit the criteria of the team, such as the combination of Department = DeptA & Title = JobA), then do the following for each user who fit the criteria.  If you're not getting anyone matching any "team" criteria (You mentioned that you're getting blank results for this), then most likely you don't have the same columns/headers in the "user data" & "teams data" files for what you're filtering on (Ex: School).

    ForEach($Result in $TeamResults)
    {
      #Add the UserName, Title, & Location, or whatever else you might want, of each user to the $BodyReplacementTxt variable.  In my original code posting, I showed how this looked for me in an actual message that got sent, along with all the other body text around it.

      $BodyReplacementTxt += "Username: $($Result.UserName)`r`nTitle: $($Result.Title)`r`nLocation: $($Result.Location)`r`n`n"
    }

    #In the "teams data" file, I specified a comma-separated list of recipients, in a set of double-quotes (""), that should receive the e-mails for each "team".  Again, I used your earlier example when you listed the 4 lines of who should receive the e-mails for TeamA, TeamB, TeamC, & TeamD.  Since I'm using a comma-separated list, I'm splitting the list using the comma.  I end up with an array of the users' e-mail addresses that the Send-MailMessage cmdlet can use as the recipients of the e-mail message for the particular team.

    $Recipient = $Team.Recipient -split ","

    #I send the message with the pre-defined e-mail arguments you specified, the modified body text (i.e. what ends up in the $BodyReplacementTxt variable as mentioned above), and the array of recipients (could be one, or more, recipients).

    Send-MailMessage @EmailArgs -Body $MsgBody.Replace("REPLACE",$BodyReplacementTxt) -To $Recipient
  }

  #I remove all of the key variables so that it's clean for the next round of the $TeamsData ForEach loop.
  Remove-Variable TeamResults,BodyReplacementTxt,Recipient -ErrorAction SilentlyContinue
}

Hopefully I haven’t confused you too much. Let me know if you have any further questions.

Hi Kevyn,

Nope, no more confused than before! Haha. Actually, I do understand how the script works, but perhaps not fully the behavior of the nested ForEach loops.

The part I am curious about (though also very pleased that it works) is how each ‘set’ of user results is sent in 1 email.

Hypothetically, $TeamResults has several user ‘fits’… 3 x users for TeamA, 2 x users for TeamB, 5 x users for TeamC.

When the script runs, the details for all 3 users for TeamA are sent to recipientA in 1 email. That is awesome. That is what I want, but I also want to understand exactly how that is happening. Looking at it, I would think an email would be sent for each user. Thankfully not, but what am I missing?

Thank you again!

Gotcha. Well, first of all it’s important to understand that everything in Windows PowerShell is object based, even an array or a string (My apologies if I’m already stating stuff you know.). Think of a person. A person (i.e. the object) has attributes (eye color, number of hands, number fingers on each hand, etc…). When you add other people you get an object (i.e. a group of people) that contains objects (i.e. individual people).

Ok, so now for a specific example of why this is working the way it does. Let’s start off with the example files I mentioned. I’ll be using the code, including variable names, as I wrote them.

--UserData.txt (Imported into $UserData):

When this file is imported.  Each line is an object.  The variable $UserData contains the group of objects.

UserName,Department,Title,Location
user1,DeptA,JobA,Dallas
user2,DeptA,JobB,Houston
user3,DeptB,JobA,Garland
user4,DeptB,JobB,Richardson
user5,DeptA,JobA,Frisco

Importing data from UserData.txt and dumping out to the screen.

PS C:\> $UserData = Import-Csv -Path C:\Data\UserData.txt
PS C:\> $UserData

UserName Department Title Location  
-------- ---------- ----- --------  
user1    DeptA      JobA  Dallas    
user2    DeptA      JobB  Houston   
user3    DeptB      JobA  Garland   
user4    DeptB      JobB  Richardson
user5    DeptA      JobA  Frisco    
user6    DeptA      JobA  Dallas    
user7    DeptB      JobB  Houston   
user8    DeptB      JobA  Garland 

--TeamsData.txt (Imported into $TeamsData):

When this file is imported.  Each line is an object, as with the previous set of data.  The variable $TeamsData contains the group of objects.

Team,Recipient,Department,Title
TeamA,"tuser1@domain.com",DeptA,JobA
TeamB,"tuser1@domain.com,tuser2@domain.com",DeptA,JobB
TeamC,"tuser3@domain.com",DeptB,JobA
TeamD,"tuser3@domain.com,tuser4@domain.com",DeptB,JobB
user6,DeptA,JobA,Dallas
user7,DeptB,JobB,Houston
user8,DeptB,JobA,Garland

Importing information into $TeamsData & dumping it out on the screen.

PS C:\> $TeamsData = Import-Csv -Path C:\Data\TeamsData.txt
PS C:\> $TeamsData

Team  Recipient                                           Department Title
----  ---------                                           ---------- -----
TeamA tuser1@domain.com                           DeptA      JobA 
TeamB tuser1@domain.com,tuser2@domain.com         DeptA      JobB 
TeamC tuser3@domain.com                           DeptB      JobA 
TeamD tuser3@domain.com,tuser4@domain.com         DeptB      JobB 

I’ll use TeamA to detail how this works. We’re doing exactly the same thing for all of the other teams as well.

The data for each file has been imported and now we’re starting the ForEach look for the $TeamsData objects. ForEach is a looping construct that basically says "For each object in this group of objects (can be only one object in the group…or none) do ". So, for one object at a time in the group of objects we do whatever is inside the brackets({}) of the ForEach block. In this case, the first object in the $TeamsData object we process is the one for TeamA, which gets stored in the $Team variable (Each object through the loop gets stored in the $Team variable, one at a time.). I know this because I can index into the group of objects as below, starting with the index of 0 (i.e. the first object).

PS C:\Users\miracles1315\OneDrive\Powershell\Scripts> $TeamsData[0]

Team  Recipient                 Department Title
----  ---------                 ---------- -----
TeamA tuser1domain.com          DeptA      JobA

So, in the first iteration of the TeamsData ForEach loop we have the above information in the $Team variable. Now, the first thing we do in the TeamsData ForEach loop is the below, which is where we do our filtering. $UserData is all of the data I showed earlier. In this part of the code, we’re taking the entire contents of the $UserData variable and saying that we only want a subset of the data. In the case of TeamA, we’re saying that from $UserData only return the user objects where the Department = DeptA* (w/ * being a wildcard) & where Title = JobA.

$TeamResults = $UserData | Where{($_.Department -like "$($Team.Department)*") -and ($_.Title -match "$($Team.Title)")}

So, let’s see what that looks like. Remember that right now $Team = $TeamsData[0] = object for TeamA. So, When I manually pull this data out, rather than doing the ForEach loop by running the full code, I’m replacing $Team with $TeamsData[0] to see what I’d get in this iteration of the loop.

PS C:\> $TeamResults = $UserData | Where{($_.Department -like "$($TeamsData[0].Department)*") -and ($_.Title -match "$($TeamsData[0].Title)")}
PS C:\> $TeamResults

UserName Department Title Location
-------- ---------- ----- --------
user1    DeptA      JobA  Dallas  
user5    DeptA      JobA  Frisco  
user6    DeptA      JobA  Dallas  

If I look back at the contents of the $UserData.txt, these are the 3 users with the criteria for TeamA. So far, so gook. Ok, time to move on.

Next, we run into the "If($TeamResults) block. If there were no users who happened to fit the criteria for TeamA, then this would evaluate to $False and the block of code in it would not execute. However, in this case, we have 3 users who fit the criteria for the team (TeamA, in this case). So, the code will execute.

The next thing we do, since $TeamResults has 3 objects in it, is the TeamResults ForEach loop. As with TeamsData ForEach loop, we take one object at a time and do whatever is inside the brackets({}). So, let’s take this loop one object at a time. Similar to me substituting $TeamsData[0] for $Team, I’ll be substituting $TeamResults[0] for $Result, then $TeamResults[1] for $Result, and finally $TeamResults[2] for $Result as the object for each of my 3 users is processed. In the TeamResults ForEach loop, I’m doing just one thing, assigning adding something to $BodyReplacementtxt. The plus-equals (+=) means that I’m taking what’s where and I’m adding to it. I’m not overriding what’s there already. In this case, I’m adding string objects to it that contain the information specified by the code and the current object I’m using.

Intially, $BodyReplacementTxt is blank.

PS C:\> $BodyReplacementTxt

PS C:\>

First, $Result = $TeamResults[0] (i.e. the first object in the $TeamResults object).

PS C:\> $TeamResults[0]

UserName Department Title Location
-------- ---------- ----- --------
user1    DeptA      JobA  Dallas

Now, I’m taking pieces from the above data (i.e. UserName, Department, & Location), for user1, and adding it to $BodyReplacementTxt.

PS C:\Users\miracles1315\OneDrive\Powershell\Scripts> $BodyReplacementTxt += "Username: $($TeamResults[0].UserName)`r`nTitle: $($TeamResults[0].Title)`r`nLocation: $($TeamResults[0].Location)`r`n`n"
PS C:\Users\miracles1315\OneDrive\Powershell\Scripts> $BodyReplacementTxt
Username: user1
Title: JobA
Location: Dallas

Now, I do that for the next object that gets assigned to $Result (i.e. $TeamResults[1]), which is a string that has the Username, Title, and Location of user5.

PS C:\Users\miracles1315\OneDrive\Powershell\Scripts> $BodyReplacementTxt += "Username: $($TeamResults[1].UserName)`r`nTitle: $($TeamResults[1].Title)`r`nLocation: $($TeamResults[1].Location)`r`n`n"
PS C:\Users\miracles1315\OneDrive\Powershell\Scripts> $BodyReplacementTxt
Username: user1
Title: JobA
Location: Dallas

Username: user5
Title: JobA
Location: Frisco

Finally, the last object that gets assigned to $Team (i.e. $TeamResults[2]), which is a string that has the Username, Title, and Location of user6 (i.e. the last of the 3 objects in $TeamResults).

PS C:\Users\miracles1315\OneDrive\Powershell\Scripts> $BodyReplacementTxt += "Username: $($TeamResults[2].UserName)`r`nTitle: $($TeamResults[2].Title)`r`nLocation: $($TeamResults[2].Location)`r`n`n"

PS C:\Users\miracles1315\OneDrive\Powershell\Scripts> $BodyReplacementTxt
Username: user1
Title: JobA
Location: Dallas

Username: user5
Title: JobA
Location: Frisco

Username: user6
Title: JobA
Location: Dallas

The next thing that happens, after the TeamResults ForEach loop, is that I determine what recipients (one, or more) should be stored in the $Recipient variable. In the case of TeamA, there is only one recipient listed. Again, $Team = $TeamsData[0] because we’re still in the first loop of the TeamsData ForEach block.

PS C:\> $TeamsData[0].Recipient
tuser1@domain.com
PS C:\> $Recipient = $TeamsData[0].Recipient -split ","
PS C:\> $Recipient
tuser1@domain.com

If there had been more than one recipient, as in the example of TeamB, it would look like this.

PS C:\> $TeamsData[1].Recipient
tuser1@domain.com,tuser2@domain.com
PS C:\> $Recipient = $TeamsData[1].Recipient -split ","
PS C:\> $Recipient
tuser1@domain.com
tuser2@domain.com

Next, I run the Send-MailMessage cmdlet, where the arguments I put in the $EmailArgs variable are used, what’s in the $BodyReplacmentTxt variable is put in place of the word “REPLACE” in the $MsgBody string object, and the list of recpients in $Recipient is assigned to the To parameter.

Finally, I remove the $TeamResults, $BodyReplacementTxt, & $Recipient variables. In case they don’t have values in them, I set the -ErrorAction parameter to SilentlyContinue.

The above is repeated for the other teams, one at a time.

Hopefully that wasn’t too much of an explanation and it made sense.

My apologies, again, if I’m assuming too much, but it seems like you could use some resources to help you better understand powershell, including objects. Below are some resources that I highly recommend. The books are co-authored by Don Jones and Jeffrey Hicks who happen to run this site. If you buy the physical books (pbooks as they call them), you also get the digital copies free in several forms (pdf, kindle, etc…).
Beginning Book/Courses:
-Learn Windows PowerShell In A Month Of Lunches (Chapter 9 is especially awesome at explaining the pipeline.)
–Getting Started with Microsoft PowerShell: https://mva.microsoft.com/en-US/training-courses/getting-started-with-microsoft-powershell-8276?l=vOd1PSWy_9204984382 (A Series of videos with Jeffrey Snover (helped create/design Windows Powershell) & Jason Helmick; Watching this in concert with reading the Learn Windows PowerShell In A Month Of Lunches book really helped the info sink in.)
–Getting Started with PowerShell Desired State Configuration (DSC): https://mva.microsoft.com/en-US/training-courses/getting-started-with-powershell-desired-state-configuration-dsc-8672?l=ZwHuclG1_2504984382 (Not yet started this.)
Advanced Book/Courses:
-Learn PowerShell Toolmaking In A Month Of Lunches (Reading through this now.)
–Advanced Tools & Scripting with PowerShell 3.0 Jump Start: https://mva.microsoft.com/en-US/training-courses/advanced-tools-scripting-with-powershell-30-jump-start-8277?l=WOWaGUWy_8604984382 (A Series of videos with Jeffrey Snover (helped create/design Windows Powershell) & Jason Helmick); Watching this in concert with reading the Learn PowerShell Toolmaking In A Month Of Lunches book has really helped the info sink in.)
–Advanced PowerShell Desired State Configuration (DSC) and Custom Resources : https://mva.microsoft.com/en-US/training-courses/advanced-powershell-desired-state-configuration-dsc-and-custom-resources-8702?l=3DnsS2H1_1504984382 (Not yet started this.)

If my above explanation doesn’t put you to sleep, I don’t know what will…lol

Hey Kevyn,

Thank you for all of the details! I understood all parts of your explanation, and I think one part may have answered my question.

When we create $TeamResults here…

PS C:\> $TeamResults = $UserData | Where{($_.Department -like "$($TeamsData[0].Department)*") -and ($_.Title -match "$($TeamsData[0].Title)")}
PS C:\> $TeamResults

UserName Department Title Location
-------- ---------- ----- --------
user1    DeptA      JobA  Dallas  
user5    DeptA      JobA  Frisco  
user6    DeptA      JobA  Dallas

…index [0] holds all user matches for each team. This was the surprise to me. I thought index [0] would be

user1    DeptA      JobA  Dallas

Since [0] is a group of objects, it makes sense that when $TeamResults is processed in the next loop, these users would be ‘grouped’ in $BodyReplacementText.

I hope that sounds like I am on the right track. Thank you VERY much for your efforts here. I think the ‘kicker’ moment for my original question was when we used the additional csv. That helped me see that is what I needed, and the rest made much more sense from there.

Let me know if that sounds right.

Thanks again!!

$TeamResults is the grouping of the objects that meet our criteria for each team. There may be none that match for a particular team. In the case of our example for TeamA, 3 users match the criteria. For TeamA, $TeamResults is the grouping of the 3 “user” objects as I showed. $TeamResults[0] is just the first user’s (i.e. user1) object. When you use the index on a grouping of objects, you’re just saying you want that specific object. It’s just like people grabbing a number when they get in a line for a service. They’re all part of $Customers, and you may be customer #10 in line (i.e. $Customers[9]…because the index always starts at 0). That make sense?

Yep, that does make sense. I will often check an index to make sure of what properties are exposed for an object, or to check the value of a property on a particular object, etc.

I have just been making this harder to understand than need be. The nested ForEach slightly through me, but when I look at it…

The objects in my list are processed one at a time through both loops, completing and sending the email for each object before starting to process the next. So, all of each matching ‘set’ are processed and emailed together before the next row even iterates for a match.

Thanks for all of your input and patience. Sure beats a massive list of ‘if’ statements. Thanks!

Not a problem. :slight_smile: The TeamsData ForEach loop/block is to see who, if any, of the users in your “user data” file match each “team” filtering criteria. The TeamResults ForEach loop/block is to process the list of users, for a particular team, to create the $BodyReplacementTxt data for the e-mail that is sent. It’s as simple as that. :slight_smile: