Converting object members before adding to array. RRS feed

  • General discussion

  • I'll probably butcher the question but here goes. I'll try keep it simple. 

    Take the following query run against MS Exchange Online. 

    $Mailboxes= Get-mailbox 

    $Mailbox contains 1000's of mailbox objects. Each object contains 241 members.

    My hypothetical goal is to get every object and each of it's 241 members into a CSV. The query logic is simple. The part i'm struggling with is that some members are not string's. They may be other Arrays so when you export-csv you see values such as "System.Collections.ArrayList" under "Delegates" column instead of TheUser1,TheUser2,TheUser3 which i'd see if i were to run the command: 

    $Mailboxes.delegates  | fl

    I totally understand why this happens. That's not my problem. What i'm struggling to do is convert these values to strings when working with objects that have large number of members.  If the mailbox object only had 3 members, say 2 strings, 1 array i'd simply create a new custom object. Something like this :

    foreach ($mailbox in $mailboxes) {
    $MailboxStringsOnly += $object
    $MailboxStringsOnly | export-csv ....

     I figure there must be a more efficient way to do this otherwise i'll end up with custom objects with hundreds of members when all i really need to do is convert a few. I had a few thoughts but not sure how to implement them. 

    1) Add only the members that need converting into a custom array and then add them to the existing array. You'd end up with extra columns in the csv but that's not a problem. 

    2) Some sort of for loop that looks for values that aren't strings and converts them into strings. I'm not sure how you'd do this. The conversion usually takes a bit of playing around with. 

    3) Try and convert the member before it's added to the array. Something like:

    $Mailboxes= Get-mailbox | DoSomethingToConverThistToString $_.delegates

    I figure this method would also be most efficient.

    Or maybe there's something really simply I just need telling about. 

    Thanks in advance for any suggestions. 

    • Changed type jrvModerator Tuesday, November 19, 2019 9:11 PM not a question
    Tuesday, November 19, 2019 6:18 PM

All replies

  • I really don't like to ask this question but why would you want to do this? You'll have an unwieldy CSV, not just in size, but in the number of columns.

    If you're doing this to analyze, say, what mailboxes have what combination of properties then I'd suggest you're better off doing the analysis in code with the "live" mailboxes. If you're doing this to move data between systems, then using a XML (or JSON) format would be better (you can export and import those without doing any "backwards" conversions from strings to other objects).

    I'm not trying to be dismissive, but I worked with Exchange since it's pre-release (I mean since before Exchange 4.0) and I've never been tempted to do something like this (at least not with every property!). I'm just trying to understand what your goal is.

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, November 19, 2019 7:49 PM
  • Fair question. Not such a straight forward answer. The example was 50% hypothetical. My end goal is to have an array with objects that only include strings so I can export them to SQL for reporting. Same concept as export-csv. It has nothing to do with exchange administration. I could have equally used an example using Get-DnsServerResourceRecord and use DNS records as the example which also have embedded system arrays. 

    How do i get them into SQL? Using a scheduled ps1 job that runs as frequently as i deem necessary on our job scheduler. The job uses DBATools PS Module to build /update/drop tables using $TheStringBasedArray

    Why do i want to get the results into SQL? Once you get data into SQL reporting becomes a breeze. I can join the tables in all sorts of different ways and then use tools such as Power BI to create really nice reports. 

    I'd still like to know if there's a solution to my original questions but given your knowledge of Exchange Online , can you recommend a better way to automate the process of getting a full dump of attributes into SQL? It also applies to Azure in general. 

    Anyway.. off topic but you asked :)


    Tuesday, November 19, 2019 8:23 PM
  • One other point to add. A reasonable questions is to ask why I need all 240 (or however many) object properties. The answer is I don't. I may need max 30 in some cases but it's much quicker to do this:

    $array = get-something

    rather than this

    $array = get-something | select-object prop1,prop15,prop37,.... ........ 
    #30 properties and several typos later

    The overhead in SQL is negligible

    • Edited by TheObrien Tuesday, November 19, 2019 8:41 PM
    Tuesday, November 19, 2019 8:41 PM
  • Please take the time to learn how to ask a clear question. Your question is vague and cannot be understaood.

    See: How to ask questions in a technical forum


    Tuesday, November 19, 2019 8:43 PM
  • Is it?  Rich didn't seem to have a problem. 
    Tuesday, November 19, 2019 8:48 PM
  • Rather than maintain the information in two places (the AD and SQL), why not query the AD from SQL? You can use all the TSQL you like. Write your queries the way you want and just get the properties you need for each query.

    Here are some examples:

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, November 19, 2019 9:05 PM
  • Is it?  Rich didn't seem to have a problem. 

    You said it was hypothetical which is a form of planned vagueness.

    Hypothetically you would have to create custom objects that define and capture the output you want which is pretty much what Rich was vaguely referring to.

    Define the properties you want in the output and use that definition to build the custom objects.   What could be easier?

    If you need help with doing that that write the script and ask a specific question about something that is not working.

    The biggest issue with the original code is that it has a complex object collection as a property and that cannot be exported.  You need to flatten the hierarchy to place it in a CSV or, as Rich noted, use a hierarchical data structure like XML.

    To flatten you will need to enumerate the collection and generate the output you need.

    The Gallery has examples and may have the exact code you need.  Have you looked?


    Tuesday, November 19, 2019 9:10 PM
  • If your intention is for flatten things into strings you'd have to know what the properties contain. Some are objects that offer a ToString() method (easy), some don't. You'd have to figure out how to convert each of those objects into a format suitable for your purpose.

    I was going to ask why you'd need all the available properties, but you answered that.

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, November 19, 2019 9:11 PM
  • "You said it was hypothetical which is a form of planned vagueness."

    It's hypothetical because the full script(s) is irrelevant. I thought the purpose of forums it to discuss and learn, not just fix people's broken scripts. 

    "Hypothetically you would have to create custom objects that define and capture the output you want which is pretty much what Rich was vaguely referring to."

    You mean what I clearly stated i was doing as a solution? The purpose of the question , as per the subject was to discuss if there's a more effective way of doing this before it get's into the array. Rich was "vaguely" recommending I do it differently if my objective as to administer Exchange. 

    "The biggest issue with the original code is that it has a complex object collection as a property and that cannot be exported.  You need to flatten the hierarchy to place it in a CSV"

    Again - I clearly stated I understand this. 

    Tuesday, November 19, 2019 9:31 PM
  • I can only say that there can only be a question of a more effective way when you have a script that actually works. As per Rich your output will be useless as defined.

    As for your other issues I have changed the type of this thread to "Discussion" since you are discussing things and not really asking a question.  Ty yo understand that the categories are to help those looking for answers.  Discussions are not answer oriented but are just probes for information.

    A forum is not a good place to get personal training in PowerShell or Exchange. Your issue is a combination of some unusable PowerShell code and a need to understand what Exchange has and how it is returned.

    Start with: Delegates are security types and are complex.  YOU likely don't want all of a delegate.  If you notice the property is "Delegates" with an "s" because it is a collection of unknown size which means it has to be enumerated in some way to be useful.

    When working with types from anywhere it is also useful to look at the documentation for the type.  THat6 shows us that a mailbox object does not have a property called "Delegates" so that will also be a problem.


    Get-MailboxPermission might work but it does not have a documented return type so you would have to inspect the returned object and search for its exact type.  I don't remember but I believe it is an AD ACL wrapped for Exchange.

    If you are trying to migrate mailboxes then you should use one of the many tools available that will do all of this and more. 

    Here are many articles and tools we would use to migrate mailboxes:


    Tuesday, November 19, 2019 10:08 PM
  • It has nothing to do with Exchange. That part at least is obvious. 

    The questions was is there a more efficient way to get data in string form from an object rather than creating a custom object. 

    Jeees... don't know whether to laugh or cry. It's as if you're so desperate to rack up points as a moderator you're not paying any attention to either the original content or subsequent reply's

    Just delete the thread if it's so outrageous. That surely gets you a few more points?

    Tuesday, November 19, 2019 11:13 PM
  • As I implied - there is no way to convert a collection of complex objects into a string. THe following line won't work:


    "delegates" is a collection and referencing a property can return an array or a collection of objects. 

    If you specify a property that is a string or number then you can do this:

    delegates=$mailbox.delegates.Name -join '|'

    Part of the issue is that you are speculating about PowerShell code (hypothesizing) and you do not know much about PowerShell or coding so your questions are complicated and hard to understand because of the example and usage of technical terms.

    If you spend some time learning basic PowerShell formally and not by copying and guessing you will understand how to write these things.  You cannot learn any technology at a technical level by guessing and asking questions.  There ae dozens of articles and blogs written that point this out.


    Tuesday, November 19, 2019 11:21 PM