This is following on from my recent http://virtualdevops.com/2017/03/22/using-createrestclient-in-vrealize-orchestrator-to-create-vrealize-automation-rest-calls/ blog as I often hear people are confused about ODATA filters and specifically, what filters are available to use in both Orchestrator and the vRealize Automation API
ODATA is a common feature used in API calls to apply filter queries to obtain a subset of information based on the query passed in to the REST API call. ODATA filters can be used in both vRA REST calls and vRO workflows. This blog explores a couple of different ways to interact with the vRA REST API and vRO workflows to help minimize the data set returned in the response. Before we look in more detail on how to construct these API calls, lets go through some basic ODATA expressions:
Select a range of values
filter=Entry_No gt 610 and Entry_No lt 615
And
filter=Country_Region_Code eq 'ES' and Payment_Terms_Code eq '14 DAYS'
Or
filter= Country_Region_Code eq 'ES' or Country_Region_Code eq 'US'
Less than
filter=Entry_No lt 610
endswith
filter=endswith(VAT_Bus_Posting_Group,'RT')
startswith
filter=startswith(Name, 'S')
These are some of the most common parameters used, but it doesn’t stop there.
Additionally, it is import to append the pagesize and limit to you ODATA filter to limit the dataset returned.
&page=1&limit=50
The page size is important when expecting a large dataset response. Without providing a pagesize or a limit size, the defaults will be used, which might mean the dataset does not include the information you are expecting.
It is also worth noting that the filter expressions are discussed in the API docs found on the vRealize Automation 7.1+ appliance. To access the docs, goto the following URL on a vRealize Automation 7.1+ appliance
https://{{vra-fqdn}}/component-registry/services/docs
With the above information in mind, let’s start looking at some basic ODATA filter expressions that can be used.
I am using POSTMAN as a REST client to interact with the vRA API.
Take this URI example:
https://vra-01.mylab.com/catalog-service/api/consumer/resources/
I only have a few catalog resources but see the following response:
"metadata": {
"size": 20,
"totalElements": 29",
"totalPages": 2,
"number": 2,
"offset": 20
}
This shows that there is a total of 29 elements in the response, 2 pages and a size of 20. This means that the JSON response has 2 pages and the 1st page list the first 20 elements. You would need to go to page 2 to get the other 9 elements. If using POSTMAN, you can see the HREF element that shows you what pagesize and limit defaults are being used.
“href”: https://vra-01.mylab.com/catalog-service/api/consumer/resources/?page=2&limit=20
By changing the pagesize and limit, you can control how many elements are returned, or you can search through each page to find the element that you want.
To limit the response to 40 items instead of the default 20 in this case
https://vra-01.mylab.com/catalog-service/api/consumer/resources/?limit=40
or to get to page 2,
https://vra-01.mylab.com/catalog-service/api/consumer/resources/?page=2
However, even though it is important to understand the pagesize and limit used, it is not an efficient way to query resources using the API. Many times, the query is set with a large limit and a pagesize of 1 to accommodate all resources, but it’s not an efficient way to find resources. What happens if you have 000’s of resources or you have more resources than what your limit is set to? Here is where you can use ODATA filters and use filter expressions to minimize the result set.
So how do we do this? One way is to read the API docs that come with vRealize Automation and it is important to read through how ODATA filters work.
A very simple example is to add a query expression that targets a particular name or ID. For example:
https://vra-01.mylab.com/catalog-service/api/consumer/resources/?
$filter=id eq '21da4606-ebfa-440e-9945-6ef9102c6292'
If you look at the metadata, you can see we have only one element returned:
"metadata": {
"size": 20,
"totalElements": 1,
"totalPages": 1,
"number": 1,
"offset": 0
}
This is indeed the element that has the ID equal to 21da4606-ebfa-440e-9945-6ef9102c6292.
Just breaking down the filter expression, here is how it worked:
$filter=id eq '21da4606-ebfa-440e-9945-6ef9102c6292'
The $filter is used and is set to equal an expression. In this case id eq ”
You can append the pagesize and limit with a filter. For example:
?page=1&limit=1&$filter=id eq ''
Notice the ? used to append a query to the URI, the & sign to have multiple filters and the $filter value which is the beginning of the ODATA filter query.
We can do additional searches too.
https://vra-01.mylab.com /catalog-service/api/consumer/resources/
?$filter=id eq '21da4606-ebfa-440e-9945-6ef9102c6292'
or id eq 'f89877f9-7c86-43e1-9956-9cefe22f127e'
Just checking the metadata, we can see the following
"metadata": {
"size": 20,
"totalElements": 2,
"totalPages": 1,
"number": 1,
"offset": 0
}
That matches our query as we see 2 elements returned.
If we supply an invalid ID, it simply doesn’t return an element.
This is all straight forward so far, so how do we do more complex ODATA filters? Well, there are a couple of ways to see how filters are used within vRealize Automation that will help you understand how to append ODATA filters to your API calls.
The first way is to look at the access_lot.txt log file that resides in /var/log/vcac/. This log file shows you all the API calls that are being made either by an end user or the vRealize Automation system. Looking at this log file can help you understand more complex ODATA filters.
You can simply use the UI and execute a search, which logs the ODATA filter used in the UI when searching in the access_log.txt . A good example of this is searching for catalogue items as we have a nice advanced search box that we can use to trigger a ODATA search.

Searching the access_log.txt log file, I can see the following API call which show how the search made via UI translates in to a more complex ODATA query.
/catalog-service/api/catalogItems/?$top=30&$skip=0&$orderby=name%20
asc&$filter=substringof%28%27docker%20-%20coreos%27%2Ctolower%28
name%29%29%20and%20substringof%28%27sample%27%2Ctolower%28
description%29%29%20and%20outputResourceType%2Fid%20eq%20%27
composition.resource.type.deployment%27%20and%20providerBinding%2F
provider%2Fid%20eq%20%275a4f1ed5-7a6c-4000-8263-5cbbcc888e84%27%20
and%20status%20eq%20%27PUBLISHED%27%20and%20organization%2F
tenant%20ne%20null
The URL has been encoded but we can simply decode this using an online decoder to give you the format that you require:
/catalog-service/api/catalogItems/?$top=30&$skip=0&$orderby=name
asc&$filter=substringof('docker - coreos',tolower(name)) and
substringof('sample',tolower(description)) and outputResourceType/id
eq 'composition.resource.type.deployment' and
providerBinding/provider/id eq '5a4f1ed5-7a6c-4000-8263-5cbbcc888e84'
and status eq 'PUBLISHED' and organization/tenant ne null
Some interesting filters have been used, specifically new filters we haven’t discussed, for example substringof(‘docker – coreos’,tolower(name)).
Substringof returns name records for catalog items with names containing the string ‘docker – coreos’
We could also use this :
catalog-service/api/catalogItems/?$filter=startswith(name,'Docker')
This returns 2 results as expected, as we have 2 OOTB catalogue items that have a name that starts with Docker.
You can also do precedence grouping which enables you to override the precedence conventions to manipulate your dataset based on the precedence grouping defined.
catalog-service/api/catalogItems/?$filter=substringof
('docker - coreos',tolower(name)) and
substringof('sample',tolower(description))
You can list all virtual machines that were created between 2 different dates:
/catalog-service/api/consumer/resources/?$filter=(dateCreated
gt '2017-01-01T00:0:00' and dateCreated le ‘17-01-02T00:0:00')
This will display all virtual machine created between ‘2017-01-01T00:0:00’ and ‘2017-01-02T00:0:00’
You can actually append a filter to this precedence and say all virtual machine that are active.
/catalog-service/api/consumer/resources/?$filter=(dateCreated
gt '2017-03-29T11:42:00' and dateCreated le '2017-03-29T11:50:00')
and status eq 'ACTIVE'
To be safe, you could make the active status to uppercase, just to ensure any parameters programmatically set are capitalized
catalog-service/api/consumer/resources/?$filter=(dateCreated gt
'2017-03-29T11:42:00' and dateCreated le '2017-03-29T11:50:00')
and status eq toupper('active')
Additionally, you can use the orderby function to sort your queries too.
orderby=dateSubmitted+desc
But how do we know what parameters can be passed in using the ODATA filter?
Well, this is a bit of trial and error. You can use the JSON response elements and using the key element and see whether you get results you are expecting that means the expression is working successfully. So take this JSON response payload:
{
"@type": "CatalogResource",
"id": "21da4606-ebfa-440e-9945-6ef9102c6292",
"iconId": "cafe_default_icon_genericCatalogItem",
"resourceTypeRef": {
"id": "composition.resource.type.deployment",
"label": "Deployment"
},
"name": "vm-app-01-88805337",
"description": null,
"status": "ACTIVE",
"catalogItem": {
"id": "8e04dceb-f040-4bbc-b312-8a1b0d0a3b95",
"label": "VM app 01"
},
"requestId": "3f49e42a-5e4e-4293-b3f0-eff2a34a5108",
"providerBinding": {
"bindingId": "3ee7fe9f-197c-4fac-8e59-cea6ac4f2336",
"providerRef": {
"id": "5a4f1ed5-7a6c-4000-8263-5cbbcc888e84",
"label": "Blueprint Service"
}
},
"owners": [
{
"tenantName": "vsphere.local",
"ref": "oleach@vmware.com",
"type": "USER",
"value": "Oliver Leach"
}
],
"organization": {
"tenantRef": "vsphere.local",
"tenantLabel": "vsphere.local",
"subtenantRef": "8723d842-d6f0-48c0-b0a3-e555eaeecdd5",
"subtenantLabel": "Corp"
},
"dateCreated": "2017-01-00T00:00:00.000Z",
"lastUpdated": "2017-01-00T00:00:00.000Z",
"hasLease": true,
"lease": {
"start": "2017-01-00T00:00:00.000Z"
},
"leaseForDisplay": null,
"hasCosts": true,
"totalCost": null,
"hasChildren": false,
"operations": null,
"forms": {
"catalogResourceInfoHidden": false,
"details": {
"type": "external",
"formId": "composition.deployment.details"
}
},
"resourceData": {
"entries": []
},
"destroyDate": null,
"pendingRequests": []
},
We can test setting the expression filter to use any of the above JSON payload elements, although there are ones that won’t work and other caveats you need to follow.
For example, take the request ID. This should look like ?$filter=requestId, however that doesn’t work. Trial and error lead me to use ?$filter=request as shown here:
catalog-service/api/consumer/resources/?$filter=request
eq '3f49e42a-5e4e-4293-b3f0-eff2a34a5108'
Additionally, in the vRA API docs, there are some caveats to be aware of. See this notes in the API docs.
Note: Notice how even though the providerRef object contains a label element, we use provider (and not providerRef) and name (and not label) to craft the filter:
$filter=provider/name eq 'theName'
"providerBinding": {
"bindingId": "cd1c0468-cc1a-404d-a2e0-a4e78fc06d4d",
"providerRef": {
"id": "2575e506-acfe-487a-b080-9898a30f519f",
"label": "XaaS"
}
},
Therefore, in order to get all providers that are of type XaaS, we need to run the following ODATA filter
/catalog-service/api/consumer/resources/?$filter=
providerBinding/provider/name eq ‘XaaS’
Notice provider is missing the Ref and the label is now the name. However, you can just use id too, for example:
catalog-service/api/consumer/resources/?$filter=
providerBinding/provider/id eq
'2575e506-acfe-487a-b080-9898a30f519f'
In order to get to an element that is an array, for example using the owner information in above JSON payload, you can simply do this:
catalog-service/api/consumer/resources?$filter=owners/ref
eq 'oleach@vmware.com'
You can also use firebug or chrome dev tools to see what API calls are being made via the UI and intercept the API call and look at the filters being used. For example, let’s redo the search we did above to see catalogue item entitlements and sniff the request using the firebug add-on in Firefox.
Here you can see the output of firebug:

The request URL will give you the UTF-8 encoded format and you can see also the ODATA filters used.
Additionally, you can also use ODATA filters in vRealize Orchestrator and these principles still apply.
Take this code example:
service = cafeHost.createCatalogClient().getCatalogConsumerResourceService();
var filter = new Array();
filter[0] = vCACCAFEFilterParam.substringOf("providerBinding/provider/id",
vCACCAFEFilterParam.string('2575e506-acfe-487a-b080-9898a30f519f'));
var query = vCACCAFEOdataQuery.query().addFilter(filter);
var odataRequest = new vCACCAFEPageOdataRequest(1, 10000, query);
resources = service.getResourcesList(odataRequest);
You can add you filter parameters in the same was as you can using the API ODATA filters.
To add additional parameters, you can build up the filter array with your queries.
Remember to consider the caveats I have been talking about above but by using the API JSON response, you can determine what filters to use. Please add comments if you find any caveats regarding ODATA filters not mentioned above.