New View API query services in PowerCLI 10.1.1: pulling event information without the sql password.

A while back I already posted about new services that where available for the View API’s in PowerCLI 10.1.1. Recently the api explorer for this version was published and can be found here. Two things that I didn’t find back then was the addition of two services for the query service. The first is GlobalApplicationEntitlementInfo this one can be compared to the previously already available GlobalEntitlementSummaryView and will return information about global entitlements.

The second added services is extremely useful: you can now query the event database. This means you don’t need the actual sql password anymore to query the events. According to the api explorer at least Horizon 7.3 is required and only events from the Event and Event_Data database tables. A simple query will show all events.

$queryservice=new-object VMware.Hv.QueryServiceservice
$defn=new-object VMware.Hv.QueryDefinition
$defn.queryEntityType = 'EventSummaryView'
$results=($queryservice.QueryService_Query($services1,$defn)).results
$results

As you can see the data is divided in data and namesdata properties, these contain the same data as what is returned with get-hvevent. I added some selections to show only one event.

$results | where {$_.data.eventtype -like "*BROKER_USERLOGGEDIN*"}  | select -last 1 | select -expandproperty data

and

$results | where {$_.data.eventtype -like "*BROKER_USERLOGGEDIN*"}  | select -last 1 | select -expandproperty namesdata

Offcourse it;s better to use filtering from the query directly. The full lust for that is available from the api explorer but I will give a couple of examples. (be aware that membername and the value are case sensitive)

$queryservice=new-object VMware.Hv.QueryServiceservice
$defn=new-object VMware.Hv.QueryDefinition
$defn.queryEntityType = 'EventSummaryView'
$equalsFilter = New-Object VMware.Hv.QueryFilterEquals
$equalsFilter.membername='data.eventType'
$equalsFilter.value="BROKER_USERLOGGEDIN"
$defn.filter=$equalsFilter
($queryservice.QueryService_Query($services1,$defn)).results.data | select -last 1

Or by severity

$queryservice=new-object VMware.Hv.QueryServiceservice
$defn=new-object VMware.Hv.QueryDefinition
$defn.queryEntityType = 'EventSummaryView'
$equalsFilter = New-Object VMware.Hv.QueryFilterEquals
$equalsFilter.membername='data.severity'
$equalsFilter.value="WARNING"
$defn.filter=$equalsFilter
($queryservice.QueryService_Query($services1,$defn)).results.data | select -last 1

As said it can be filtered on other properties as well but that might require some more logic to get the userid or desktopid for example. This is a very useful addition in my opinion to the Horizon View api’s.

Bookmark the permalink.

Comments are closed.