Script to retrieve vm info from esxi

[admssm]

Hi,

im trying to create a script, which would get basic (IP, MAC, Hostname) info from each vm running on each esxi. I need to run it in Linux. Since im new to this im not sure if i did all the steps correctly..:

I downloaded/installed:

VMware-vSphere-Perl-SDK-5.1.0-780721.x86_64.tar

this way i can execute command to get at least simple info:

esxcli --server x.x.x.x --username user --password passwd --formatter=csv network vm list >>/tmp/rrr.csv

however, this doesnt run on older esx versions...

Please let me know - correct me how to proceed with the script-commands

Thx

[stumpr]

What's the error?  How old is your ESXi version? 

[admssm]

My esxi is 4.0. I managed to find a pl script which gives me Hostname and Mac ,which i can use as "unique identifier" for further database join queries. Im running these commands on Centos 6.3.

Here are the commands:

./vmmac.pl --server x.x.x.1 --username user --password passwd >> /tmp/API/Esx_x.x.x..1.csv
./vmmac.pl --server x.x.x.2 --username user --password passwd >> /tmp/API/Esx_x.x.x..2.csv

etc...

What i cant figure out is how to put these commands into *.sh or into *.pl script, so instead of typing each commands separately, i would set the IP once, and scheduled it in cron. Can you please help me with that..? Attached is the mentioned pl script

Thank you.

[stumpr]

Wouldn't something like the following do the trick (simply):

#!/bin/bash

for server in 172.16.254.51 172.16.254.52

do

   ./vmmac.pl --server $server --username user --password passwd >> /tmp/API/Esx_$server.csv

done

You should use full path so your cron can find the vmmac.pl script.  Also, the VI SDK for Perl accepts a lot of environment variables for login, so you can set those and they'll be picked up automatically (check the documentation).

You could also just modify the script to connect to multiple servers.  I posted about using a LinkedMode API to connect to multiple vCenters at the same time, you can modify it to instead take a list of comma separated vCenter server addresses and connect to those.  You just need to build the service_url and call Util::connect($service_url).  Then clear the VIM global (examples in my code).

http://www.virtuin.com/2012/12/querying-vcenter-linked-mode-with.html

But if you just want a quick and simple solution, just wrap your commands in a cronjob like above.

[admssm]

Thank you for your reply.

The script im using retrieves Mac and hostname of each vm. However, when i wan to to export these data from each esxi, i need to run separate commands for each esxi, since from the output, i dont see which esxi the vm is from. How can i adjust the script, so it also output relevant esxi hostname for each vm..?

Thank you.

One more question..:

Im trying to do the clean install on 64bit Centos 6.3.

My assumptions:

The main package is VMware-vSphere-CLI-4.0.0-161974.x86_64.tar

What are the pre-requisites packages i should install..? I seem to have lot of XML and Perl errors when i want to execute .pl script.

Thank you

[stumpr]

Are your ESXi hosts in vCenter or stand-alone?

[admssm]

Stand alone in HP Blade center.

[stumpr]

Try the attached script.  I updated the one you had to loop through multiple ESX servers.  I changed your output slightly, I never liked packing data into CSV fields, gets clunky to automate so I broke the MAC addresses up one per line.  You can probably tweak the output if you want something different.

$ perl vmmac2.pl --username=root --password=****** --esxlist=172.16.254.101,172.16.254.102 >> out.csv

$ more out.csv

"VM Name","NIC Label","MAC Address","HostSystem"

"TestVM1","Network adapter 1","00:50:56:a6:11:47","172.16.254.101"

"TestVM1","Network adapter 2","00:50:56:a6:50:7a","172.16.254.101"

"TestVM2","Network adapter 1","00:50:56:a6:7a:bf","172.16.254.102"

You need to supply each ESXi host system IP address or hostname to the --esxlist parameter, comma-separated.  It will connect to each one in turn, and extract the VM names, devices.  It prints out the esxlist entity used, you could update the script to also get more HostSystem information using the ESXi's SDK interface.

If you were on vCenter, you'd have to do more data mapping for vms to hosts.

[admssm]

Thank you for your help.. i really do appreciate..

The main reason why the output is in csv, is that i need to get these data into MySql. For that i need to create another script, because to retrieve data and send them into db (without having it csv), in one script is waay over my league and i just cant ask someone to spend time on it..

I tried to install VMware-vSphere-CLI-4.0.0-161974.x86_64.tar on my CentOS 64bit. Seems to have problem with XML-LibXML. I tried from forum to run it from cpan cpan -i XML::LibXML::Document and follow the prerequisites from http://www.vmware.com/support/developer/viperltoolkit/viperl40/doc/vsperl_40_install.pdf:

Installed from yum:

OpenSSL

binutils

glibc

there i cant install from yum:

libxml2

perl-doc

liburi-perl

I guess some of them might be included in CLI package, but not sur ehow to get this fixed... Attached is the error. Please let me know your thoughts...

[stumpr]

Looks like it wants a compiler to build the module.  You probably don't have the development tools installed, so it can't compile the C libs that LibXML calls from Perl.  Give yum groupinstall 'Development Tools' a shot, then then retry the LibXML installation.

You'd be surprised how easy it is to insert the data into a database table with Perl's DBI module.  But you'll probably want to do a a multi-relationship table like the CSV output I showed you.  Or you could do a table with 10 NIC columns (current VM limit is 10 NICs, so you can have upto 10 MACs per VM).

I actually run into this pattern a lot with customers.  They export some amount of data from vCenter into a CSV format, then import it into another database (custom, another vendor CMDB, etc).  The challenge is when you run into trying to relate Hosts, Datastores, VMs, Networks, etc.  But if you're looking to just create a simple network table of VM content, I think it would only be few more lines of code to just drop it in your database directly.

[admssm]

Thank you for all your help, it saved me lots of time.

Im looking into way how to adjust the script to write discovered data from vm's directly into mysql. Most of the google results are talking only about importing created csv into mysql, not the direct import. Asuming from the results, do i have to first save csv and then import it, or can it be done directly using these variables for connection?:

use DBI;

use DBD::mysql;
my $dbh = DBI->connect("DBI:mysql:vmware","username","password",{RaiseError => 1, PrintError => 0});

Can you please help me to point me the right direction how to get the discovered data into mysql?

Thank you.

[stumpr]

You can probably just use the REPLACE function in MySQL to simplify an insert or update call.

Assuming your database structure looked like the following (and you've setup up a user named vmware and a database called 'test')

CREATE TABLE vms (

mac VARCHAR(18) PRIMARY KEY,

name VARCHAR(128),

label VARCHAR(18),

host VARCHAR(256)

);

Then you can connect and call REPLACE for each VM MAC entry.

# Connect to MySQL DB

my $dbh = DBI->connect("DBI:mysql:database=test;host=172.16.254.235;port=3306", "vmware", "vmware");

my $sql = q{REPLACE INTO vms (name,label,mac,host) VALUES (?,?,?,?)};

my $sth = $dbh->prepare($sql) || die "Failed prepare()";

foreach my $row (@csv) {

   # Insert values

   $sth->execute(@$row) || die "Failed execute()!";

}

I attached a new version of the script with the DBI commands instead of the CSV output.

$ perl vmmac3.pl --username=root --password=VMware1! --esxlist=172.16.254.101,172.16.254.102

select * from vms;

+-------------------+---------+-------------------+----------------+

| mac               | name    | label             | host           |

+-------------------+---------+-------------------+----------------+

| 00:50:56:a6:11:47 | TestVM1 | Network adapter 1 | 172.16.254.101 |

| 00:50:56:a6:50:7a | TestVM1 | Network adapter 2 | 172.16.254.101 |

| 00:50:56:a6:7a:bf | TestVM2 | Network adapter 1 | 172.16.254.102 |

+-------------------+---------+-------------------+----------------+

3 rows in set (0.00 sec)

Since MAC is your primary key here, you can just call the script anytime, it will update around the MAC address without extra logic for INSERT or UPDATE SQL commands.  You may need some extra logic to DELETE MACs if they get removed from VMs -- depends on your tool's intended use.

[admssm]

Thank you stumpr, your help is greatly appreciated. Script works like a charm.