Building an OrgChart in PowerShell

twyrch · 2021-12-02T15:47:26.000Z

I have a list of employees with their Employee ID, Name, Manager Name and Manager ID. I’m trying to run a Powershell script that will link the employee to their manager, and that manager, to their manager, etc, but my code isn’t giving me the results I’m hoping for. Here’s an example of my CSV:

ID,FirstName,LastName,ManagerID
5,Smith,John,8
8,Jones,James,332
332,Schmidt,Johan,15
10,Simmons,Richard,3032
15,MakeIt,Willie,3032
3032,Wont,Betty,4507
4507,Bunns,Seymore,9575
9575,TooFar,Eileen,8988
8988,Beatem,Dewey,0

In this example, John Smith, reports to James Jones, who reports to Johan Scmidt, who reports to Willie MakeIt, who reports to Betty Wont, who reports to Seymore Bunns, who reports to Eileen TooFar, who reports to Dewey Beatem. Since Richard Simmons also reports to Willie MakeIt, it should reflect that in the output.

This is an example of my script.

$Employees = Import-CSV -Path "C:\Scripts\OrgChart\ActiveEmployees.csv"
$Employees | Get-Member
Function Add-ManagerLevel([Object[]]$Employees) {

    Function Get-Level($ManagerID, [INT]$Level = 0) {# 0 is the top of heirarchy
        $Manager - $Employees | Where-Object {_.Id -eq $ManagerID}
        IF (!$Manager) {$Level} Else {Get-level $Manager.ManagerID ($Level + 1)}
    }

    $Employees | ForEach-Object {
        $_ | Add-member "Level" (Get-level $_.ManagerID) -PassThru
    }
}
$Employees | Export-Csv -Path "C:\Scripts\OrgChart\OrgChart.csv" -NoTypeInformation -Encoding UTF8

I get this output from the script:

PS C:\Scripts\OrgChart> .\Get-OrgChart.ps1

  TypeName: System.Management.Automation.PSCustomObject

Name       Member        Type   Definition                  
----       ---------- ----------                  
Equals      Method       bool   Equals(System.Object obj)
GetHashCode Method       int    GetHashCode()          
GetType     Method       type   GetType()              
ToString    Method       string ToString()          
FirstName   NoteProperty string FirstName=Alexander  
ID          NoteProperty string ID=5                
LastName    NoteProperty string LastName=Davis      
ManagerID   NoteProperty string ManagerID=8

What am I doing wrong? Please help if you can. We can make this work in SQL, but we’re trying to do it in PowerShell.

Neally · 2021-12-02T15:58:18.000Z

Welcome

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don't say "Need help" or "PowerShell Help", actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy, past…

192033ab-bb8f-4032-88a5-8e2313af0344-codebutton_small.png

Neally Bot

twyrch · 2021-12-02T16:45:01.000Z

Done. Better?

Neally · 2021-12-02T17:49:36.000Z

Twyrch:

Done. Better?

Yes, thank you.

the output you see comes from this line

$Employees | Get-Member

Your script is rather incompelte.

You define functions, but you are not calling them?

‘add-managerlevel’ is not called. and there are syntax errors

Where-Object {_.Id -eq $ManagerID}
#############{$_.Id

twyrch · 2021-12-02T18:02:42.000Z

Thank you. I’ll correct these issues and try again.

twyrch · 2021-12-02T20:53:08.000Z

Neally:

Twyrch:

Done. Better?

Yes, thank you.

the output you see comes from this line
$Employees | Get-Member
Your script is rather incompelte.

You define functions, but you are not calling them?

‘add-managerlevel’ is not called. and there are syntax errors
Where-Object {_.Id -eq $ManagerID}
#############{$_.Id

I corrected the Function and the syntax and now it’s at least producing the list of names, but they’re all at level 0. I also get this error prior to the script producing the names with the levels.

Method invocation failed because [System.Object[]] does not contain a method named 'op_Subtraction'.
At C:\Scripts\OrgChart\Get-OrgChart.ps1:6 char:9
+         $Manager - $Employees | Where-Object {$_.Id -eq $ManagerID}
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (op_Subtraction:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

This is my updated code.

$Employees = Import-CSV -Path "C:\Scripts\OrgChart\ActiveEmployees.csv"
$Employees | Get-Member
Function Add-ManagerLevel([Object[]]$Employees) {

    Function Get-Level($ManagerID, [INT]$Level = 0) {# 0 is the top of heirarchy
        $Manager - $Employees | Where-Object {$_.Id -eq $ManagerID}
        IF (!$Manager) {$Level} Else {Get-level $Manager.ManagerID ($Level + 1)}
    }

    $Employees | ForEach-Object {
        $_ | Add-member "Level" (Get-level $_.ManagerID) -PassThru
    }    
}
Add-ManagerLevel $Employees | Sort-Object Level | Format-Table
$Employees | Export-Csv -Path "C:\Scripts\OrgChart\OrgChart.csv" -NoTypeInformation -Encoding UTF8

I’m afraid I’m in over my head here.

Neally · 2021-12-02T20:54:47.000Z

can you show an example of what you want the result to look like? I’m not following.

tulioarends · 2021-12-02T21:37:00.000Z

Just build the directed graph from the data and traverse it a couple of times to make a lookup hash table and assign the levels. Afterwards you can use the same traversal technique from the top level to generate the report in any way you want.

$testData = @'
ID,FirstName,LastName,ManagerID
5,Smith,John,8
8,Jones,James,332
332,Schmidt,Johan,15
10,Simmons,Richard,3032
15,MakeIt,Willie,3032
3032,Wont,Betty,4507
4507,Bunns,Seymore,9575
9575,TooFar,Eileen,8988
8988,Beatem,Dewey,0
'@ | ConvertFrom-Csv |
ForEach-Object {
    [PSCustomObject][ordered]@{
        ID            = $_.ID
        FirstName      = $_.FirstName
        LastName      = $_.LastName
        ManagerID     = $_.ManagerID
        DirectReports = @()
        Level         = 0
    }
}

$lookUp = @{}
foreach ($employee in $testData) {
    $lookUp.Add($employee.ID, $employee)
}

$rootCandidate = $null
foreach ($employee in $testData) {
    if ($lookUp.ContainsKey($employee.ManagerID)) {
        $lookUp[$employee.ManagerID].DirectReports += $employee
    }
    else {
        $rootCandidate = $employee
    }
}

function Set-Level( $employee, $level) {
    $employee.Level = $level
    foreach ($report in $employee.DirectReports) {
        Set-Level $report ($level+1)
    }    
}

Set-Level $rootCandidate 1
$lookUp.Values

Neally · 2021-12-02T21:47:29.000Z

I would have never guessed that’s what he wanted from what he wrote.

clear-host 

$testData = @'
ID,FirstName,LastName,ManagerID
5,Smith,John,8
8,Jones,James,332
332,Schmidt,Johan,15
10,Simmons,Richard,3032
15,MakeIt,Willie,3032
3032,Wont,Betty,4507
4507,Bunns,Seymore,9575
9575,TooFar,Eileen,8988
8988,Beatem,Dewey,0
'@ | ConvertFrom-Csv |
ForEach-Object {
    [PSCustomObject][ordered]@{
        ID            = $_.ID
        FirstName     = $_.FirstName
        LastName      = $_.LastName
        ManagerID     = $_.ManagerID
        DirectReports = @()
        Level         = 0
    }
}

$lookUp = @{}
foreach ($employee in $testData) {
    $lookUp.Add($employee.ID, $employee)
}

$rootCandidate = $null

foreach ($employee in $testData) {
    if ($lookUp.ContainsKey($employee.ManagerID)) {
        $lookUp[$employee.ManagerID].DirectReports += $employee
    }
    else {
        $rootCandidate = $employee
    }
}

function Set-Level( $employee, $level) {
    $employee.Level = $level
    foreach ($report in $employee.DirectReports) {
        Set-Level $report ($level+1)
    }    
}

Set-Level $rootCandidate 1

$data = 
$lookUp.Values |
select id, firstname,lastname,managerid,level, @{n='DirectReportsCount';e={($_.directreports |measure).count}} | sort level

$data | format-table -autosize -wrap

$data | export-csv "C:\Scripts\OrgChart\OrgChart.csv" -NoTypeInformation -Encoding UTF8

ebe4fd3e-40a3-4542-9654-5be6524b7580-2021-12-02_15-47-05.png

twyrch · 2021-12-06T12:23:15.000Z

@alexw Sorry if I wasn’t clear.

@tulioarends Thanks, I’ll give that a try. I have over 4000 lines in my CSV. Do I need to put all 4000 lines into the PowerShell script?

$testData = @'
ID,FirstName,LastName,ManagerID
5,Smith,John,8
8,Jones,James,332
332,Schmidt,Johan,15
10,Simmons,Richard,3032
15,MakeIt,Willie,3032
3032,Wont,Betty,4507
4507,Bunns,Seymore,9575
9575,TooFar,Eileen,8988
8988,Beatem,Dewey,0
'@ | ConvertFrom-Csv |

twyrch · 2021-12-06T12:37:46.000Z

Neally:

I would have never guessed that’s what he wanted from what he wrote.

clear-host 

$testData = @'
ID,FirstName,LastName,ManagerID
5,Smith,John,8
8,Jones,James,332
332,Schmidt,Johan,15
10,Simmons,Richard,3032
15,MakeIt,Willie,3032
3032,Wont,Betty,4507
4507,Bunns,Seymore,9575
9575,TooFar,Eileen,8988
8988,Beatem,Dewey,0
'@ | ConvertFrom-Csv |
ForEach-Object {
    [PSCustomObject][ordered]@{
        ID            = $_.ID
        FirstName     = $_.FirstName
        LastName      = $_.LastName
        ManagerID     = $_.ManagerID
        DirectReports = @()
        Level         = 0
    }
}

$lookUp = @{}
foreach ($employee in $testData) {
    $lookUp.Add($employee.ID, $employee)
}

$rootCandidate = $null

foreach ($employee in $testData) {
    if ($lookUp.ContainsKey($employee.ManagerID)) {
        $lookUp[$employee.ManagerID].DirectReports += $employee
    }
    else {
        $rootCandidate = $employee
    }
}

function Set-Level( $employee, $level) {
    $employee.Level = $level
    foreach ($report in $employee.DirectReports) {
        Set-Level $report ($level+1)
    }    
}

Set-Level $rootCandidate 1

$data = 
$lookUp.Values |
select id, firstname,lastname,managerid,level, @{n='DirectReportsCount';e={($_.directreports |measure).count}} | sort level

$data | format-table -autosize -wrap

$data | export-csv "C:\Scripts\OrgChart\OrgChart.csv" -NoTypeInformation -Encoding UTF8

I ran the script exactly as you listed it and it did give me output but when I put in the actual data, it did not list the employees in the correct levels. Do the names have to be in a specific order? HR provides them in the order of their employee ID.

Here’s an example of the output I’m receiving.

931  Brandon          Brooks              391           0                  0
867  Brian            Jones               8             0                  0
8718 Meghan           Stackhouse          9473          0                  1
479  Melanie          Edwards             500           0                  3
7854 Gordon           Davis               7118          0                 28
3162 Patrick          Ingram              7086          0                  0
8742 Evan             Goble               3241          0                  0
5432 Grant            Spanier             31            0                  0
7302 Charles          Wright              3009          0                  0
8163 Donna            Thedford            6836          0                  1
4539 Regina           Ellison             4541          0                  0
7652 John             Fisher              7916          0                  0

tulioarends · 2021-12-06T17:57:47.000Z

Twyrch:

@alexw Sorry if I wasn’t clear.

@tulioarends Thanks, I’ll give that a try. I have over 4000 lines in my CSV. Do I need to put all 4000 lines into the PowerShell script?
$testData = @'
ID,FirstName,LastName,ManagerID
5,Smith,John,8
8,Jones,James,332
332,Schmidt,Johan,15
10,Simmons,Richard,3032
15,MakeIt,Willie,3032
3032,Wont,Betty,4507
4507,Bunns,Seymore,9575
9575,TooFar,Eileen,8988
8988,Beatem,Dewey,0
'@ | ConvertFrom-Csv |

No, I just used ConvertFrom-CSV for testing with the data you provided. It does the same thing as Import-CSV but without needing to read a file.

tulioarends · 2021-12-06T18:02:04.000Z

One thing to note is that my script onle takes into consideration one top level exec. I assumed that this is a tree graph (only one root node).
If that is not the case the code needs to be modified slightly.

twyrch · 2021-12-08T13:50:21.000Z

tulioarends:

One thing to note is that my script onle takes into consideration one top level exec. I assumed that this is a tree graph (only one root node).
If that is not the case the code needs to be modified slightly.

@tulioarends No, I only have one top level exec. He has a 0 after his name, as in the example data. I replaced the example data with real data, though. Does that matter?

twyrch · 2021-12-08T14:07:36.000Z

tulioarends:

One thing to note is that my script onle takes into consideration one top level exec. I assumed that this is a tree graph (only one root node).
If that is not the case the code needs to be modified slightly.

I found the problem. I had to have my top level executive at the bottom, so I sorted my list on the ManagerID column from largest to smallest and it worked.