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

 

Copyright © 2022 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

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

 

Copyright © 2022 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

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>



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...


Copyright © 2020 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Monday, 15 June 2020

Restore Azure SQL Database to different subscription without bacpac

Have you ever faced a scenario doing bacpac for a large database in order to move to another subscription, and waiting endlessly for this process to complete and thought why are't we able to just restore AzSQL Database. Well actually we can and it is well documented in Microsoft too under (Copy a transactionally consistent copy of a database in Azure SQL Database ) the problem is it is not under restore section so on basic google search it becomes bit tricky as the default bacpac option is the one you are going to see.

Summarising the process in the link, you can do this only using SSMS and not through Portal,You have to use a login that has the same name and password as the database owner of the source database on the source server. The login on the target server must also be a member of the dbmanager role, or be the server administrator login. Then execyte the below TSQL(Server name will be just the AZSQL Server name without database.windows.net


-- Execute on the master database of the target server (server2) to start copying from Server1 to Server2
CREATE DATABASE Database2 AS COPY OF server1.Database1;

Hope the article helps!

Tuesday, 17 December 2019

Last login date Power BI

Recently one of my client came up with a requirement of finding out the last login date of a Power BI user and wanted it scheduled as report, The details can be found in Power BI audit log or Azure AD user sign in info. I used the AzureADPreview Module cmdlets in PowerShell to get the required details
Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Friday, 11 October 2019

PowerShell - Audit / Extract Workspace User Access in Power BI

As part of security exercise I was asked extract all the users and their permissions in ouer Power BI environment, With introduction of PowerShell Module of Power BI and REST API's for Power BI auditing has become as easy as ever using PowerShell and API's.

In order to run the below Powershell script you first need to install MicrosoftPowerBIMgmt module in your powershell environment.

Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.