Last active
August 5, 2022 08:21
-
-
Save darrenjrobinson/6957a3e3d8cbf1309c7f490d8114436b to your computer and use it in GitHub Desktop.
Azure Log Analytics Report using KQL via REST API with PowerShell output to CSV. Associated blogpost https://blog.darrenjrobinson.com/?p=39731
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#The resource URI | |
$resource = "https://westus2.api.loganalytics.io" | |
#Your Client ID and Client Secret obtained when registering your WebApp | |
$clientid = "yourAADAppClientID" | |
$clientSecret = "yourAADAppClientSecret" | |
#Your Reply URL configured when registering your WebApp | |
$redirectUri = "https://localhost" | |
#Scope | |
$scope = "Data.Read" | |
Add-Type -AssemblyName System.Web | |
#UrlEncode the ClientID and ClientSecret and URL's for special characters | |
$clientIDEncoded = [System.Web.HttpUtility]::UrlEncode($clientid) | |
$clientSecretEncoded = [System.Web.HttpUtility]::UrlEncode($clientSecret) | |
$resourceEncoded = [System.Web.HttpUtility]::UrlEncode($resource) | |
$scopeEncoded = [System.Web.HttpUtility]::UrlEncode($scope) | |
#Refresh Token Path | |
$refreshtokenpath = "C:\pathForYourRT\LogAnalyticsRefresh.token" | |
#Functions | |
Function Get-AuthCode { | |
Add-Type -AssemblyName System.Windows.Forms | |
$form = New-Object -TypeName System.Windows.Forms.Form -Property @{Width = 440; Height = 640 } | |
$web = New-Object -TypeName System.Windows.Forms.WebBrowser -Property @{Width = 420; Height = 600; Url = ($url -f ($Scope -join "%20")) } | |
$DocComp = { | |
$Global:uri = $web.Url.AbsoluteUri | |
if ($Global:uri -match "error=[^&]*|code=[^&]*") { $form.Close() } | |
} | |
$web.ScriptErrorsSuppressed = $true | |
$web.Add_DocumentCompleted($DocComp) | |
$form.Controls.Add($web) | |
$form.Add_Shown( { $form.Activate() }) | |
$form.ShowDialog() | Out-Null | |
$queryOutput = [System.Web.HttpUtility]::ParseQueryString($web.Url.Query) | |
$Global:output = @{ } | |
foreach ($key in $queryOutput.Keys) { | |
$output["$key"] = $queryOutput[$key] | |
} | |
$output | |
} | |
function Get-AzureAuthN ($resource) { | |
# Get Permissions (if the first time, get an AuthCode and Get a Bearer and Refresh Token | |
# Get AuthCode | |
$url = "https://login.microsoftonline.com/common/oauth2/authorize?response_type=code&redirect_uri=$redirectUri&client_id=$clientID&resource=$resourceEncoded&scope=$scopeEncoded" | |
Get-AuthCode | |
# Extract Access token from the returned URI | |
$regex = '(?<=code=)(.*)(?=&)' | |
$authCode = ($uri | Select-String -pattern $regex).Matches[0].Value | |
Write-Output "Received an authCode, $authCode" | |
#get Access Token | |
$body = "grant_type=authorization_code&redirect_uri=$redirectUri&client_id=$clientId&client_secret=$clientSecretEncoded&code=$authCode&resource=$resource" | |
$Authorization = Invoke-RestMethod https://login.microsoftonline.com/common/oauth2/token ` | |
-Method Post -ContentType "application/x-www-form-urlencoded" ` | |
-Body $body ` | |
-ErrorAction STOP | |
Write-Output $Authorization.access_token | |
$Global:accesstoken = $Authorization.access_token | |
$Global:refreshtoken = $Authorization.refresh_token | |
if ($refreshtoken) { $refreshtoken | Out-File "$($refreshtokenpath)" } | |
if ($Authorization.token_type -eq "Bearer" ) { | |
Write-Host "You've successfully authenticated to $($resource) with authorization for $($Authorization.scope)" | |
} | |
else { | |
Write-Host "Check the console for errors. Chances are you provided the incorrect clientID and clientSecret combination for the API Endpoint selected" | |
} | |
} | |
function Get-NewTokens { | |
# We have a previous refresh token. | |
# use it to get a new token | |
$refreshtoken = Get-Content "$($refreshtokenpath)" | |
# Refresh the token | |
#get Access Token | |
$body = "grant_type=refresh_token&refresh_token=$refreshtoken&redirect_uri=$redirectUri&client_id=$clientId&client_secret=$clientSecretEncoded" | |
$Global:Authorization = Invoke-RestMethod https://login.microsoftonline.com/common/oauth2/token ` | |
-Method Post -ContentType "application/x-www-form-urlencoded" ` | |
-Body $body ` | |
-ErrorAction STOP | |
$Global:accesstoken = $Authorization.access_token | |
$Global:refreshtoken = $Authorization.refresh_token | |
if ($refreshtoken) { | |
$refreshtoken | Out-File "$($refreshtokenpath)" | |
Write-Host "Updated tokens" | |
$Authorization | |
$Global:headerParams = @{'Authorization' = "$($Authorization.token_type) $($Authorization.access_token)" } | |
} | |
} | |
$logAnalyticsWorkspace = "your Workspace ID" | |
$logAnalyticsBaseURI = "https://westus2.api.loganalytics.io/v1/workspaces" | |
# $logQuery = "AuditLogs | where SourceSystem == `"Azure AD`" | project Identity, TimeGenerated, ResultDescription | limit 50" | |
$logQuery = "AuditLogs | limit 50" | |
$logQueryBody = @{"query" = $logQuery } | convertTo-Json | |
# Get the Log Analytics Data | |
$result = invoke-RestMethod -method POST -uri "$($logAnalyticsBaseURI)/$($logAnalyticsWorkspace)/query" -Headers @{Authorization = "Bearer $($Global:accesstoken)"; "Content-Type" = "application/json" } -Body $logQueryBody | |
# Output Columns for CSV | |
$headerRow = $null | |
$headerRow = $result.tables.columns | Select-Object name | |
$columnsCount = $headerRow.Count | |
# Format the Report | |
$logData = @() | |
foreach ($row in $result.tables.rows) { | |
$data = new-object PSObject | |
for ($i = 0; $i -lt $columnsCount; $i++) { | |
$data | add-member -membertype NoteProperty -name $headerRow[$i].name -value $row[$i] | |
} | |
$logData += $data | |
$data = $null | |
} | |
# Export to CSV | |
$logData | export-csv C:\yourReportPath\logAnalytics.csv -NoTypeInformation |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment