Setting Delete On Termination in Attached AWS Volumes

By default, any additional EBS volumes that you attach to an EC2 instance persist even after the instance terminates, unless Delete On Termination is set on each of the attached volumes. This AWS doc shows how to set it in the console and cli but not in PowerShell. 

However, the cli example uses "aws ec2 modify-instance-attribute" and you can find in the AWS PowerShell reference doc a cmdlet with similar name Edit-EC2InstanceAttribute. So here is how to set it with PowerShell.

First, you need to instantiate two classes InstanceBlockDeviceMappingSpecification and EbsInstanceBlockDeviceSpecification

$bdm = New-Object Amazon.EC2.Model.InstanceBlockDeviceMappingSpecification
$ebs = New-Object Amazon.EC2.Model.EbsInstanceBlockDeviceSpecification

Next assign values similar to the cli json format example

     

[
  {
    "DeviceName": "device_name",
    "Ebs": {
      "DeleteOnTermination": true
    }
  }
]

The equivalent in PowerShell is the following

     

$ebs.DeleteOnTermination = $true
$bdm.DeviceName = 'device_name'
$bdm.Ebs = $ebs

Finally, call the cmdlet

     

Edit-EC2InstanceAttribute -InstanceId 'id' -BlockDeviceMapping $bdm -Region 'region'

Search-AzGraph for All Subscriptions

A while ago I wrote about Search-AzGraph here. Since then, I have used it quite a bit and learned that depending on how you connect you may not query all subscriptions

Connecting Specifying a Subscription

To illustrate this behavior, let's say you have a service principal with access to 3 subscriptions, so to connect you would use the cmdlet Connect-AzAccount specifying the first subscription context

$paramAz = @{
    ServicePrincipal = $true
    TenantId         = 'tttttttt-tttt-tttt-tttt-tttttttttttt'
    Credential       = Get-Credential
    Subscription     = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
}
Connect-AzAccount @paramAz

Then you can query the resource count in all subscriptions with this Kusto query

$query = "
Resources
| summarize count() by subscriptionId
"

As expected, the cmdlet Search-AzGraph returned the resource count in all 3 subscriptions

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

However, if you change the context to the second subscription using Set-AzContext, you will see that now Search-AzGraph only shows the resource count for the second subscription

Set-AzContext -Subscription 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338

Moreover, if you change the context to the third subscription, you will see that Search-AzGraph shows the resource count for the second and third subscriptions

Set-AzContext -Subscription 'zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

The reason is that Search-AzGraph uses the cumulative context when a subscription context is set in the connection, Get-Context shows the two subscriptions Search-AzGraph will use

(Get-AzContext).Account.ExtendedProperties.Subscriptions

yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy,
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz

A workaround is to pass to Search-AzGraph a list of all subscriptions using Get-AzSubscription

$subIds = (Get-AzSubscription).Id

Search-AzGraph -Query $query -Subscription $subIds

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

Connecting Without Specifying a Subscription

In this scenario, you would use the cmdlet Connect-AzAccount without specifying a subscription

$paramAz = @{
    ServicePrincipal = $true
    TenantId         = 'tttttttt-tttt-tttt-tttt-tttttttttttt'
    Credential       = Get-Credential
}
Connect-AzAccount @paramAz

Next, you change the context to the second subscription using Set-AzContext, you will see that Search-AzGraph shows the resource count for all subscriptions, unlike our previous case

Set-AzContext -Subscription 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

Because a subscription context is not set in the connection, Get-Context will show all the subscriptions Search-AzGraph will use

(Get-AzContext).Account.ExtendedProperties.Subscriptions

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy,
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz

Conclusion

If you specify a subscription context in the connection, Search-AzGraph will not look in all subscriptions when you change the context later. You can override this behavior by passing a subscription list. On the other hand, if you do not specify the subscription context in the connection, Search-AzGraph will look in all subscriptions regardless of changing the context.

Nesting Invoke-SqlCmd in the Pipeline

If you need to collect data from an list of SQL Servers stored in a table, you would write a PowerShell script that uses Invoke-SqlCmd to query the list from the table and then loop through each instance to execute another query. However, PowerShell 5 does not support nesting Invoke-SqlCmd in the pipeline. The workaround is to use an intermediate variable or use PowerShell 7. 

Scenario 

For example you have a table with the SQL instance names stored in SQL1

create table instances(name varchar(255))
insert instances values ('SQL1')
insert instances values ('SQL2')
insert instances values ('SQL3')

To query the list, you use Invoke-SqlCmd in PowerShell 5

$query = "select name from instances"
Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query

To loop through each instance, you pipe the results and use Invoke-SqlCmd to execute another query to let's say get the version

Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

The script will fail with this error

# Invoke-Sqlcmd : The WriteObject and WriteError methods cannot be 
# called from outside the overrides of the BeginProcessing, ProcessRecord,     
# and EndProcessing methods, and they can only be called from within the same thread.
# Validate that the cmdlet makes these calls correctly, or contact Microsoft Customer 
# Support Services.

Workaround 

Use a variable $instances to hold the results of the first Invoke-SqlCmd and then pipe it

$instances = Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query 
$instances |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

or use PowerShell 7

Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

# instance     version
# --------     -------
# SQL1         13.0.5865.1
# SQL2         13.0.5865.1
# SQL3         13.0.5492.2

Not Defined as Subscriber Error

In SQL 2019, if you create a transnational push subscription to a database that participates in an Availability Group, using the subscriber name format <AGListener>, <port number> then it will fail with the error:

Msg 20032, Level 16, State 1, Procedure distribution.dbo.sp_MSadd_subscription, Line 175 [Batch Start Line 0]
'AGLISTENER,54321' is not defined as a Subscriber for 'SERVER1\INSTANCE1'.
Msg 14070, Level 16, State 1, Procedure sys.sp_MSrepl_changesubstatus, Line 1249 [Batch Start Line 0]
Could not update the distribution database subscription table. The subscription status could not be changed.
Msg 14057, Level 16, State 1, Procedure sys.sp_MSrepl_addsubscription_article, Line 384 [Batch Start Line 0]
The subscription could not be created.
Msg 20021, Level 16, State 1, Procedure sys.sp_MSrepl_addpushsubscription_agent, Line 258 [Batch Start Line 0]
The subscription could not be found.

Similar error will happen if you upgrade the remote distributor to SQL 2019. It is a bug fixed in CU5 released last week.

Assuming you have a replication environment like this

• Publisher: SERVER1/INSTANCE1
• Distributor: SERVER2/INSTANCE2
• Subscriber: AGLISTENER,54321

The script to reproduce the issue is:

-- Publisher
create database pub_test
go

use pub_test
create table test(a int primary key, b varchar(255))
go
exec sp_replicationdboption @dbname = N'pub_test', @optname = N'publish', @value = N'true'
exec sp_addpublication @publication = N'test', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true'
exec sp_addpublication_snapshot @publication = N'test'
exec sp_addarticle @publication = N'test', @article = N'test', @source_owner = N'dbo', @source_object = N'test'
go

-- Susbcriber
create database sub_test
go

-- Publisher

exec sp_addsubscription @publication = N'test', @subscriber = N'AGLISTENER,54321', @destination_db = N'sub_test', @subscription_type = N'Push'
exec sp_addpushsubscription_agent @publication = N'test', @subscriber = N'AGLISTENER,54321', @subscriber_db = N'sub_test'

Listing Azure Databases

If you are a data professional and need to list all the databases in your organization, across all subscriptions and resource providers, then you can easily accomplish this task by using Azure Resource Graph. We will start with writing the Kusto query in the Azure portal and later we will use PowerShell to execute the query programatically.

Azure Resource Graph Explorer

The easiest way to write a Kusto query is using the explorer. In the Azure portal search box, type Resource Graph Explorer and click on it. On the left pane search for sql, and you will see all the resource types that contain sql in the name. Click on resources, then click on the resource type microsoft.sql/servers/databases, both will show in the query editor in the right like this:

resources
 | where type == "microsoft.sql/servers/databases"

Next click on Run query and that's it, you wrote your first Kusto query!

In this example you will notice that master shows as a third database, you can filter it out by adding another where operator:

 | where name != "master"

It is a good practice to only show the columns needed (less data travel over the wire), you can select the columns with the project operator:

 | project type, name, kind, subscriptionId

Since the query will search in all subscriptions, you can create another query to find the subscriptions in your organizations using resourcecontainers of type microsoft.resources/subscriptions, and you will use the project operator to select what is needed such as subscriptionId and susbcriptionName:

 resourcecontainers
 | where type == "microsoft.resources/subscriptions"
 | project subscriptionId, subscriptionName = name

Now you can join both queries using the join operator with the column subscriptionId, and selecting again only the columns needed with the project operator:

resources
 | where type == "microsoft.sql/servers/databases"
 | where name != "master"
 | project type, name, kind, subscriptionId
 | join kind = inner (
   resourcecontainers
   | where type == "microsoft.resources/subscriptions"
   | project subscriptionId, subscriptionName = name
   ) on subscriptionId
 | project type, name, kind, subscriptionName

Azure PowerShell

Now that you have the Kusto query under your belt, you can call it from a PowerShell script using the cmdlet Search-AzGraph. But first you will need the Az modules, then connect to Azure:

Connect-AzAccount

Next you can run the same query from above. In addition, you can add other database resource types (MySQL and PostgreSQL) using the in operator

$query = '
resources
 | where type in (
    "microsoft.sql/servers/databases"
    ,"microsoft.sqlvirtualmachine/sqlvirtualmachines"
    ,"microsoft.sql/managedinstances"
    ,"microsoft.dbforpostgresql/servers"
    ,"microsoft.dbformysql/servers"
   )
 | where name != "master"
 | project type, name, kind, subscriptionId
 | join kind = inner (
   resourcecontainers
   | where type == "microsoft.resources/subscriptions"
   | project subscriptionId, subscriptionName = name
   ) on subscriptionId
 | project type, name, kind, subscriptionName
'
Search-AzGraph -Query $query

This was just a peek of the capabilities that Azure Resource Graph provides and hopefully it sparked your interest to learn more about it.

Using the .Net Oracle Data Access Client

There is a cmdlet for SQL Server databases called Invoke-Sqlcmd that will allow you to run TSQL commands but there is not a cmdlet for Oracle. This is where the .Net framework comes to the rescue.

Microsoft no longer provides the Oracle library. So we need to download it from the Oracle website link below

https://www.oracle.com/database/technologies/dotnet-odacmsi-vs2017-downloads.html

Then, we can use Add-Type to add the .Net class to a PowerShell session, and use a basic .Net framework syntax to execute database commands, as shown below.

Add-Type -Path 'C:\Program Files (x86)\Oracle Developer Tools for VS2017\odp.net\managed\common\Oracle.ManagedDataAccess.dll'

$conString = "User Id=myuser;Password=mypassword;Data Source=mydatabase"
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection
$con.ConnectionString = $conString
$con.Open()

$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand
$cmd.Connection = $con
$cmd.CommandText = 'select banner from v$version'
$rs = $cmd.ExecuteReader()

while ($rs.Read()) 
{
    Write-Host "$($rs.GetValue(0))"
}

$con.Close()
$con.Dispose()

Azure DevOps Build Queue Wait Time

Azure DevOps is used to automate CI/CD pipelines. It uses an agent to execute one job at a time. If we have more jobs to execute than agents then some jobs have to wait in the queue. This post is about how to gather metrics about the wait time to decide when to add more agents.

Agents are organized into pools and the data needed is available via REST API.  The URI to list the pools is

https://dev.azure.com/{your organization}/_apis/distributedtask/pools

and the URI to list the jobs running on each pool is

https://dev.azure.com/{your organization}/_apis/distributedtask/pools/{pool id}/jobrequest

To authenticate to Azure DevOps, you will need your access token and replace it in the PowerShell script below

$token = "{your access token}"
$bytes = [System.Text.Encoding]::UTF8.GetBytes(":$($token)")
$base64bytes = [System.Convert]::ToBase64String($bytes)
$headers = @{ "Authorization" = "Basic $base64bytes"}

$uri = 'https://dev.azure.com/{your organization}/_apis/distributedtask/pools/{pool id}/jobrequests'

$r = Invoke-RestMethod -Uri $uri -Headers $headers -Method Get -ContentType "application/json"

The result is a PSCustomObject with other nested PSCustomObject as Get-Member shows, so  expressions are needed to query the nested objects for example reservedAgent. The time fields such as queueTime are strings, so expressions are also required to convert to universal time

$r.value | gm

   TypeName: System.Management.Automation.PSCustomObject

Name                   MemberType   Definition
----                   ----------   ----------
Equals                 Method       bool Equals(System.Object obj)                     
GetHashCode            Method       int GetHashCode()                                  
GetType                Method       type GetType()                                     
ToString               Method       string ToString()                                  
agentDelays            NoteProperty Object[] agentDelays=System.Object[]               
assignTime             NoteProperty string assignTime=2019-07-19T21:40:26.8666667Z     
data                   NoteProperty System.Management.Automation.PSCustomObject
definition             NoteProperty System.Management.Automation.PSCustomObject
demands                NoteProperty Object[] demands=System.Object[]                   
hostId                 NoteProperty string hostId=22399273-0444-4322-aea9-2e628bac6c60 
jobId                  NoteProperty string jobId=12f1170f-54f2-53f3-20dd-22fc7dff55f9  
lockedUntil            NoteProperty string lockedUntil=2019-07-19T22:05:33.4833333Z    
matchesAllAgentsInPool NoteProperty bool matchesAllAgentsInPool=True                   
orchestrationId        NoteProperty string orchestrationId=d476f3d0-c2b4-41d5-9ebb
owner                  NoteProperty System.Management.Automation.PSCustomObject
planId                 NoteProperty string planId=d476f3d0-c2b4-41d5-9ebb-789eb9704ca7 
planType               NoteProperty string planType=Build                              
poolId                 NoteProperty int poolId=9                                       
queueTime              NoteProperty string queueTime=2019-07-19T21:40:26.4633333Z      
receiveTime            NoteProperty string receiveTime=2019-07-19T21:40:29.379468Z     
requestId              NoteProperty int requestId=1024                                 
reservedAgent          NoteProperty System.Management.Automation.PSCustomObject 
scopeId                NoteProperty string scopeId=0020fc51-3569-4cdc-8c2b-2affa5401996
serviceOwner           NoteProperty string serviceOwner=00025394-6065-48ca-87d

The final script parses the required info and filters out Pool Maintenance jobs and data older than 3 hours using Where-Object.

$r.value `
| Select-Object requestId, poolId, result,`
  @{Label=”name”; Expression={$_.definition.name}},`
  @{Label=”agent”; Expression={$_.reservedAgent.name}},`
  @{Label=”queued”; Expression={(get-date $_.queueTime).ToUniversalTime()}},` 
  @{Label=”assigned”; Expression={(get-date $_.assignTime).ToUniversalTime()}},` 
  @{Label=”received”; Expression={(get-date $_.receiveTime).ToUniversalTime()}},` 
  @{Label=”finished”; Expression={(get-date $_.finishTime).ToUniversalTime()}}`
| Where-Object {$_.name -ne 'PoolMaintenance' `
  -and $_.queued -gt (Get-Date).AddHours(-3).ToUniversalTime()}`
| Select-Object requestId, poolId, result, name, agent, queued,`
  @{Label=”wait”; Expression={$_.assigned-$_.queued}},`
  @{Label=”duration”; Expression={$_.finished-$_.received}}`
| Format-Table

requestId poolId result    name              agent   queued                wait     duration
--------- ------ ------    ----              -----   ------                ----     --------
     1023      9 succeeded project-for-demo2 AGENT01 7/19/2019 8:00:31 PM  00:33:40 00:00:25
     1022      9 succeeded project-for-demo1 AGENT01 7/19/2019 7:55:04 PM  00:00:00 00:38:03

From the results above, request id 1023 was waiting 33 minutes for request id 1022 to complete. If a 33 minute wait is not acceptable then a second agent can be added to process the job. Also this data can be collected and stored for trend analysis.