Reporting AD accounts per cost center

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:&quot;<br>$h_costcenteracct.GetEnumerator&#40;&#41; | Sort-Object Name<br>&quot;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.