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
}
Romiko Derbynew 
You must be logged in to post a comment.