Tuesday, 21 February 2023

Bug In Azure API creating Data Factory Default AutoResolveIntegrationRuntime on Managed Virtual Network

Recently our terraform Infrastructure as code effort to create a datafactory resource with default AutoResolveIntegrationRuntime in Managed Virtual Network failed to create one.

We had managed_virtual_network_enabled flag on terraform and tried to use native azure cli too as a backup but both failed to create the default integration runtime on Managed Virtual Network and created a default public integration runtime. Only work around it was to create a Data Factory using ARM Template. I exported the ARM template and created the below Powershell to create the Data factory. I stored the Template and parameter file on a Fileshare and used it in the powershell to reuse and create a parameter file each time for different DF creation.

Powershell Azure Devops REST API to create/update a variable group based on json variable input

I had a requirement to automate creation and updating of Azure devops variable group based on a configuration variable json file proved to me by the application team for CICD process, the below script will look for the variable group if it exists with the project mentioned if not it will create a new variable group, If the variable group exists it will update the variable group with json provided. The idea is to maintain the json file on git for version and see the history of the changes on the variable group variables provided to the pipeline.

The below is the format of the Variable json passed on to the script

Thursday, 3 November 2022

Work with Azure SQL with Python import result to pandas dataframe

I am going start a series of blogs working with python on Azure SQL, what we can do and limitations, In this first blog we look look into connecting to a database and importing a sql result to pandas dataframe. In the below example we can see we have imported the data and printed the last 10 rows and I have attached the screen shot of the result. In the future blogs we can se how we can work in pandas with the data.

Wednesday, 9 February 2022

Azure Windows VM Disk Usage report for All VM in a Azure Tenant

I was setting up monitoring for my Azure environment and I had an requirement to email the VM Disk usage of all windows VM's in a tenant to Support team on daily Basis and also sent notification in Slack. I used PowerShell to consolidate the data and Logic apps for the integration for Slack notification.

To breakdown the script for better understanding, I have used a credential from credential manager which had RBAC permissions to all subscriptions in the tenant and I used the function in https://gallery.technet.microsoft.com/scriptcenter/Accessing-Windows-7210ae91 for my Azure and SFTP authentication using the credentials already in credential manager. I am using Invoke-AzVMRunCommand which basically runs a PowerShell script file on the remote server. The below PowerShell script will run C:\temp\GetDisk.ps1 on all the remote servers I am passing.

GetDisk.ps1 runs the below PowerShell commandlet

"Get-Volume|where {$_.FileSystemLabel -ne 'System Reserved' -and $_.FileSystem -eq 'NTFS'}|SELECT DriveLetter,Size,SizeRemaining|ConvertTo-Json"

Invoke-AzVMRunCommand Will return only as single string irrespective of output format so I have converted into json so I can easily convert to PSObject for processing in my main script.

The script will consolidate result in HTML format and Json format. Json format will be sent as Payload to Webhooks to Logic Apps were you can integrate with Slack or Teams alerts. HTML format will be styled with yellow rows for 10 to 15% free space and Red Rows for usage less than 10% free space as below.

VM Disk Usage

VMName	SubscriptionName	RGName	DriveLetter	Size(GB)	SizeRemaining(GB)	UsedSpace(GB)	FreeSpacePercentage
Server1	Sub-1	RG-1	C	512	223	289	43.49978
Server1	Sub-1	RG-1	D	32	30	2	92.29322
Server1	Sub-1	RG-1	F	2048	381	1667	18.60302
Server2	Sub-2	RG-2	C	127	19	108	14.84652
Server2	Sub-2	RG-2	F	512	27	485	5.293138

Azure File Share Usage Report for all Subscription in your Tenant (Email and Web hooks notification)

I was setting up monitoring for my Azure environment and I had an requirement to email the Fileshare usage of all fileshares in a tenant to Support team on daily Basis and also sent notification in Slack. I used PowerShell to consolidate the data and Logic apps for the integration for Slack notification.

To breakdown the script for better understanding, I have used a credential from credential manager which had RBAC permissions to all subscriptions in the tenant and I used the function in https://gallery.technet.microsoft.com/scriptcenter/Accessing-Windows-7210ae91 for my Azure and SFTP authentication using the credentials already in credential manager. I am using Azure Management API's to get the information of the File storage as I found PowerShell Modules had restriction to Storage account opened only for particular subnet outside of the server I am running the script. I have used the method in https://www.powershellgallery.com/packages/LSECosmos/0.2.1-alpha/Content/Get-AzAccessToken.ps1 for API Authentication.

The script will consolidate result in HTML format and Json format. Json format will be sent as Payload to Webhooks to Logic Apps were you can integrate with Slack or Teams alerts. HTML format will be styled with yellow rows for 80 to 90% and Red Rows for usage above 90% as below.

File Share Usage

StorageAccountName	SubscriptionName	ResourceGroupName	ShareName	TotalCapacity(GB)	UsedSpace(GB)	PercentageUsed
StgAcctName1	Sub-1	RG-1	FileShare-1	1	0	0
StgAcctName2	Sub-1	RG-2	FileShare-2	10	0	2
StgAcctName3	Sub-2	RG-3	FileShare-3	100	0	0
StgAcctName4	Sub-3	RG-4	FileShare-4	1024	926	90
StgAcctName5	Sub-4	RG-5	FileShare-5	250	91	37
StgAcctName6	Sub-4	RG-5	FileShare-6	1024	845	83

Wednesday, 2 June 2021

Recover Deleted Azure Database using LTR backups

I have had many queries in past where there was scenario where PROD Azure DB has to be restored which mean a new DB is created in Azure DB and users were not sure if they will loose the LTR backups of the old databases if deleted and kept paying for an unused DB.

Most of the confusion comes when you delete the database in Azure option to access LTR backups of the deleted DB though portal goes away which makes the users think the backups are also deleted permanently. This is not true even though you have deleted the DB the LTR backups are always retained according to your retention policy. It is only not accessible though Poral, You can use Powershell or AzureCLI to access it.

use the below Powershell it will return all the LTR backups on your location,

Get-AzSqlDatabaseLongTermRetentionBackup -Location <yourAzDClocation>

https://docs.microsoft.com/en-us/powershell/module/az.sql/get-azsqldatabaselongtermretentionbackup?view=azps-6.0.0

You can use the filer at SQL instance level, DB level and Resource group level too. So don't be afraid of losing your LTR backup when you delete the DB.

But PiTR backups is a different story, by default the backup can be recovered within 7 days(unless you have set PiTR backups to one day) from deleted DB folder in portal or using Powershell ( https://docs.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups#deleted-database-restore ) After which you will loose the DB if there are not LTR backups so be careful before deleting Azure databases with no LTR backups.

Wednesday, 30 December 2020

Powershell - Set Azure SQL Firewall for all Azure SQL Service Tag IP in a Region

With one of my client we had to create a external data source between 2 Azure DB's but we were not happy to set Allow Azure services option to enable connectivity for 2 Azure DB's and open up Firewall to DB from all Azure resources so we went through the option of setting Allow Azure Services Off and whitelisting SQL ServiceTag IP's for Azure region where our DB's were hosted, Below is the script i created to accomplish the task and WestEurope is the region I used...

Data Technet