Custom report to show Secretaries and who they support

Question

I am trying to create a custom report which lists all secretaries and whom they support, I have a working report except for when there are multiple secretaries supporting a user it is displaying them as multiple values on the same row and the report specific column is presenting an error due to the unexpected multi value being passed to it.

I have created a directory search report and added the columns Secretary, Secretary Object GUID (report specific column), Name, and Object GUID.

The report specific column appears to be working where there are single secretaries assigned but when trying to export the report it fails because multiple values are being passed to the report specific column, specifically the error is "Exception calling BindToObjectByDN - Cannot parse ADsPath String, line 8". The PowerShell is:

$obj = $Context.GetDirectoryObject() try { $secretaryDN = $obj.Get("secretary") $secretary = $Context.BindToObjectByDN($secretaryDN) $Context.Value = $secretary.Get("ObjectGUID") } catch [System.Runtime.InteropServices.COMException] { }

Is it possible to separate out multiple Secretary values to their own lines?

I am wanting to export a CSV containing: Secretary (displayName), Secretary (Object GUID), User (displayName), User (Object GUID)

The CSV should have a line for each person a secretary supports and where there are multiple values then that should be separated out so each secretary would have its own row.

For example:

Secreatry	Object Guid	User	Object Guid
Jane	1234567	Steven	32132331
Jane	1234567	Alice	4324523
Paul	234572	Steven	32132331
Paul	234572	Michael	58745646
Paul	234572	Alice	4324523
Mary	95723272	Lara	0937546732
Mary	95723272	Kathy	932742

In the above, Jane and Paul look after Steven and Alice and currently appear as multiple values and need to be separated out.

Comments

Hello Matt,

Sorry for the confusion, but we are not sure how exactly you need the report to look like. Your example is missing the Name column and that is not something you can remove. Please, provide another example of the desired report with a detailed description taking this into account.

As a side note, you can create a report with just a Directory search based on the criteria including users with the Secretary property being not empty. In this case, the Name column will include the name of the user and the Secretary column will include all the accounts specified in the property. No scripts will be required. Unfortunately, this approach does not allow having a separate line for each secretary and including their Object GUIDs into the report.