vCops remote DB access

UPDATE:

I still get hit on this blog post - So just for the record this blog post is obsolete! If you are running vCops I suggest you upgrade to vRops asap and then take a look at my blog post around the vRops API - vRops API consumed with Powershell

I posted this on twitter and postbin earlier this year, but now that I've got the site up and running, I thought I would write it here also. This started as a wish to get some of the data out of vCops in order to do a centralized capacity management. I started out looking for a solution and quickly found Clint Kitsons blog post on "Unofficial VMware vCenter Operations Powershell Module" I played around with it for sometime, but it wasn't working for me - After playing around with the script I finally found out why it wasn't working for me, basically the script executes a sql statement and it simply timed out. I'm guessing there is to much info in the vCops db. So from here on I started looking for a different solution and ended up with opening up for direct access to the vCops db.

If you like I can't get data out of the powershell module (It seems so cool - would love for it to work), you can always open up for remote db access like I did.

SSH to the Analytics VM and do the following

First we need to setup some security, to limit the security risk of exposing data to unwanted ip scopes. By default there is not open for remote access at all.

vi /data/pgsql/data/pg_hba.confAdd line:host    all         all         192.168.0.0/24          md5

Then you need to open the port used in the firewall

vi /usr/lib/vmware-vcops/user/conf/install/vcopsfirewall.confAdd lines:# postgresTCPPORTS="$TCPPORTS 5432"

Restart the firewall to get the rule loaded

   service vcopsfirewall restart

Finally restart the vCops db

   sudo -u postgres /opt/vmware/vpostgres/9.0/bin/pg_ctl -D /data/pgsql/data -m immediate restart

That's all :)

Well here's a few other tricks that might come in handy

List db users

su postgrespsql\du

Get the password for the alive user(Default vCops user)

su adminvcops-admin get_db_password

Create db user and assign "read only" rights

su postgrespsql alivevmCREATE USER USERNAME WITH PASSWORD 'PASSWORD';GRANT CONNECT ON DATABASE alivevm TO USERNAME;GRANT SELECT ON ALL TABLES IN SCHEMA public to USERNAME;

Remember when doing upgrades of vCops all of these settings might be overwritten, so you will probably have to apply them again.