Exaforge

Cloud, DevOps, Evangelism

Getting the AdapterKindKey & ResourceKindKey for every vCops Resource

Just a quick post today as I'm working on a secret project.  I had a need to identify the AdapterKindKey & ResourceKindKey for every resource in vCops.  Thats not as easy as it sounds, because the OpenHTTPAdapterAPI doesn't allow you to just list resource - you have to know what resource you want, along with its KindKeys before you can get its metric data.  As a result, you have to get the information from the SQL database directly.

Because these are in a database schema thats pretty solidly normalized, so we have to do a couple of SQL joins to get all the information back in one query.  In my case, I wanted the resource Name, ResourceKindKey and AdapterKindKey all together.  

The query ended up looking like this:

 

SELECT Name,RESKND_KEY,ADAPTER_KEY FROM AliveResource,ResourceKind,AdapterKind WHERE (AliveResource.RESKND_ID = ResourceKind.RESKND_ID AND AdapterKind.ADAPTER_KIND_ID = ResourceKind.ADAPTER_KIND_ID);

 

So how did I get to there.  First, I simply took a look at the AliveResource table to see what I got:

SELECT * FROM AliveResource

 

RESOURCE_ID RESKND_ID NAME INFORMATION CRED_ID ATTRPKG_ID COLLECTOR_ID MONITORING_INTERVAL LAST_MAINTAIN_TIME MAINTAIN_UNTIL maintenanceScheduleId TYPE Latitude Longitude CURRENT_HEALTH BUSSVC_ID TIER_ID SMPackageId CreationTime SUBTYPE EarlyWarningAlertEnabled DT_ENABLED FLAG

1 1 vCenter Operations Adapter (vCenter Operations Server) null null 2 1 5 null null null 7 null null 0 null null null 1324564710237 2 null 1 null

2 3 vCenter Operations Collector (vCenter Operations Server) null null 6 null 5 null null null null null null 0 null null null 1324564710294 2 0 1 null

 3 2 vCenter Operations Analytics null null 4 null 5 null null null null null null 0 null null null 1324564710343 2 0 1 null

Wow, thats a lot, but I see that I have alot of what I want, including a Name column and a RESKND_ID column, which would give me the ResourceKindKey (a string) from another table.  So lets limit the query a bit more:

 SELECT Name,RESKND_ID FROM AliveResource;

Name RESKND_ID

vCenter Operations Adapter (vCenter Operations Server) 1

vCenter Operations Analytics 2

vCenter Operations Collector (vCenter Operations Server) 3

 vCenter Operations Message Queue 4

 


OK, thats a little better and cleaner.  But now what I want to do is use the RESKND_ID (which is just a number) to get the ResourceKindKey for that ID from the ResourceKind table.  This is called a 'join', more specifically its an INNER JOIN.  For the math nerds out there like me, its a Cartesian product of the two tables.  So what I need to do is specify to the SQL engine which parts of the table are the same thing.  Specifically, that the AliveResource.RESKND_ID values and the ResourceKind.RESKND_ID are the same thing, and would should combine based on that.  The query looks like this:

SELECT * FROM AliveResource,ResourceKind WHERE AliveResource.RESKND_ID = ResourceKind.RESKND_ID

 

Name RESOURCE_ID RESKND_ID NAME INFORMATION CRED_ID ATTRPKG_ID COLLECTOR_ID MONITORING_INTERVAL LAST_MAINTAIN_TIME MAINTAIN_UNTIL maintenanceScheduleId TYPE Latitude Longitude CURRENT_HEALTH BUSSVC_ID TIER_ID SMPackageId CreationTime SUBTYPE EarlyWarningAlertEnabled DT_ENABLED FLAG RESKND_ID RESKND_KEY ADAPTER_KIND_ID NAME_ID TYPE SUBTYPE ICON SHOW_TAG ATTRPKG_ID SMPackageId DYNAMIC DT_ENABLED EarlyWarningAlertEnabled

vCenter Operations Adapter (vCenter Operations Server) 1 1 vCenter Operations Adapter (vCenter Operations Server) null null 2 1 5 null null null 7 null null 0 null null null 1324564710237 2 null 1 null 1 vCenter Operations Adapter Instance 1 10 7 null 1.png 0 null null null null null

vCenter Operations Collector (vCenter Operations Server) 2 3 vCenter Operations Collector (vCenter Operations Server) null null 6 null 5 null null null null null null 0 null null null 1324564710294 2 0 1 null 3 Collector 1 3 null null named_Collector.png 0 null null null null null

vCenter Operations Analytics 3 2 vCenter Operations Analytics null null 4 null 5 null null null null null null 0 null null null 1324564710343 2 0 1 null 2 Analytics 1 2 null null named_Analytics.png 0 null null null null null

vCenter Operations Web Server 4 5 vCenter Operations Web Server null null 10 null 5 null null null null null null 0 null null null 1324564710371 2 0 1 null 5 Web 1 5 null null named_Web.png 0 null null null null null

Whoa, thats a lot of data, so lets narrow it down by just selecting the columns I want:

SELECT Name,RESKND_KEY FROM AliveResource,ResourceKind WHERE AliveResource.RESKND_ID = ResourceKind.RESKND_ID

 

Name RESKND_KEY

vCenter Operations Analytics Analytics

DISK ARRAY_SUMMARY

LUN_EFDCACHEADMIN ARRAY_SUMMARY

LUN_FLARE ARRAY_SUMMARY

LUN_MLUADMIN ARRAY_SUMMARY

SP ARRAY_SUMMARY

VMware vCenter Operations BusinessService

Aha, thats a little better.  But I also wanted the AdapterKindKey too, and thats in a third table.  Well, another JOIN is certainly allowed (albeit computationally expensive, as we are talking about a 3D Cartesian product here).  However, I have fast computers and this is a pretty small database, so lets give it a show, this timeing joining ResourceKind.ADAPTER_KIND_ID and AdapterKind.ADAPTER_KIND_ID along with my previous join.  Also, we'll only select the columns we want to keep it clean:

SELECT Name,RESKND_KEY,ADAPTER_KEY FROM AliveResource,ResourceKind,AdapterKind WHERE (AliveResource.RESKND_ID = ResourceKind.RESKND_ID AND AdapterKind.ADAPTER_KIND_ID = ResourceKind.ADAPTER_KIND_ID);

 

Name RESKND_KEY ADAPTER_KEY

vCenter Operations Adapter (vCenter Operations Server) vCenter Operations Adapter Instance vCenter Operations Adapter

vCenter Operations Analytics Analytics vCenter Operations Adapter

vCenter Operations Collector (vCenter Operations Server) Collector vCenter Operations Adapter

vCenter Operations Message Queue MQ vCenter Operations Adapter

vCenter Operations Web Server Web vCenter Operations Adapter

Http Post (vCenter Operations Server) Http Post Adapter Instance Http Post

VMware vCenter Operations BusinessService Container

Et voila!  We have what we wanted!  Neato!