OData Queries Using the Service Management Automation Web Service
Requests to the Service Management Automation web service adhere to the OData standard. By understanding how to construct an OData query, you can retrieve different sets of data that you require. Basic information is provided here to get you started working with the Service Management Automation web service. Detailed information on OData and forming requests to return data are available at OData.org.
Note
The current version of Service Management Automation uses a GUID value of 0 (00000000-0000-0000-0000-000000000000) as the tenant identifier for all SMA requests.
Inspecting data from the Service Management Automation Web Service in Internet Explorer
You can inspect the data available from the Service Management Automation web service by typing appropriate URL strings directly into a browser and then inspect the XML of the data that is returned. By default, Internet Explorer will not display all data returned from an OData service. In order to inspect all of the available data, you must first configure Internet Explorer to expose all of the available data.
To configure Internet Explorer expose all OData data
In Internet Explorer, select Internet Options, and then Content.
Click Settings.
Unselect Turn on feed reading view.
Click OK and then OK.
Service Management Automation Data Model Information
You can obtain detailed information about the Service Management Automation data model by querying for the SMA Web service metadata file.
The following URL will return the SMA Web Service Data Model:
HTTPS://<HOST>:<PORT>/00000000-0000-0000-0000-000000000000/$metadata
[A partial snippet of the metadata (EDMX) file.]
<?xml version="1.0" encoding="UTF-8"?>
-<edmx:Edmx xmlns:edmx="https://schemas.microsoft.com/ado/2007/06/edmx" Version="1.0">
-<edmx:DataServices xmlns:m="https://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:MaxDataServiceVersion="3.0" m:DataServiceVersion="3.0">
-<Schema xmlns="https://schemas.microsoft.com/ado/2009/11/edm" Namespace="Orchestrator.ResourceModel">
-<EntityType m:HasStream="true" Name="Job">
-<Key>
<PropertyRef Name="JobID"/>
</Key>
<Property Name="JobID" Nullable="false" Type="Edm.Guid"/>
<Property Name="JobContextID" Nullable="false" Type="Edm.Guid"/>
<Property Name="TenantID" Nullable="false" Type="Edm.Guid"/>
<Property Name="JobStatus" Nullable="false" Type="Edm.String" Unicode="true" FixedLength="false" MaxLength="32"/>
<Property Name="StartTime" Type="Edm.DateTime"/>
<Property Name="EndTime" Type="Edm.DateTime"/>
<Property Name="CreationTime" Nullable="false" Type="Edm.DateTime"/>
<Property Name="LastModifiedTime" Nullable="false" Type="Edm.DateTime"/>
<Property Name="ErrorCount" Type="Edm.Int16"/>
<Property Name="WarningCount" Type="Edm.Int16"/>
<Property Name="JobException" Type="Edm.String" Unicode="true" FixedLength="false" MaxLength="Max"/>
<NavigationProperty Name="JobContext" FromRole="Job" ToRole="JobContext" Relationship="Orchestrator.ResourceModel.FK_Job_JobContext"/>
</EntityType>
-<EntityType Name="Runbook">
-<Key>
<PropertyRef Name="RunbookID"/>
</Key>
<Property Name="TenantID" Nullable="false" Type="Edm.Guid"/>
<Property Name="RunbookID" Nullable="false" Type="Edm.Guid"/>
<Property Name="RunbookName" Nullable="false" Type="Edm.String" Unicode="true" FixedLength="false" MaxLength="64"/>
<Property Name="CreationTime" Nullable="false" Type="Edm.DateTime"/>
<Property Name="LastModifiedTime" Nullable="false" Type="Edm.DateTime"/>
<Property Name="LastModifiedBy" Type="Edm.String" Unicode="true" FixedLength="false" MaxLength="512"/>
<Property Name="Description" Type="Edm.String" Unicode="true" FixedLength="false" MaxLength="512"/>
<Property Name="IsApiOnly" Nullable="false" Type="Edm.Boolean"/>
<Property Name="IsGlobal" Nullable="false" Type="Edm.Boolean"/>
<Property Name="PublishedRunbookVersionID" Type="Edm.Guid"/>
<Property Name="DraftRunbookVersionID" Type="Edm.Guid"/>
<Property Name="Tags" Type="Edm.String" Unicode="true" FixedLength="false" MaxLength="512"/>
<Property Name="LogDebug" Nullable="false" Type="Edm.Boolean"/>
<Property Name="LogVerbose" Nullable="false" Type="Edm.Boolean"/>
<Property Name="LogProgress" Nullable="false" Type="Edm.Boolean"/>
<NavigationProperty Name="Statistics" FromRole="Runbook" ToRole="Statistics" Relationship="Orchestrator.ResourceModel.FK_Runbook_Statistics"/>
<NavigationProperty Name="DraftRunbookVersion" FromRole="Runbook" ToRole="RunbookVersion" Relationship="Orchestrator.ResourceModel.FK_Runbook_DraftRunbookVersion"/>
<NavigationProperty Name="PublishedRunbookVersion" FromRole="Runbook" ToRole="RunbookVersion" Relationship="Orchestrator.ResourceModel.FK_Runbook_PublishedRunbookVersion"/>
<NavigationProperty Name="Schedules" FromRole="Runbook" ToRole="Schedule" Relationship="Orchestrator.ResourceModel.FK_Schedule_Runbook"/>
</EntityType>
Etc …
Query Syntax
OData queries have three sections that use the following syntax. Details on each section of the request are provided below:
[Service Root URI]/[Resource Path]?[Query Options]
Service Root URI
The service root URI identifies the root of the OData service. This includes the protocol to use, the path to the computer hosting the service, and the port number that it is listening on. A typical service root URI for the Service Management Automation web service looks like the following. This example uses https to connect to the web service on port 9090 on a computer called server01.contso.com.
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/
The service root URI is required in all OData queries. If you provide just the service root URI without specifying a resource path, then you will get a listing of the different entities available from the service.
Resource Path
The resource path defines the resource that you want to work with. This can be a single resource such as a runbook, or it can be a collection of resources such as all runbooks or a collection of jobs related to a particular runbook. Providing the name of a collection alone in the resource path will return all instances of that resource.
For example, to return all runbooks from a server called server01.contoso.com, you could use the following URL:
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks
Important
Collection names in a URL are case-sensitive. In the above example, Runbooks returns all runbook, but runbooks would result in an error.
You can return a single member of a collection by specifying its key property enclosed in parentheses. For example to return all properties of a single runbook with the GUID ab072e85-0ab6-42b5-97dd-239a5a50ebbd, you can use the following URL:
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks(guid'ab072e85-0ab6-42b5-97dd-239a5a50ebbd')
You can return a single property from a single member of a collection by specifying the name of the property at the end of the URL for the specific member. For example to return just the name property of a single runbook with the GUID ab072e85-0ab6-42b5-97dd-239a5a50ebbd, you can use the following URL:
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks(guid'ab072e85-0ab6-42b5-97dd-239a5a50ebbd')/Name
You can return the members of one collection that are related to a single member of another collection by specifying the name of the related collection at the end of the URL for the specific member. For example to return all jobs from a single runbook with the GUID ab072e85-0ab6-42b5-97dd-239a5a50ebbd, you can use the following URL:
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks(guid'ab072e85-0ab6-42b5-97dd-239a5a50ebbd')/Jobs
Query Options
Query options allow you to further refine which data is returned and how it is ordered. Each option is specified with a dollar sign ($), and multiple options are separated with an ampersand (&). Each of the options is described in the following sections.
Expand Query Option ($expand)
The expand query option includes members for one collection that are associated with the members in the collection that you are returning. The following example returns all runbooks and their associated jobs.
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks?$expand=Jobs
Filter Query Option ($filter)
The filter query option allows you to limit the results of the query to only those entries that match the specified criteria. You can specify criteria using one or more of the properties of the collection that you are returning. The following table provides the list of operators that you can use in specifying the criteria.
| Operator | Description |
|---|---|
eq |
equal |
ne |
not equal |
gt |
greater than |
ge |
greater than or equal |
lt |
less than |
le |
less than or equal |
and |
logical and |
or |
logical or |
not |
logical not |
The following example returns all runbooks with the name My Runbook.
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks?$filter=Name eq 'Top Test'
The following example returns all runbooks that were created after 3/1/2012 and orders them by Name.
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks?$filter=CreationTime gt datetime'2012-03-01'&$orderby=Name
You can get further options and examples for the $filter query option at OData: URI Conventions.
Inlinecount Query Option ($inlinecount)
The inlinecount query option allows you to include a count of the total number of entries in the request response. A value of allpages will include the total count. This value can be used to determine how many entries when the total count exceeds the maximum number of entries for the collection. The following example will return records 101-150 (the third page of 50) and include the total number of records in the request response.
https://server1.contoso.com:9090/00000000-0000-0000-0000-000000000000/Jobs?$skip=100&$top=50&$inlinecount=allpages
Note
For more information about the maximum number of entries returned see Paging in Service Management Automation Web Service.
Orderby Query Option ($orderby)
The orderby query option specifies the property that the returned entries are ordered by. The following example returns all runbooks sorted by the Name property.
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks?$orderby=Name
Select Query Option ($select)
The select query option allows you to specify a subset of properties that are returned for each member of the collection. The following example returns the Id and Name for all runbooks.
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks?$select=Id,Name
Skip Query Option ($skip)
The skip query option allows you to skip the first specified number of members of a collection. This option is usually paired with the orderby query option to ensure that a predictable set of entries are skipped. The following example returns all runbooks after skipping the first 5.
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks?$orderby=Name&$skip=5
Top Query Option ($top)
The top query option allows you to specify the number of entries to return. This can be paired with the skip query option to select a specific set page of entries. The following example returns the second set of 50 runbooks ordered by name.
https://server01.contoso.com:9090/00000000-0000-0000-0000-000000000000/Runbooks?$orderby=Name&$skip=50&$top=50
Note
The value for the top query option cannot exceed the maximum number of entries that are returned for a particular collection. This value is 50 for most collections. For more information see Paging in Service Management Automation Web Service.
See Also
Concepts
Authentication and Authorization
Programming Using the Service Management Automation Web Service
Standard Service Management Automation POST/GET/PUT/DELETE Headers