Posted in : Office 365, Powershell

2 months ago

I was tasked to export data from Microsoft Graph about users to CSV in order to build reports in Qlickview.
The problem I had was that the user object in Microsoft Graph are polymorphic, meaning that the output we get are not required to have the same properties for each of the user object we get returned. When we then try to convert the return data to CSV using the builtin ”Convertto-Csv” or ”Export-CSV” cmdlets, they only build headers from the first object, which means much of the data of the other objects might get truncated. The solution to the problem seems quite simple in my head, but when I did a quick google search for the problem I didn’t find any relevant results.
So, here is my solution.
First I get all the properties in a big array for all the user objects. I do this by running Get-Member on each of the objects, and then join in the result with a Select -Unique. When the headers are all found and sorter I write them to my csv file.

$AllProperties = 0..$users.count | Foreach-object{
    $users[$_] | Get-Member -membertype NoteProperty | Select -ExpandProperty Name
} | Select -unique | Sort-Object
$AllProperties -join ',' | out-file "$outFile.csv"

After that I compile an expression to run on each of the objects, to expand the properties that exist, and fill the blanks with null, The expression outputs a commaseparated string by default that matches the headers we generated earlier.

$AllPropertiesExpression = "`"$(($AllProperties | Foreach-object{'$($_.{0})' -f $_}) -join ',')`""

When the expression is prepared, we need to execute it and append the output to the csv file.

$users | Foreach-object{(Invoke-Expression $allPropertiesExpression)}  | out-file "$outFile.csv" -append

So even though this was only 4 rows, it helped me to convert the nosql structured output to a structured CSV without losing any data on the way.
Hope this helps someone!
Full code below.

$AllProperties = 0..$users.count | Foreach-object{$users[$_] | get-member -membertype NoteProperty | Select -ExpandProperty Name} | Select -unique | Sort-Object
$AllProperties -join ',' | out-file "$outFile.csv"
$AllPropertiesExpression = "`"$(($AllProperties | Foreach-object{'$($_.{0})' -f $_}) -join ',')`""
$users | Foreach-object{(Invoke-Expression $allPropertiesExpression)}  | out-file "$outFile.csv" -append

 

Tags : Azure AD, CSV, data, Microsoft Graph, msgraph, NoSql, OData API, PowerBI, qlickview, Security, users

Add comment

Your comment will be revised by the site if needed.