WilliamLam.com

Quick Tip - Microsoft Power Automate expression to access specific row from Excel workbook

by // Leave a Comment

Microsoft Power Automate is a pretty nifty service that allow users to automate various business processes using Microsoft Office 365 as well as supporting other 3rd party integrations. For a particular project I have been working on, I have been trying to figure out the best way to access a specific row of information from an Excel workbook such as the example below.


For anyone that does not have a scripting or programming background, Power Automate is relatively easy to use for basic process automation. However, if you have some scripting background, then the tool can feel a bit clunky, especially since each action must be manually created, one at a time and there are no bulk operations or even the ability to quickly duplicate an action for editing.

For my initial prototype, I had used multiple Excel Get Row action to fetch a specific row from my Excel workbook to be able to reference it later in my automation. Not only was this not optimal but I also ran into a number of connection issues since I had up to 40 of these actions. I had searched online and various Power Automate forums for better solution which would allow me to read the Excel data once and then reference it when I needed. From what I could find, most examples focused on a specific row within the document or required looping through the table which did not help me since I needed to be able to access a particular row on-demand.

[Read more...]

Exploring the new vSphere with Tanzu VM Service with Nested ESXi

by // Leave a Comment

After upgrading my homelab to the latest vSphere 7.0 Update 2a, I was looking forward to kicking the tires on the highly anticipated vSphere with Tanzu Virtual Machine Service capability. Both Oren Penso and Myles Gray have both done a fantastic job on their respective blogs here and here demo'ing the new VM Service.

While browsing through Oren's Github repo since I came across his blog post first, a couple of things quickly caught my attention. The first was a reference to OvfEnv transport with the YAML manifests and the second was that he was able to deploy an Ubuntu VM, which is interesting since only CentOS is currently officially supported. Why was this interesting? Well, with these two pieces of information, I had a pretty good theory on how the guest customizations were being passed into the GuestOS for configuration and this gave me an idea 🤔

I decided to put my hypothesis to the test and try out the VM Service and deploy one of my Nested ESXi Virtual Appliance and as you can see from the tweet below, it worked! 🤯

Disclaimer: vSphere with Tanzu and the VM Service currently only officially supports CentOS images for deployment, other operating systems are currently not supported. This is primarily for educational and experimentation purposes only.

[Read more...]

Why does Deploy OVF Template operation show vpxd-extension-[uuid]?

by // Leave a Comment

A question that I had received awhile ago from a customer was how to identify the specific user(s) who have deployed an OVF/OVA? Customers can easily do this by leveraging vCenter Serve Events, which are extremely rich with information that can help answer this and many other questions you might have in your vSphere enviornment.


The first challenge that you will find is that an OVF/OVA import operation is mapped to a generic vCenter TaskEvent, which will require some additional filtering. Secondly, depending on the method that was used to deploy the OVF/OVA such as the vSphere UI or using Automation tools like OVFTool or PowerCLI, you will also slightly diffrent behaviors in terms of the vCenter Server Events that are emitted.

If you deploy an OVF/OVA using the vSphere UI, you may have noticed there are actually two vCenter tasks which are displayed and running simultaneously as shown in the screenshot above. The first task is called "Import OVF package" and you will see that this task is associated with the actual user who initiated the import. The second task called "Deploy OVF template" is then associated with a vCenter system account that handles the actual deployment which will show up with a vpxd-extension-[uuid] username. This occurs because the user who is performing the import is not interacting directly with with vCenter Server, but rather through the vSphere UI which uses a system account to then communicate the operation to vCenter Server.

For this reason, when an OVF/OVA is imported through the vSphere UI, you will need to look at the TaskEvent and filter for the initial import operation. If an OVF/OVA is imported using the vSphere API using something like OVFTool, PowerCLI or any other vSphere SDK, then you will only see the Deploy OVF Template operation and the user associated with that operation is the person who initiated the import.

Using PowerCLI and the Get-VIEvent cmdlet, we can easily filter out these two types of TaskEvents.

Get-VIEvent | where {$_.GetType().Name -eq "TaskEvent" -and $_.FullFormattedMessage -eq "Task: Import OVF package"}

Get-VIEvent | where {$_.GetType().Name -eq "TaskEvent" -and $_.FullFormattedMessage -eq "Task: Import OVF package"}