[midPoint] Query midpoint 4.4.3 postgre DB

[Chai, Lisa]

Hi,
We recently upgraded to midpoint 4.4.3 with postgres DB, which replaced SQL DB, since postgres DB has different structures, i.e. the user extension in a json object in one column fullobject in m_user table. we have manager extension attributes. Each user has a manager associated with the user profile. We need to provide roll-up reporting by managers at various levels. Therefore, I am looking for a query that shows the reporting hierarchy for any given person as follows:
Table structure:

  *   Person ID
  *   Name (Example: Joe Doe)
  *   L0 Manager Name (Example: Name of CEO)
  *   L0 Person ID (...)
  *   L1 Manager (Example: Name of CIO)
  *   L1 Person ID (...)
  *   L2 Manager (Example: Name of Division Director)
  *   L2 Person ID (...)
  *   L3 Manager (Example: Name of Department Manager)
  *   L3 Person ID (...if exist, may be null or blank otherwise)
  *   L4 Manager (example: Name of Sub-Department Manager if exists or blank)
  *   L4 Person ID (...)
  *   L5 Manager (example: blank)
  *   L5 Person ID (...)
Has anyone created a similar SQL query and would you be willing to share it here?
Alternatively, would anyone know which Midpoint tables would hold extension attributes other than m_user?


Thanks!

Lisa

 
 ----
Secured by Paubox - HITRUST CSF certified
https://www.paubox.com
----
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20231101/c631b1af/attachment-0001.htm>