Author Posts

January 1, 2012 at 12:00 am

by ingrdude at 2013-03-18 12:11:33

I have been tasked with creating a report, which I thought would not be too terribly difficult. I have worked on this so long and am so frustrated that I don't know what I'm doing is right or wrong. Any help would be appreciated.
This is the information pulled from AD, the samaccountname and department field.
Name Department
—- ———-
CRR – Department 1 (75401694)
V3B – D 2 (15791413)
BZ9 – Dept 3 (50519999)
PLB – Dept 3 (50519999)
L04 – Dept-4 (40700116)
L11 – Dept-4 (40700116)
L10 – D7 (10935476)
L09 – D7 (10935476)
L08 – 5thDept (10035)
L19 – 5thDept (10035)
L15 – Dept-4 (40700116)
E8N – Department 1 (75401694)
D5G – D 2 (50501413)
QA5 – Depart6 (5056333)
KGR – Org. Code: 00000000

I need to reference just the data in (). All valid dept #'s are in ().

I have set up an Excel file, conomap.xlsx to map the Dept number to Cost Center. We have hundreds of Dept numbers but only 6 cost centers.
75401694 = CostCenter1
15791413 = CostCenter2
50519999 = CostCenter3
40700116 = CostCenter4
10035 = CostCenter5
5056333 = CostCenter6
10935476 = CostCenter6

I am trying to get an Excel spreadsheet with a sheet with a list of accounts per Costcenter, accounts without a valid department, and a Master Summary Sheet
There are x accounts that fall under CostCenter1
There are x accounts that fall under CostCenter2
There are x accounts that fall under CostCenter3
There are x accounts that fall under CostCenter4
There are x accounts that fall under CostCenter5
There are x accounts that fall under CostCenter6

by DexterPOSH at 2013-03-18 15:42:00

Hi,

To accomplish what you can do is use the switch statment in PowerShell along with RegEx matching. What I did in the below code is imported your csv which is in this format]Name – Department
CRR – Department 1 (75401694)
V3B – D 2 (15791413)
BZ9 – Dept 3 (50519999)
PLB – Dept 3 (50519999)
L04 – Dept4 (40700116)[/code]

Then used switch to do regex matching and increase the count for every cost center by 1 when a match is found
$VerbosePreference = "continue"
Import-Csv -Delimiter "-" -Path C:\temp\test.csv |#
ForEach-Object -Process {
switch -regex ($_.department) {
75401694 {
Write-Verbose "$_ matched C1 "
$costcenter1 += 1

break
}
15791413 {
Write-Verbose "$_ matched C2 "
$costcenter2 += 1
break
}
50519999 {
Write-Verbose "$_ matched C3 "
$costcenter3 += 1
break
}

40700116 {
Write-Verbose "$_ matched C4 "
$costcenter4 += 1
break
}

10035 {
Write-Verbose "$_ matched C5 "
$costcenter5 += 1
break
}

5056333 {
Write-Verbose "$_ matched C6 "
$costcenter6 += 1
break
}
10935476 {
Write-Verbose "$_ matched C7 "
$costcenter7 += 1
break
}

default {
Write-Verbose "Not supported Cost Center"
break
}
}
}

"There are $CostCenter1 accounts that fall under CostCenter1"
"There are $CostCenter2 accounts that fall under CostCenter2"
"There are $CostCenter3 accounts that fall under CostCenter3"
"There are $CostCenter4 accounts that fall under CostCenter4"
"There are $CostCenter5 accounts that fall under CostCenter5"
"There are $CostCenter6 accounts that fall under CostCenter6"

This solution is not the most efficient one but you can build upon it.

Hope it helps

Regards

by MasterOfTheHat at 2013-03-19 08:43:04

DexterPOSH's way will work, but it requires hardcoding all of those dept code to cost center matchups inside the script, which is a lot of work and limits the ability to expand to other departments/cost centers later.

I would suggest pulling the dept code to cost center "table" into the script as a hash table and then reading the account name to dept code file line-by-line, looking for the dept code in the hash table, and then updating counters and output tables accordingly.

Maybe this will get you started...

Input files
costcenter.csv:
DeptCode,CostCenter
75401694,CostCenter1
15791413,CostCenter2
50519999,CostCenter3
40700116,CostCenter4
10035,CostCenter5
5056333,CostCenter6
10935476,CostCenter6

deptnames.csv:
SamAccountName,Department
CRR,Department 1 (75401694)
V3B,D 2 (15791413)
BZ9,Dept 3 (50519999)
PLB,Dept 3 (50519999)
L04,Dept-4 (40700116)
L11,Dept-4 (40700116)
L10,D7 (10935476)
L09,D7 (10935476)
L08,5thDept (10035)
L19,5thDept (10035)
L15,Dept-4 (40700116)
E8N,Department 1 (75401694)
D5G,D 2 (50501413)
QA5,Depart6 (5056333)
KGR,Org. Code: 00000000

The script:
$f_costcenter = Import-Csv C:\temp\costcenter.csv
$f_deptnames = Import-Csv C:\temp\deptnames.csv

$h_costcenter = @{}
$f_costcenter | ForEach-Object { $h_costcenter.Add($_.DeptCode,$_.CostCenter) }

$h_costcentercounts = @{}
$h_costcenteracct = @{}
$a_invaliddeptcode = @()

# matches only numbers inside parentheses
$deptcode_regex = [regex]"\((\d+)\)"
$deptname_regex = [regex]"^[^(]+"
$f_deptnames | ForEach-Object {
# valid dept code was found
if([regex]::Match($_.Department, $deptcode_regex).Success)
{
$deptcode = [regex]::Match($_.Department, $deptcode_regex).Groups[1].Value
$deptname = [regex]::Match($_.Department, $deptname_regex).Groups[0].Value

# make sure there is a cost center associated with the dept code
if($h_costcenter.ContainsKey($deptcode))
{
$costcenter = $h_costcenter.Get_Item($deptcode)
# if there is already a record in the "counts" hash table for the dept code, increment it
if($h_costcentercounts.ContainsKey($costcenter))
{
$counter = [int]$h_costcentercounts.Get_Item($costcenter)
$counter++
$h_costcentercounts.Set_Item($costcenter, $counter)
}
# if there isn't already a record in the "counts" hash table for the dept code, create it
else
{
$h_costcentercounts.Add($costcenter,1)
}

# if there is already a record in the "accounts" hash table for the cost center, add the account to it
if($h_costcenteracct.ContainsKey($costcenter))
{
$accts = $h_costcenteracct.Get_Item($costcenter)
$accts += $_.SamAccountName
$h_costcenteracct.Set_Item($costcenter, $accts)
}
# if there isn't already a record in the "accounts" hash table for the cost center, create it
else
{
$h_costcenteracct.Add($costcenter,@($_.SamAccountName))
}
}
else
{
"No cost center matched to department $deptcode"
}
}
# if the department code is invalid, add it to the "invalide dept code" array
else
{
$a_invaliddeptcode += "$($_.SamAccountName) - $($_.Department)"
}
}

"cost center counts:"
$h_costcentercounts.GetEnumerator() | Sort-Object Name
"`ncost center depts:"
$h_costcenteracct.GetEnumerator() | Sort-Object Name
"`ninvalid dept code:"
$a_invaliddeptcode.GetEnumerator() | Sort-Object Name

And the output:
No cost center matched to department 50501413
cost center counts:

Name Value
---- -----
CostCenter1 2
CostCenter2 1
CostCenter3 2
CostCenter4 3
CostCenter5 2
CostCenter6 3

cost center depts:
CostCenter1 {CRR, E8N}
CostCenter2 {V3B}
CostCenter3 {BZ9, PLB}
CostCenter4 {L04, L11, L15}
CostCenter5 {L08, L19}
CostCenter6 {L10, L09, QA5}

invalid dept code:
KGR - Org. Code: 00000000

And now I should probably get some actual work done...

by DexterPOSH at 2013-03-19 13:32:27

Great Work Charles !!

Your approach is much better...thanks for sharing it.

by ingrdude at 2013-03-20 05:37:18

Wow, thanks to both of you!! I think this is the hump I needed to get over. I will see if I can get it all put together.

by MasterOfTheHat at 2013-03-20 06:57:10

Come back and ask questions if you don't understand something! That's what the forum is for!

Glad you could use it.