Hierarchy of organisation

[DaveBunn]

Before I start, I've view TreeMap and Word Tree visualisations but they don't seem to do what I need (happy to be proven wrong though)

We use workday, we export the complete org hierarchy from workday and ingest that into a lookup table every day.  

The data contains - Name - OrgPosition- Manager - MiscDetails
So
Name=Dave Bunn
OrgPosition=12345_Dave_Bunn
Manager=1230_Mrs_Bunn
MiscDetails="some text about my job"

We then use the manager detail in the OrgPosition field to look for their manager and so on until we come across as service level manager (indicated in the misc details filed)

Name=Mrs Bunn
OrgPosition=1230_Mrs_Bunn
Manager=10_The_Big_Boss
MiscDetails="some text about Mrs Bunns job"

Name=Big Boss
OrgPosition=10_The_Big_Boss
Manager=0_The_Director
MiscDetails="Manager of HR"

What I would like to do is programmatically generate a hierarchy for any inputted user - with the named individual listed in the middle, their managers above and subordinates below.
I would like a visualisation similar to Word Tree Viz, but accept that it's more likely going to have to look like the principal name sandwiched beteen two fileds - one containing sorted managers and one containing sorted subordinates.

[danspav · Accepted solution]

Hi @DaveBunn,

I wrote the Word Tree Viz and as cool as it is... I don't think it will give you what you need in this case.

However, you may be interested in the Treeview Viz.

Here's some SPL to create a treeview representation of the hierarchy:

|makeresults 
| eval raw = "Name=\"Dave Bunn \", OrgPosition=\"12345_Dave_Bunn \",Manager=\"1230_Mrs_Bunn\",MiscDetails=\"some text about my job\"@@@
Name=\"Mrs Bunn\",OrgPosition=\"1230_Mrs_Bunn\",Manager=\"10_The_Big_Boss\",MiscDetails=\"some text about Mrs Bunns job\"@@@
Name=\"Big Boss\",OrgPosition=\"10_The_Big_Boss\",Manager=\"0_The_Director\",MiscDetails=\"Manager of HR\""
| makemv raw delim="@@@" | mvexpand raw | rename raw as _raw  | fields _raw | extract | fields - _time, _raw
``` Above: Creating the test data ```

| rename OrgPosition as id, Manager as parentid, Name as label
``` This bit is to fix any managers that don't appear in the data - i.e. 0_The_Director```
| appendpipe[|stats count by parentid| eval label=parentid, id=parentid | table label, id]
``` Reverse so the appendpipe appears first for the visualisation```
| reverse
| eval iconDoc="user-circle", iconFolderOpen="users"
| eval color=if(label="Dave Bunn","#DC4E41",null())

And here's what it looks like:

Alternatively, you could use the Network Diagram Viz:

SPL looks like this:

|makeresults 
| eval raw = "Name=\"Dave Bunn \", OrgPosition=\"12345_Dave_Bunn \",Manager=\"1230_Mrs_Bunn\",MiscDetails=\"some text about my job\"@@@
Name=\"Mrs Bunn\",OrgPosition=\"1230_Mrs_Bunn\",Manager=\"10_The_Big_Boss\",MiscDetails=\"some text about Mrs Bunns job\"@@@
Name=\"Big Boss\",OrgPosition=\"10_The_Big_Boss\",Manager=\"0_The_Director\",MiscDetails=\"Manager of HR\""
| makemv raw delim="@@@" | mvexpand raw | rename raw as _raw  | fields _raw | extract | fields - _time, _raw
``` Above: Creating the test data ```

| appendpipe[| stats count by Manager | eval type="user", nodeText=Manager, from=Manager  | table from, nodeText, type]
| appendpipe[| stats count by Name, OrgPosition | eval type="user", from=OrgPosition, nodeText=Name | table from, nodeText, type]
| appendpipe[| stats count by OrgPosition, Manager | eval from=Manager, to=OrgPosition | table from, to]

| eval color=if(nodeText="Dave Bunn","red",null())
| table from, to, nodeText, color, type
| search from=*

When choosing a hierarchal view, that gives you this: 

It will look a bit more impressive when there are more people and roles listed.

Hopefully those two visualisations give you something to work from.

Cheers,

Daniel