Device Datastore per Host from VCDB

[dloop2]

This may not be the right place to post this, I'm trying to rewrite some code that is in powershell using the vi-toolkit, to instead be in powershell going directly to the VCDB. In case you are wondering why, this is part of a larger script that runs too slowly. This is the last piece to convert to go to the DB directly.

The information I am after is what Virtual Center displays in the "Configuration ...Storage Panel". In particular the "IDENTIFICATION" column and the matching "DEVICE" (vmhba) column.

Here is a powershell script that gathers this information:

function FillDatastoreLookupTable () {
	Write-Host "Function: FillDatastoreLookupTable"
	foreach ($h in $HostTable.keys) {
		$hss = Get-View (Get-View (Get-VMHost -Name $h).ID).ConfigManager.StorageSystem
		foreach($mount in $hss.FileSystemVolumeInfo.MountInfo){
  			if($mount.Volume.Type -eq "VMFS"){
    			foreach($ext in $mount.Volume.Extent){
      				foreach($lun in $hss.StorageDeviceInfo.MultipathInfo.Lun){
	   		 			if($lun.Id -eq $ext.DiskName) {break}
	  				}
					
					Write-Host $h $ext.DiskName $mount.Volume.Name
				}
  			}
		}
	}
}

I've come close to duplicating this going directly to the VCDB. The only place I can find the VMHBA infromation stored with the Datastore identification is in the XML text contained with the INFO column of the VPX_Datastore table. If this cross-reference of information VMHBA to Datastore is located somewhere else that would be VERY HELPFUL to know.

Here is the code to extract it out of the INFO column:

function FillDatastoreLookupTable () {
	Write-Host "Function: FillDatastoreLookupTable"
	$SqlCmd 					= New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText 		= "SELECT name, Info FROM dbo.VPX_DATASTORE where type = 'vmfs'"	 
	$SqlCmd.Connection 			= $SqlConnection
	$SqlAdapter 				= New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand 	= $SqlCmd
	$DataSet 					= New-Object System.Data.DataSet
	$SqlAdapter.Fill($DataSet)  | out-null
	$dsinfo = $DataSet.Tables[0] 
	
	foreach ($row in $dsinfo) {
		$doc = [xml] ("<root xmlns:xsi='urn:dummy'>" + $row.info + "</root>")
		($doc.root.obj.vmfs) | % {
			$_.extent | % {
				$DatastoreLookupTable.add($_.diskname, $row.name)  | Out-Null
			}
		}
	}
}

Any suggestions would be appreciated.

[LucD]

Afaik that is the only table in the VCDB where you can find that information.

For completeness you could add the partition number to have exactly the same as what you see in the VIC.

...
		($doc.root.obj.vmfs) | % {
			$_.extent | % {
				$DatastoreLookupTable.add(($_.diskname + ":" + $_.partition), $row.name)  | Out-Null
			}
		}
	}
}

Beware that this is undocumented and could probably change with any patch or upgrade you install on the VC!

Out of curiosity what is the difference in execution time you see between script 1 and script 2, that goes directly to the VCDB ?

[dloop2]

Thanks Luc,

Overall performance between the two is dramatic. For this one function alone it is not enough to brag about, but this is part of a larger script and the total time difference can be several tens of minutes.

So for the first script, is the vi-toolkit getting this information from each host directly, not from the vcdb? The problem with script #2 is that for some reason it does not contain all the device entries that script #1 provides. Some are missing.

dave