SOLVED

Microsoft Graph and PowerShell - extracting the data into csv

%3CLINGO-SUB%20id%3D%22lingo-sub-284548%22%20slang%3D%22en-US%22%3EMicrosoft%20Graph%20and%20PowerShell%20-%20extracting%20the%20data%20into%20csv%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284548%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20someone%20can%20point%20me%20in%20the%20right%20direction%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20messing%20about%20connecting%20up%20PowerShell%20(using%20PnP)%20to%20the%20Microsoft%20Graph%20API%20with%20a%20view%20of%20returning%20the%20data%20into%20a%20csv%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59376iD970E71EFD641037%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22output.PNG%22%20title%3D%22output.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAll%20I'm%20wanting%20to%20achieve%20is%20to%20retrieve%20the%20table%20header%20information%20and%20the%20results%2C%20is%20there%20a%20way%20to%20extrapolate%20that%20data%20out%20and%20output%20to%20a%20csv%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESteve%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-284548%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Graph%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPowerShell%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPowershell%20and%20Rest%20API%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284950%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Graph%20and%20PowerShell%20-%20extracting%20the%20data%20into%20csv%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284950%22%20slang%3D%22en-US%22%3EHuge%20thanks%20for%20this%20Kevin!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284920%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Graph%20and%20PowerShell%20-%20extracting%20the%20data%20into%20csv%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284920%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steve%2C%20you%20can%20use%20below%20powershell%20script%20for%20your%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EConnect-PnPOnline%20-Scopes%20%22User.Read.All%22%2C%22User.ReadBasic.All%22%2C%22Reports.Read.All%22%3CBR%20%2F%3E%24accesstoken%20%3DGet-PnPAccessToken%3CBR%20%2F%3E%24apiUrl%20%3D%20%22https%3A%2F%2Fgraph.microsoft.com%2Fv1.0%2Freports%2FgetSharePointSiteUsageFileCounts(period%3D'D7')%22%3CBR%20%2F%3E%24result%20%3D%20Invoke-RestMethod%20-Headers%20%40%7BAuthorization%20%3D%20%22Bearer%20%24accesstoken%22%7D%20-Uri%20%24apiUrl%20-Method%20Get%3CBR%20%2F%3E%23Remove%20special%20chars%20from%20header%3CBR%20%2F%3E%24result%20%3D%20%24result.Replace('%C3%AF%C2%BB%C2%BFReport%20Refresh%20Date'%2C'Report%20Refresh%20Date')%3CBR%20%2F%3E%23Convert%20the%20stream%20result%20to%20an%20array%3CBR%20%2F%3E%24resultarray%20%3D%20ConvertFrom-Csv%20-InputObject%20%24result%3CBR%20%2F%3E%23Export%20result%20to%20CSV%3CBR%20%2F%3E%24resultarray%20%7C%20Export-Csv%20%22C%3A%5CSiteUsage.csv%22%20-NoTypeInformation%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Hey all,

 

Hoping someone can point me in the right direction please?

 

I'm messing about connecting up PowerShell (using PnP) to the Microsoft Graph API with a view of returning the data into a csv format.

 

output.PNG


All I'm wanting to achieve is to retrieve the table header information and the results, is there a way to extrapolate that data out and output to a csv file?

 

Many thanks,

 

Steve

2 Replies
Best Response
Solution

Hi Steve, you can use below powershell script for your need.

 

Connect-PnPOnline -Scopes "User.Read.All","User.ReadBasic.All","Reports.Read.All"
$accesstoken =Get-PnPAccessToken
$apiUrl = "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageFileCounts(period='D7')"
$result = Invoke-RestMethod -Headers @{Authorization = "Bearer $accesstoken"} -Uri $apiUrl -Method Get
#Remove special chars from header
$result = $result.Replace('Report Refresh Date','Report Refresh Date')
#Convert the stream result to an array
$resultarray = ConvertFrom-Csv -InputObject $result
#Export result to CSV
$resultarray | Export-Csv "C:\SiteUsage.csv" -NoTypeInformation
Huge thanks for this Kevin!