Query Azure AppInsights with Powershell

In order to query AppInsights using powershell, you will need your AppInsights AppId and APIKey.

The important consideration is to ensure your JSON is valid, so always run it through a parser and use the correct escape characters for both JSON and PowerShell. Have a look at the string in $queryData.

The following code will query appinsights and generate csv files based on the batch size. It also using paging by leveraging:

| serialize | extend rn = row_number()

Happy DevOps Reporting 🙂

param (
[Parameter(Mandatory = $true)]
[string] $AppInsightsId,

[Parameter(Mandatory = $true)]
[string] $apiKey,

[Parameter(Mandatory = $false)]
[string]
$Timespan = "P7D",

[Parameter(Mandatory = $false)]
[int]
$batchSize = "10000",

[Parameter(Mandatory = $false)]
[string]
$OutputFolder = "C:\Output\",

[Parameter(Mandatory = $false)]
[string]$logFileName = "AppQuery.log",

[Parameter(Mandatory = $false)]
[string]$logFolder = "C:\Logs\"
)

Add-Type -AssemblyName System.Web

Import-Module .\Helpers.psm1 -Force -ErrorAction Stop
Import-Module .\Shared.Logging.psm1 -Force -Global
Import-Module .\Security.Helpers.psm1 -Force -Global

function prepareFileHeader($filenumber, $columnNames) {
$csvString = ""
ForEach ( $Property in $columnNames )
{
$csvString += "$($Property.Name),"
}
$csvString = $csvString.Substring(0,$csvString.Length -1)
$file = Join-Path $OutputFolder "batch-$i.csv"
$csvString | Out-File -filepath $file -Encoding utf8
$csvString = $null
}

function writeRecordsToFile($records) {
ForEach ( $record in $records )
{
$csvString = ""
foreach($cell in $record) {
$csvString += "$cell,"
}
$csvString = $csvString.Substring(0,$csvString.Length -1)
$file = Join-Path $OutputFolder "batch-$i.csv"
$csvString | Out-File -filepath $file -Encoding utf8 -Append -NoClobber
$csvString = $null
}
}

$logFilePath = PrepareToLog $logFolder $logFileName

try {
$url = "https://api.applicationinsights.io/v1/apps/$AppInsightsId/query"
$headers = @{"Content-Type" = "application/json"}
$headers.add("x-api-key", $apiKey)
$queryString = "?timespan=$Timespan"
$fullUrl = $url + $queryString

$queryTotalMessageCount = "traces\r\n | where message contains \`"Max Retry Count reached\`" and message contains \`"MessageService\`"\r\n | summarize count()"
$queryTotalMessageCountBody = "{
`"query`": `"$queryTotalMessageCount`"
}"
$resultCount = Invoke-WebRequest -Uri $fullUrl -Headers $headers -Method POST -Body $queryTotalMessageCountBody -ErrorAction Continue
$totalObject = ConvertFrom-Json $resultCount.Content

$totalRecords = $totalObject.tables.rows[0]
$pages = [math]::ceiling($totalRecords/$batchSize)
$startRow = 0
$endRow = $batchSize

Write-Host "Total Files: $pages for Batch Size: $batchSize"
For ($i=1; $i -le $pages; $i++) {
Write-Host "Processing File: C:\batch-$i.csv"
$queryData = "traces\r\n | extend TenantId = extract(\`"Tenant Id \\\\[[a-z0-9A-Z-.]*\\\\]\`", 0, message) | extend UniqueTransactionId = extract(\`"\\\\[[a-z0-9A-Z-. _\\\\^]*\\\\]\`",0 ,extract(\`"Message Transaction \\\\[[a-z0-9A-Z-._]*\\\\]\`", 0, message))\r\n | extend TransactionId = trim_start(\`"\\\\[\`", tostring(split(UniqueTransactionId, \`"_\`") [0]))\r\n | extend TransactionDateTicks = tostring(split(UniqueTransactionId, \`"_\`") [1])\r\n | extend PrincipalId = trim_end(\`"\\\\]\`", tostring(split(UniqueTransactionId, \`"_\`") [2]))\r\n | where message contains \`"Max Retry Count reached\`" and message contains \`"MessageService\`"\r\n | project TransactionId, TransactionDateTicks, PrincipalId, TenantId\r\n | summarize ErrorCount = count(TransactionId) by TransactionId, TransactionDateTicks, PrincipalId, TenantId\r\n | serialize | extend rn = row_number()\r\n | where rn > $startRow and rn <= $endRow"
$queryBody = "{
`"query`": `"$queryData`"
}"
$result = Invoke-WebRequest -Uri $fullUrl -Headers $headers -Method POST -Body $queryBody -ErrorAction Continue
$data = ConvertFrom-Json $result.Content
$startRow += $batchSize
$endRow += $batchSize

if($i -eq 1) {
$columnNames = $data.tables.columns | select name
}

prepareFileHeader $i $columnNames
writeRecordsToFile $data.tables.rows
}
} catch {
LogErrorMessage -msg $error[0] -filePath $logFilePath -fatal $true
}

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s