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

A sample scenario:

We start off with an array of users that we get from Active Directory and we need to correlate the data with another array that contains time sheet information about the users. The first array is $users – and we get this by running

$users = get-aduser –prop name, mail, Givenname, Surname, employeeid -filter *

Here is the output from the $users array

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, rate of pay and employee numbers like below.

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

 

We can import this 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 and the total gross pay. (This is only a sample script so you can 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 both arrays

$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}}

 

There is much more that can be achieved using the select-object and I will cover these in the next post

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

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.