SpiraPlan: OData Web Service

How To Access The OData API

Using the Right URL

To access OData web services, you need to use the specific URL of the application.

For instance, if you access the application in the browser from https://companyname.spiraservice.net, then that will be the base of the URL used for accessing the API. The API URL will be: https://companyname.spiraservice.net/api/odata

If you are viewing this page from your application, the base URL is:

https://api.inflectra.com/spira/services/OData.aspx

Specifying The Data Format

The OData API supported in SpiraPlan is OData v4.0 which is based on JSON, so you need to use the following content type / accept headers:

  • Content-Type: application/json - Sends data in JSON format
  • accept: application/json - Returns data in JSON format

Authentication

There are three different ways to authenticate with the web service. They all need a username and an api-key. The api-key used is the same as the RSS token created by the application. You can find a user's RSS token by either going to the user profile (either by visiting your own profile page, or accessing it via the administration panel). If no RSS token is shown for a user, make sure "Enable RSS Feeds" is set to yes.

Copy the full RSS-token / api-key for the relevant user--including the curly braces { }. In the below examples the username "fredbloggs" is used, with an api-key of "{XXXXXXXXXXXXXXXX}"

  1. Append the username and application API-Key as an extra querystring parameter: 
    ?username=fredbloggs&api-key={XXXXXXXXXXXXXXXX}
  2. Pass in the username application API-Key using the username and api-key HTTP headers: 
    username: fredbloggs
    api-key: {XXXXXXXXXXXXXXXX}
  3. Pass in the username and api-key using the standard HTTP basic authentication header: 
    Authorization: Basic XXXXXXXXXXXXXXXXXXXXXXXXXX
    where XXXXXXXXXXXXXXXXXXXXXXXXXX is username:api-key base64 encoded

Example OData Calls

Note: OData is primarily designed to allow third-party reporting tools such as PowerBI and Excel to access the data in SpiraPlan for the purposes of creating custom graphs and reports. So although we include some examples here of using the raw OData protocol manually, we recommend that you consider using a tool such as Excel or PowerBI instead.

When you connect to the main OData endpoint URL (e.g. https://companyname.spiraservice.net/api/odata), you will receive a JSON response containing the list of reportable views available for querying: 

{
   "@odata.context":"https://companyname.spiraservice.net/api/odata/$metadata",
   "value":[
      {
         "name":"ArtifactAssociations",
         "kind":"EntitySet",
         "url":"ArtifactAssociations"
      },
      {
         "name":"ArtifactAttachments",
         "kind":"EntitySet",
         "url":"ArtifactAttachments"
      },
      {
         "name":"Attachments",
         "kind":"EntitySet",
         "url":"Attachments"
      },
      {
         "name":"AutomationHosts",
         "kind":"EntitySet",
         "url":"AutomationHosts"
      },
        ...
      {
         "name":"Users",
         "kind":"EntitySet",
         "url":"Users"
      }
   ]
}

From here, you can now add the name of the EntitySet to your URL and then it will display the first 50 rows of data from the appropriate EntitySet.

For example, if you were to query https://companyname.spiraservice.net/api/odata/Incidents, you would see: 

{
   "@odata.context":"https://companyname.spiraservice.net/api/odata/$metadata#Incidents",
   "value":[
      {
         "INCIDENT_ID":1,
         "PROJECT_ID":1,
         "INCIDENT_STATUS_ID":1,
         "INCIDENT_TYPE_ID":1,
         "OPENER_ID":2,
         "DESCRIPTION":"When trying to log into the application with a valid username and password, the system throws a fatal exception",
         "CREATION_DATE":"2021-03-02T20:04:45.62-05:00",
         "CLOSED_DATE":null,
         "LAST_UPDATE_DATE":"2021-03-19T20:04:45.62-04:00",
         "START_DATE":null,
         "COMPLETION_PERCENT":0,
         "IS_DELETED":false,
         "PRIORITY_NAME":null,
         "PRIORITY_COLOR":null,
         "SEVERITY_NAME":null,
         "SEVERITY_COLOR":null,
         "INCIDENT_STATUS_NAME":"New",
         "INCIDENT_TYPE_NAME":"Incident",
         "OPENER_NAME":"Fred Bloggs",
         "OWNER_NAME":null,
         "DETECTED_RELEASE_VERSION_NUMBER":"1.0.0.0",
         "RESOLVED_RELEASE_VERSION_NUMBER":"1.0.1.0",
         "VERIFIED_RELEASE_VERSION_NUMBER":"1.0.1.0",
         "PROJECT_GROUP_ID":2,
         "PROJECT_NAME":"Library Information System (Sample)",
         "CUST_01":null,
         "CUST_02":null,
         "CUST_03":null,
        ...
         "CUST_99":null,
         "NAME":"Cannot log into the application",
         "IS_ATTACHMENTS":true,
         "COMPONENT_IDS":null,
         "INCIDENT_STATUS_IS_OPEN_STATUS":true,
         "PROJECT_IS_ACTIVE":true,
         "CONCURRENCY_DATE":"2021-03-19T20:04:45.62-04:00",
         "END_DATE":null,
         "DETECTED_BUILD_NAME":null,
         "RESOLVED_BUILD_NAME":null
      },
      ...
      {
         "INCIDENT_ID":2,
         "PROJECT_ID":1,
         "INCIDENT_STATUS_ID":1,
         "INCIDENT_TYPE_ID":1,
         "OPENER_ID":3,
         "DESCRIPTION":"When I try and click on the button to add a new author the system simply displays the main screen and does nothing",
         "CREATION_DATE":"2021-03-02T20:04:45.62-05:00",
         "CLOSED_DATE":null,
         "LAST_UPDATE_DATE":"2021-03-07T20:04:45.62-05:00",
         "START_DATE":null,
         "COMPLETION_PERCENT":0,
         "IS_DELETED":false,
         "PRIORITY_NAME":null,
         "PRIORITY_COLOR":null,
         "SEVERITY_NAME":null,
         "SEVERITY_COLOR":null,
         "INCIDENT_STATUS_NAME":"New",
         "INCIDENT_TYPE_NAME":"Incident",
         "OPENER_NAME":"Joe P Smith",
         "OWNER_NAME":null,
         "DETECTED_RELEASE_VERSION_NUMBER":"1.0.0.0",
         "RESOLVED_RELEASE_VERSION_NUMBER":"1.0.1.0",
         "VERIFIED_RELEASE_VERSION_NUMBER":"1.0.1.0",
         "PROJECT_GROUP_ID":2,
         "PROJECT_NAME":"Library Information System (Sample)",
         "CUST_01":null,
         "CUST_02":null,
         "CUST_03":null,
         ...
         "CUST_99":null,
         "NAME":"Not able to add new author",
         "IS_ATTACHMENTS":false,
         "COMPONENT_IDS":"2",
         "INCIDENT_STATUS_IS_OPEN_STATUS":true,
         "PROJECT_IS_ACTIVE":true,
         "CONCURRENCY_DATE":"2021-03-07T20:04:45.62-05:00",
         "END_DATE":null,
         "DETECTED_BUILD_NAME":null,
         "RESOLVED_BUILD_NAME":null
      },
    ],
    "@odata.nextLink":"https://companyname.spiraservice.net/api/odata/Incidents?$skip=50"
}

Advanced Querying Options

The OData protocol lets you perform standard querying on the data, including filtering, sorting and pagination. These are done using special operators that get added to the querystring:

Filtering

To filter data using OData, you simply use the $filter= operator in the URL. A simple example is shown below for filtering the list of incidents to be only those that are priority "1 - High": 

https://companyname.spiraservice.net/api/odata/Incidents?$filter=PRIORITY_NAME eq '2 - High'

Sorting

To sort data using OData, you simply use the $orderby= operator in the URL. A simple example is shown below for sorting the list of incidents by priority: 

https://companyname.spiraservice.net/api/odata/Incidents?$orderby=PRIORITY_NAME asc

Other Query Operators

For more information on querying OData, please refer to the following guide:
https://www.odata.org/getting-started/basic-tutorial/#queryData