Compare two columns in Excel

 If you have two large lists that you need to compare (to identify what items are missing in one of the lists), this can be done in excel.

Add the first list (the bigger of the two) in column A and the other list in column B.

For column C, add this formula in cell C2 and drag it all the way down to the bottom column C:

=IF(COUNTIF($B:$B; A2)=0; "Item in column A is missing in column B"; "Item exists in both lists")

What it does is for each cell in column A, it will run through all the items in column B and look for a match and note if it finds one, see screenshot below.

An example xlsx file is available on GitHub (choose download raw file).

Create architecture diagrams with Mermaid and ChatGPT

 Mermaid is a practical tool that can create all sorts of diagrams as code. And if you combine it with an LLM, you can create it with a prompt.

To view the results, go to https://mermaid.live and add the code in the box on the left.

As an example, I had ChatGPT create a hybrid cloud architecture diagram with an on-prem location connected to Azure via an ExpressRoute. For encryption, VPN runs inside the ER circuit. The Azure setup is hub-spoke meaning the central networking components are in the hub and are connected to multiple spoke VNets. Traffic flows through an Azure Firewall.

I have put the code in a markdown file on Github (click Raw to view code).

Result looks like below:

If saving the mermaid code in a markdown file (.md) it will render the diagram directly in Github as long as it is wrapped with ```mermaid at the beginning and ``` at the end.

And if you copy raw code to mermaid.live, only use the text inside the wrapper.

Here's what it looks like at mermaid.live:

You can also preview the diagram in Visual Studio Code if you install the Markdown Preview Mermaid Support extension (and save file as .md). To view diagram, just right-click the .md file and choose "Open preview". It looks like this:

The native Mermaid file format extension is .mmd and there are extensions for VS Code for that as well.

Azure Bastion Developer - shared Bastion pool

 The Azure Bastion is useful for connecting securely to virtual machine without exposing the VMs to the internet using public IP's.

This however, requires a dedicated Bastion instance to be deployed and an AzureBastionSubnet.

With Bastion Developer, test/dev users can connect to the local IP of the VM via Bastion using a shared pool, so no dedicated setup required. The features are limited though. See here for more info.

To use, in the portal simply go to the VM -> Connect -> Bastion.

Add credentials and click Connect. This will open a remote session in the browser, see below:

Azure: Troubleshoot connectivity to a key vault with a private endpoint

 If you have a key vault that you can't reach there can be multiple reasons for this. Two of the main ones are DNS issues and firewall blocks.

This post will go over those two issues and show a couple of ways to test for connectivity.

When working in hybrid setups with an on-prem location connected to Azure either via VPN or ExpressRoute then it happens that you can create and see the key vault (this also goes for e.g. storage accounts and other PaaS services) but you get an error when trying to add a secret or other content to it. The error can mention e.g. "the connection to the data plane failed".

To troubleshoot, first we ensure that the local IP of the private endpoint can be resolved.

nslookup myown-keyvault.vault.azure.net

On Linux you can use the command dig to get slightly better lookup details than with nslookup:

dig myown-keyvault.vault.azure.net

This should resolve to a local IP. If it doesn't resolve at all or if it returns a public IP, there is something wrong with the DNS setup.

More info on troubleshooting DNS can be found here.

If that works, you can check for connectivity from your source. This can be done in a couple of ways and either of them is fine.

From Windows run:

tnc myown-keyvault.vault.azure.net -port 443

From Linux run (netcat and nc is same command):

nc -zv myown-keyvault.vault.azure.net 443

All data to a key vault goes over port 443, so if you have connectivity on that port and it can resolve the IP, then you should be good.

Alternatively try:

$(Invoke-WebRequest -UseBasicParsing -Uri https://myown-keyvault.vault.azure.net/healthstatus).Headers

Or from Linux:

curl -i https://myown-keyvault.vault.azure.net/healthstatus

There is more info on troubleshooting behind a firewall here.

Azure: Adding KQL queries as Resource Graph queries

 Using the Resource Graph Explorer can be helpful to retrieve information about your Azure environment using KQL. If you have certain queries that you run on a frequent basis and want to save, this can be done under Resource Graph queries.

If they are saved as 'shared' queries they can be viewed and run by others. Private queries can only be seen by you.

Resource Graph queries can be added via code and it is also supported by Azure Verified Modules.

The below screenshot shows what it looks like, when a few queries have been added:

If you click one of the queries, it will show the query itself in a new window:

And on the Results tab, you can run the query directly:

The Bicep AVM files are available on Github, see link.

The queries can be placed in any resource group.

Here are some examples of more queries from MS.

Retrieve custom DNS server settings on VNets using Kusto (KQL)

 You can use Kusto Query Language (KQL) to retrieve information about your Azure environment across subscriptions.

It's a fast tool and very useful in larger environments with many subscriptions and resources.

The following example shows how to list all VNets in the environment including their custom DNS server settings.

Go to Azure Portal -> Azure Resource Graph Explorer

In the query window, add the following KQL:

Resources

| where type == "microsoft.network/virtualnetworks"

| extend dnsServers = properties.dhcpOptions.dnsServers

| project id, name, dnsServers

Click: Run query

Result will look like below (custom DNS only set on one VNet):

You can download the content as a csv and import into xls for sorting and filtering.