*Warming – This is most likely totally unsupported and not how the product is intended to be used!
I’ve been working on a couple projects with vCOps recently (NO SPOILERS YET!) and came across something that was totally new to me that I wanted to share. No internal secret sauce here either. I found this via KB 2011714.
FYI – I’m not an expert on licensing, but I assume only the highest level license unlocks this along with the custom portal.
There is a way to query the vCOps system with SQL syntax from a web browser so you do not have to muck with any database connectors, hacking anything, or potentially breaking something. Plug in the URL https://[vcops UI]/vcops-custom/dbAccessQuery.action and you are greeted with a screen like the below.
I was looking for what the exact syntax was for host memory so I went through the tables one by one with select * statements until I found output that looked like it was what I recognized. And BINGO! I found it via syntax like this (heh, i said like there…get it?)
select * from attributekey where attr_key like ‘%mem%’ and attr_key like ‘%host%’
The metric I was looking for is mem|host_usagePct
Excellent but what can I do with that now?
vCOps 5.8, the most recent version at the time of this writing, has had an API of sorts for many versions. I wouldn’t call it a REST API by any stretch but it does offer a way of getting data IN and OUT over HTTP. (This is nothing ground breaking here, people have been talking about this at VMworld and blogs for a while now…)
How do I get data in?
On your vCOps system you can plug this URL in to get a definition of the API: https://[vcops ui]/HttpPostAdapter/OpenAPIServlet
addGeneralMetricObservations
This interface is used to import metric data into vCenter Operations Manager, one resource at a time. This interface handles the creation of the resource and resource kind, as well as any new metric names. This is the default interface used when no “action” parameter is specified in the first line of body of the HTTP Post.
This is what we are looking for to add custom data. Note it says no “action” parameter is specified. Also note the unfamilar syntax for posting data. You only provide the below URL, a POST method and this in the body.
-
first line
resourceName,adapterKindKey,resourceKindKey,identifiers,resourceDescription,monitoringInterval,storeOnly, sourceAdapter, disableResourceCreation -
All subsequent lines
One metric per line with a comma-separated list of: metricName,alarmLevel,alarmMessage,date,value,thresholdHigh,thresholdLow
URL: https://[vcops ui]/HttpPostAdapter/OpenAPIServlet
METHOD: Post
BODY:
MyData,Http Post,MyResource,,MyDescription,,yes,
Metric|Name,0,NoValue,139780080800,42,
And the value is added. Note this is the time in MS not S. Originally I was doing work from vCO and found the time in there was seconds and not milliseconds as vCOps expects, and….I was confused. For a while.
Ok so you can add custom data so what…
The “So what” will be coming! Be patient!
How do I pull data out?
Back to the original point of the post in figuring out that host metric syntax. I learned from the API reference the lookupResource action:
lookupResource
Use this interface to find existing resources matching specified resource name, adapter kind and resource kind.
Resource name matching can use string compare or regular expression matching. To enable regular expression matching specify “regex:” prefix followed by the matching pattern.
Adapter kind and resource kind parameters are optional. If not specified only name will be used for matching.
Return value is one row per found resource:
resourceName=[name of resource]&adapterKindKey=[adapterKindKey]&resourceKindKey=[resourceKindKey]&identifiers=[identifiers]
To use this interface, the body of the HTTP POST request should contain a single line:
action=lookupResource&resourceName=[name of resource]&adapterKindKey=[adapterKindKey]&resourceKindKey=[resourceKindKey]
This is useful in short, in case I know one of the values like the name of the resource (my host name), I can find the rest of the values for adapterKindKey, resourceKindKey and Identifier. This all probably makes sense to someone but it’s way confusing to me. So here’s the shortcut:
Similar to the previous query, send this in the body. Ah ha!
BODY:
action=lookupResource&resourceName=my.esxi.host.fqdn
RESULT:
resourceName=esx-01.jaas.local&adapterKindKey=VMWARE&resourceKindKey=HostSystem&identifiers=VMEntityName::my.esxi.host.fqdn::false$$VMEntityObjectID::host-10$$VMEntityVCID::A1319CCE-D9AC-491A-A990-C8880CBEDE7C
Excellent! But…Why?
Now we know all the variables needed to pull data! If you check the API reference again you’ll find “ getMetricDataAndDT”
getMetricDataAndDT
Use this interface to get collected data for a resource and a metric.
Results will be a CSV List (Including the header):Time, Value, LowDT, HighDt, smooth
1365195172879, 37.5, 15.2, 73.3, 36.2 — with DT and Smooth values
1365195172879, 37.5, , , 36.2 — with no dt and smooth values
1365195172879, 37.5, , , — with no dt and no smooth
To use this interface, the body of the HTTP POST request should contain a single line:
action=getMetricDataAndDT&resourceName=[name of resource]&adapterKindKey=[adapterKindKey]&resourceKindKey=[resourceKind]&identifiers=[identifiers]&metricKey=[metrickey]&starttime=[startTime]&endtime=[endTime]&includeDt=[true|false]&includeSmooth=[true|false]
Now we know the resource name, adapter kind key, resource kind key, identifiers AND the metric key (which was the original point of this post!). I love when a plan comes together…
Combine all the bits, and stick a start time in there (or just something really small to return everything… probably not recommended):
action=getMetricDataAndDT&resourceName=my.esxi.host.fqdn&adapterKindKey=VMWARE&resourceKindKey=HostSystem&metricKey=mem|host_usagePct&starttime=-1&identifiers=VMEntityName::esx-01.jaas.local::false$$VMEntityObjectID::host-10$$VMEntityVCID::A1319CCE-D9AC-491A-A990-C8880CBEDE7C
And we have data!
Again.. The product is not really intended to be used this way, but all that data is in there. Let’s do something special with it!
Josh as a Service 