Reporting AD accounts per cost center

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 3 months ago.

  • Author
    Posts
  • #5682

    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.

You must be logged in to reply to this topic.