vcsa6.5 & postgres: search mac addresses

[abuzzi]

Hello,

can anyone provide any suggestion to extract from vcsa's postgres DB the vmname, esxi host from a provided mac address ?

Currently I'm able to extract VM name from vpx_nic/vpx_entity:

SELECT entity_id FROM vpx_nic WHERE mac_address='00:50:56:b4:3c:98'

ie: 1424

SELECT name FROM vpx_entity WHERE id='1424'

I know "dns_name, ip_address" are inside vpx_host but can't see how to get the proper information...

Thx!

A.

[Sreejesh_D]

I'm not quiet sure abt the sql query, but if you are looking for a way to get vm with specific MAC, you can get with following PowerCLI one liner . You can find more details of usage in Terence Luk: Finding a virtual machine in VMware vSphere by the MAC address .

Get-VM | Get-NetworkAdapter | Where {$_.MacAddress -eq “00:50:56:91:33:b4”}

[abuzzi]

Hello,

thank you for the powerCLI suggestion...

however we are creating a html frontend to allow user to derive the VM name and the host running it across multiple vcenters.

In the past (6.0) we had a single mssql serving all the vcenters and it was a simple query out of it.

Now (6.5) we have multiple unique VCSA with embedded postgres DB and the idea is to have a php script query'ing all of them to find the proper match.

(query'ing DB directly is much faster then checking all VM's information... and easily to handle from html frontend...)

So from mac address we should get back vmname, esxi host.

Thx!

A.

[daphnissov]

You really, really should stay away from regular querying of vpostgres. If you don't wish to use PowerShell bindings, grab the SDK and call directly to the API. This is not a complicated process to write with native REST. A kitten dies every time you try and query the VCDB directly.

[abuzzi]

Hello,

I just found my answer... google suggested the following link:

Listar direcciones MAC de maquinas virtuales en VMWare vCenter | Prova

and I just converted the SQL into:

select e.name, n.mac_address, v.ip_address,  n.network_name, h.dns_name

from vpx_vm v

inner join vpx_nic n on (v.id = n.entity_id)

inner join vpx_entity e on (v.id = e.id)

inner join vpx_host h on (v.host_id = h.id)

where n.mac_address='00:50:56:b4:3c:98'

and I get:

I think this resolve my issue.

Thx,

A.

[abuzzi · Accepted solution]

Hello,

in case anyone is looking for same result, here my "query_look-4-macaddress.php" implementation:

<?php
if (isset($_POST["mac"])) { $mac=$_POST["mac"]; } else { $mac="NULL"; }
$vcsa_all = array("***vcsa-ip1***"=>"***key1***", "***vcsa-ip2***"=>"***key2***", "***vcsa-ip3***"=>"***key3***", "***vcsa-ip4***"=>"***key4***");
foreach($vcsa_all as $vcsa => $password) {
   $dbconn = pg_connect("host=".$vcsa." dbname=VCDB user=vc password=".$password."")
      or die(json_encode(array("pg_error"=>pg_last_error())));
   $SQL = "SELECT e.name, h.dns_name, h.ip_address FROM vpx_vm v INNER JOIN vpx_nic n ON (v.id = n.entity_id) INNER JOIN vpx_entity e ON (v.id = e.id) INNER JOIN vpx_host h ON (v.host_id = h.id) WHERE n.mac_address='".$mac."'";
   $result = pg_query($SQL);
   $count = pg_num_rows($result);
   if ($count == 0)
     $final_array[$vcsa]=array("count"=>"0");
   else
     $final_array[$vcsa]=pg_fetch_row($result, 0, PGSQL_ASSOC);
}
echo json_encode($final_array);
?>

To allow postres access from specific host follow this guide:

http://blog.bertello.org/2017/12/connecting-to-embedded-postgresql-on-vcsa-6-5-to-unlock-nsx-control...

Thx,

A.