Pages

Showing posts with label Azure. Show all posts
Showing posts with label Azure. Show all posts

Monday, November 6, 2023

Finding Modules that Depend on Az.Accounts

I needed to use new functionality in the Az.Compute module so I updated it to the last version 

Update-Module Az.Compute

But then my script started failing with this error: "Method 'get_SerializationSettings' does not have an implementation." This article suggested to downgrade the version of the Az.Accounts module to 2.12.1. That got me thinking how many modules will be affected by the downgrade of Az.Accounts?

You can find the answer by finding the dependencies of the modules using Find-Module. For example for the Az.Compute the current version at the time of writing this post is 6.3.0 and it has a dependency on Az.Accounts 2.13.0

Find-Module -Name Az.Compute
 
Version Name       Repository Description                                                                           
------- ----       ---------- -----------                                                                           
6.3.0   Az.Compute PSGallery  Microsoft Azure PowerShell...
 
$r = Find-Module -Name Az.Compute
$r.Dependencies
 
Name           Value                                                                                                                                
----           -----                                                                                                                                
Name           Az.Accounts                                                                                                                          
MinimumVersion 2.13.0                                                                                                                               
CanonicalId    powershellget:Az.Accounts/2.13.0#https://www...  

To find all the modules that have a dependency on Az.Accounts 2.12.1 you can use the same cmdlet Find-Module to first get a list of all modules that start with Az, then for each module ($module) get all the versions, next for each module version ($module2), check each dependency ($dep) if it matches the name and version you are looking for (Az.Accounts and 2.12.1), then show the the respective info. The variable $found is used as short circuit flag to break the loop once a match is found 

$depName = 'Az.Accounts'
$depVersion = '2.12.1'
$modules = Find-Module -Name 'az.*' |
Where-Object {$_.Name -ne $refName} |
Sort-Object -Property Name
 
foreach ($module in $modules) {
    $modules2 = Find-Module -Name $module.Name -AllVersions
    $found = $false
    foreach ($module2 in $modules2){
        foreach ($dep in $module2.Dependencies){
            if ($dep.Name -eq $depName -and $dep.MinimumVersion -eq $depVersion){
                [pscustomobject]@{
                    ModuleName = $module2.Name
                    ModuleVersion = $module2.Version
                    DependencyName = $depName
                    DependencyVersion = $depVersion
                }
                $found = $true
                break
            }
            else {
                $found = $false
            }
        }
        if ($found) {
            break
        }
    }
}
 
ModuleName           ModuleVersion DependencyName DependencyVersion
----------           ------------- -------------- -----------------
Az.Aks               5.3.2         Az.Accounts    2.12.1          
Az.ArcResourceBridge 0.1.0         Az.Accounts    2.12.1          
Az.Batch             3.4.0         Az.Accounts    2.12.1          
Az.Billing           2.0.1         Az.Accounts    2.12.1          
Az.CognitiveServices 1.13.1        Az.Accounts    2.12.1          
Az.Compute           5.7.0         Az.Accounts    2.12.1          
Az.ContainerRegistry 3.0.3         Az.Accounts    2.12.1          
Az.CosmosDB          1.10.0        Az.Accounts    2.12.1          
Az.CostManagement    0.3.1         Az.Accounts    2.12.1          
Az.DataProtection    1.2.0         Az.Accounts    2.12.1          
Az.EventGrid         1.6.0         Az.Accounts    2.12.1          
Az.EventHub          3.2.3         Az.Accounts    2.12.1          
Az.Kusto             2.2.0         Az.Accounts    2.12.1          
Az.Network           5.6.0         Az.Accounts    2.12.1          
Az.Reservations      0.12.0        Az.Accounts    2.12.1          
Az.Resources         6.6.0         Az.Accounts    2.12.1          
Az.Search            0.9.0         Az.Accounts    2.12.1          
Az.ServiceBus        2.2.1         Az.Accounts    2.12.1          
Az.Sql               4.5.0         Az.Accounts    2.12.1          
Az.SqlVirtualMachine 1.1.1         Az.Accounts    2.12.1          
Az.Storage           5.5.0         Az.Accounts    2.12.1          
Az.Websites          2.14.0        Az.Accounts    2.12.1          
Az.Workloads         0.1.0         Az.Accounts    2.12.1      

From the output you can see that in our example to use Az.Accounts 2.12.1 you require Az.Compute 5.6.0 instead of 6.3.0. Fortunately, Az.Compute 5.6.0 still had the new functionality I was looking. Thus, reinstalling the correct versions fixed the issue.

Uninstall-Module Az.Accounts -RequiredVersion 2.13.0
Install-Module Az.Accounts -RequiredVersion 2.12.1
Uninstall-Module Az.Compute -RequiredVersion 6.3.0
Install-Module Az.Compute -RequiredVersion 5.6.0

Wednesday, September 8, 2021

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.

Tuesday, May 5, 2020

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.


Friday, July 26, 2019

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.

Friday, April 5, 2019

Analyzing SQL Error Logs with Kusto

We can leverage Azure Kusto to analyze SQL error logs. It is easy to learn if you know SQL querying, in fact here is a link on how SQL translates to Kusto. My favorite two operators to reduce large amount of errors to a small number of patterns are: autocluster and reduce

Autocluster

It will find a common pattern on the given columns. In the example below, 60% of the column values in host, source_name, and message, are from replication errors on a particular host



Reduce

It will find common patterns in the message and replace them with "*" to easily aggregate them. In the example below we can see the frequency of the most common errors.


Friday, March 15, 2019

Data Points Outside Time Range

I was using the cool Microsoft Azure plugin for Grafana to easily develop a dashboard for SQL error logs in Azure Kusto, and noticed the message "Data Points Outside Time Range" even though my data points were on range. After trial and error, I found out that if I add "asc" to the "order by" the message will disappear

Example of the original Kusto query where the message "Data Points Outside Time Range" appears



By adding "asc" to the "order by" the message will disappear


The reason is quite silly. I was learning Kusto from a TSQL background. In Kusto the "order by" defaults to descending order while in TSQL it defaults to ascending order.