Creating an array using Select-Object with the ‘Switch’ option

A sample scenario:
We start off with an array of users that we have collected from Active Directory. We need to correlate the data with another array that contains additional information about the users.
The first array we will call $users – and we get this by running

$users = get-aduser –prop name, mail, Givenname, Surname, employeeid -filter *
name	mail	givenname	surname	employeeid
proberts	[email protected]	paul	roberts	12345
jdavis	[email protected]	john	davis	12347
dsutton	[email protected]	david	sutton	12352
ssmith	[email protected]	susan	smith	12355
jmann	[email protected]	joan	mann	12357
mreynolds	[email protected]	mary	reynolds	12362
pjones	[email protected]	paula	jones	12365
mrussell	[email protected]	mike	russell	12367
pdunne	[email protected]	peter	dunne	12372
pward	[email protected]	paul	ward	12375
ssummers	[email protected]	sam	summers	12377
fwilliams	[email protected]	fred	williams	12382
mwhite	[email protected]	monica	white	12385

And we have a spreadsheet (in CSV format) with user’s hours, rates and employee numbers like below. We have added the ‘dept’ column, but this only has a number.

empno	hours	rate	dept
12345	37.5	12	1
12347	37.5	12	3
12352	40	12	2
12355	37.5	15	2
12357	37.5	12	1
12362	40	12	3
12365	37.5	15	1
12367	37.5	15	1
12372	37.5	25	2
12375	50	15	3
12377	37.5	15	1
12382	37.5	15	2
12385	37.5	15	3

We can import the CSV by using

$timesheets = import-csv c:\timesheet.csv

What we want to output is an array with the user’s name, employee number, hours worked, rate of pay, the total gross pay and their department name based on the number from the timesheets. (This is only a sample idea just so you get the idea of how it will work)
Using the command below we can generate a new array called $payroll with all the required data from above

$payroll = $users | Select-Object @{Name = 'Full name';Expression = {$_.'Givenname' + " " + $_.'surname'}}, @{Name = 'Employee Number';Expression = {$_.employeeid}},`
@{Name = 'Hours Worked'; Expression = {$empid = $_.employeeid ; [decimal]($timesheets | Where-Object {$_.empno -eq $empid}).hours}},`
 @{Name = 'Hourly Rate';Expression = {$empid = $_.employeeid ; [decimal]($timesheets | Where-Object {$_.empno -eq $empid}).rate}},`
 @{Name = 'Gross Pay';Expression = {$empid = $_.employeeid ; ([decimal]($timesheets | Where-Object {$_.empno -eq $empid}).rate)*[decimal]($timesheets | Where-Object {$_.empno -eq $empid}).rate}},`
 @{Name = 'Department';Expression = {$empid = $_.employeeid ; switch (($timesheets | Where-Object {$_.empno -eq $empid}).dept){
    "1"{"Sales"}
    "2"{"Finance"}
    "3"{"Marketing"}
    }
 }}

Note the SWITCH part

switch (($timesheets | Where-Object {$_.empno -eq $empid}).dept){
    "1"{"Sales"}
    "2"{"Finance"}
    "3"{"Marketing"}

This will replace the number with the corresponding data as you can see below:

Full name       : sam summers
Employee Number : 12377
Hours Worked    : 37.5
Hourly Rate     : 15
Gross Pay       : 225
Department      : Sales
Full name       : fred Williams
Employee Number : 12382
Hours Worked    : 37.5
Hourly Rate     : 15
Gross Pay       : 225
Department      : Finance
Full name       : monica white
Employee Number : 12385
Hours Worked    : 37.5
Hourly Rate     : 15
Gross Pay       : 225
Department      : Marketing

As always, have fun playing around with this and hopefully it helps you out in your scripting and feel free to comment.

One comment

  1. This is very fascinating, You are an excessively
    skilled blogger. I have joined your feed and stay up for in the hunt for extra of your wonderful post.

    Also, I have shared your website in my social networks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.