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

Install NServiceBus as a service with Powershell

Hi,

I have just completed a script that can be used to install a service bus host as a windows service. It is nice to use, when delegating the installs to the support team, this is always done via NServiceBus.Host.exe.

It can always be tricky parsing arguments in PowerShell. The trick is to use the Start-Process command, and isolate the arguments into it’s own variable.

Here is the two scripts.

Download the script

The one will install the license and the other installs your endpoints as a windows service. All you now need to do, is just type in the password.

It relies on a directory structure, where.

Root\powershellscripts.ps1
Root\ServiceNameA\Debug or Root\ServiceNameA\Release
Root\ServiceNameB\Debug or Root\ServiceNameB\Release

I hope this makes installing all your endpoints easy.

Download the script

Now, remember, if you install Subscriber endpoints before publishers, you will get a race condition, so with this script, add the endpoints in the array argument in the correct dependent order. First install all the publisher endpoints first, then the subscriber endpoints.

Romiko

Automate #Azure Blob Snapshot backups with @Cerebrata

Hi,

Leveraging the cerebrata cmdlets for Azure, we can easily backup our blob containers via snapshot, this will prove useful for Page Blobs that are Random Access i.e. VHD’s on Cloud Drive

Here is how Purging Snapshots works

#requires -version 2.0
param (
	[parameter(Mandatory=$true)] [string]$AzureAccountName,
	[parameter(Mandatory=$true)] [string]$AzureAccountKey,
	[parameter(Mandatory=$true)] [array]$BlobContainers
)

$ErrorActionPreference = "Stop"

if ((Get-PSSnapin -Registered -Name AzureManagementCmdletsSnapIn -ErrorAction SilentlyContinue) -eq $null)
{
	throw "AzureManagementCmdletsSnapIn missing. Install them from Https://www.cerebrata.com/Products/AzureManagementCmdlets/Download.aspx"
}

Add-PSSnapin AzureManagementCmdletsSnapIn -ErrorAction SilentlyContinue

function SnapShotBlobContainer 
{
	param ( $containers, $blobContainerName )
	Write-Host "Starting snapshot $blobContainerName"

	$container = $containers | Where-Object { $_.BlobContainerName -eq $blobContainerName }

	if ($container -eq $null)
	{
		Write-Host  "Container $blobContainerName doesn't exist, skipping snapshot"
	}
	else
	{
        Write-Host  "Found blob container $blobContainerName"
Checkpoint-BlobContainer -Name $container.BlobContainerName -SaveSnapshotInformation -AccountName $AzureAccountName -AccountKey $AzureAccountKey
	Write-Host  "Snapshot complete for $blobContainerName"
	}
}

$containers = Get-BlobContainer -AccountName $AzureAccountName -AccountKey $AzureAccountKey
foreach($container in $BlobContainers)
{
	SnapShotBlobContainer $containers $container
}

Then just call the script with the params. remember an array of items is parsed in like this:

-BlobContainers:@(‘container1’, ‘contaner2’) -AzureAccountName romikoTown -AzureAccountKey blahblahblahblahblehblooblowblab==