Getting the Full Path of a CMS folder from MSDB

Nice, even though I posted this at 12:25am CST on 8/1, blogger still gave me credit for a July post. It has been a busy month as we prepare for a DR exercise next month but I wanted to share this. We build CMS groups for different groups of servers that we need to run scripts against for the DR exercise: Availability Group servers on the production side who are primary, mirrored servers on the DR side, non-participating AGs, etc.

I was writing code to populate all these groups and found that when you query msdb to find the group name it only shows the name of that level and not the full path. I had to write a recursive CTE to get all the paths. This is the query I wrote:

USE msdb GO -- SELECT * FROM dbo.sysmanagement_shared_server_groups_internal AS sssgi; -- SELECT * FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]; WITH cte_groups (groupid, name, parentid, parentname, fullname) AS ( SELECT g1.server_group_id,     g1.name,     g1.parent_id,     CAST('' AS VARCHAR(100)) AS parentname,     CAST(g1.name AS VARCHAR(200)) AS fullname FROM dbo.sysmanagement_shared_server_groups_internal AS g1 WHERE g1.parent_id IS null UNION ALL SELECT g2.server_group_id,     g2.name,     g2.parent_id,     (gr.name AS VARCHAR(100)) AS parentname,     CAST( gr.fullname + '\' + g2.name AS VARCHAR(200)) AS fullname FROM dbo.sysmanagement_shared_server_groups_internal AS g2 INNER JOIN cte_groups AS gr ON gr.groupid = g2.parent_id ) SELECT * FROM cte_groups as cg

iiiiii

Connecting to vCenter from PowerShell

For a few years now I have been pulling data from the vCenter SQL Server database about my SQL Server virtual machines and storing that data into my own database.  After systems did the last upgrade, vCenter no longer stores the information in a SQL database. I asked them how to connect and they suggested that I use the PowerShell library that VMware provides. This is how I got it to work.

NOTE: I am only going to be using Get commands when I connect. You can change settings or start and stop VMs and even hosts from the tools if you have the proper permissions so know what a command does before you run it.  I don't believe you can use the -whatif parameter with any of the commands so be extra careful.

Installation

First I need to install the library, also known as VMware PowerCLI. I tried downloading the install package but it is an older version. After some web searching, I found that you can get it from PowerShell Gallery. 

You can get the most current pre-release version using this code. NOTE: I ran into some issues using pre-release versions.

Install-Module -Name VMware.PowerCLI

If you want to install a specific version, you can specify the version number. Check this PowerShell Gallery page for version numbers. I am going to install the current version listed on that page

Install-Module -Name VMware.PowerCLI -RequiredVersion 6.5.4.7155375

If you find you need to remove the module you can do so with this command. I had to use this to uninstall an older version and install a newer one

get-module -Name VMware.PowerCLI -ListAvailable | uninstall-module -force

Getting Connected

Now that I have it installed I want to see what I have available and then I will connect to a vCenter server. To list the modules that were installed use this command

get-module -ListAvailable |where-object {$_.name -like 'VMWare*'}

On my machine I get this list back.

You could then use Get-Command -Module [Module Name] to list out all the function and CmdLets for each module. Feel free to test that out but we are going to be using the VMware.VimAutomation.Core module today to get some information.

First we are going to connect to a vCenter server. We need to import the module first then we can connect (Remember to change the parameters for your environnment)

Import-Module -Name VMware.VimAutomation.Core Connect-VIServer "vCenterServer" -User "DFA_Primary\MYUSERNAME" -Password "MYPASSWORD"

In my environment I got the following error message:

Connect-VIServer : 6/25/2019 2:18:48 PM Connect-VIServer Error: Invalid server certificate. Use Set-PowerCLIConfiguration to set the value for the InvalidCertificateAction option to Prompt if you'd like 
to connect once or to add a permanent exception for this server.

This is because we use a self-signed certificate. You need to execute this command once to fix this:

Set-PowerCLIConfiguration -InvalidCertificateAction Ignore -Confirm:$false

After I executed this command, I was able to use the Connect-VIServer command.

Basic Commands

Now that we are connected we can use some basic commands to get inventory information. First I am going to list all of the hosts with some of their properties

Get-VMHost |select-object Name, ConnectionState, PowerState, NumCPU, MemoryTotalGB | Format-Table

If you want to see all the properties of just one host you can do it like this

#This gets basic info on a host Get-VMHost |where-object {$_.name -eq "astdc-esxi02.dimensional.com"} |select-object -Property *   #This will get you even more info but you have to supply a -VMHost paramater Get-VMHostAdvancedConfiguration -VMHost "astdc-esxi02.dimensional.com"

Now lets do the same thing for virtual machines. First we will list them 

Get-VM

Then we can dig in and see all the available properties of a particular virtual machine

Get-VM |where-object {$_.name -eq "VMNAME"} |select-object -Property *

These are just some basic commands to get started but they give me the data that I need to collect the inventory information I used to be able to collect directly from the old SQL Server database.