Programmatic access paradigm for commercial marketplace

This diagram shows the API call pattern used to create a new report template, schedule the custom report, and retrieve failure data.

Illustrates the API call pattern used to create a new report template, schedule the custom report, and retrieve failure data. Figure 1: High-level flow of the API call pattern

This list provides more details about Figure 1.

  1. The Client Application can define the custom report schema/template by calling the Create Report Query API. Alternately, you can use a report template (QueryId) from the list of system queries.
  2. On success, the Create Report Template API returns the QueryId.
  3. The client application then calls the Create Report API using the QueryID along with the report start date, Repeat Interval, Recurrence, and an optional Callback URI.
  4. On success, the Create Report API returns the ReportID.
  5. The client application gets notified at the callback URI as soon as the report data is ready for download.
  6. The client application then uses the Get Report Executions API to query the status of the report with the Report ID and date range.
  7. On success, the report download link is returned and the application can initiate download of the data.

Report query language specification

While we provide system queries you can use to create reports, you can also create your own queries based on your business needs. To learn more about custom queries, see Custom query specification.

Create report query API

This API helps to create custom queries that define the dataset from which columns and metrics need to be exported. The API provides the flexibility to create a new reporting template based on your business needs.

You can also use the system queries we provide. When custom report templates aren't needed, you can call the Create Report API directly using the QueryIds of the system queries we provide.

The following example shows how to create a custom query to get Normalized Usage and Estimated Financial Charges for PAID SKUs from the ISVUsage dataset for the last month.

Request syntax

Method Request URI
POST https://api.partnercenter.microsoft.com/insights/v1.1/cmp/ScheduledQueries

Request header

Header Type Description
Authorization string Required. The Microsoft Entra access token. The format is Bearer <token>.
Content-Type string application/JSON

Path parameter

None

Query parameter

None

Request payload example

{
    "Name": "ISVUsageQuery",
    "Description": "Normalized Usage and Estimated Financial Charges for PAID SKUs",
    "Query": "SELECT UsageDate, NormalizedUsage, EstimatedExtendedChargePC FROM ISVUsage WHERE SKUBillingType = 'Paid' ORDER BY UsageDate DESC TIMESPAN LAST_MONTH"
}

Glossary

This table provides the key definitions of elements in the request payload.

Parameter Required Description Allowed values
Name Yes User-friendly name of the query string
Description No Description of the created query string
Query Yes Query string based on business need string

Note

For custom query examples, see sample queries.

Sample response

The response payload is structured as follows:

Response codes: 200, 400, 401, 403, 500

Response payload example:

{
  "value": [
        {
            "queryId": "78be43f2-e35f-491a-8cd5-78fe14194f9c",
            "name": " ISVUsageQuery",
            "description": "Normalized Usage and Estimated Financial Charges for PAID SKUs",
            "query": " SELECT UsageDate, NormalizedUsage, EstimatedExtendedChargePC FROM ISVUsage WHERE SKUBillingType = 'Paid' ORDER BY UsageDate DESC TIMESPAN LAST_MONTH",
            "type": "userDefined",
            "user": "142344300",
            "createdTime": "2024-01-06T05:38:34Z"
        }
    ],
    "totalCount": 1,
    "message": "Query created successfully",
    "statusCode": 200
}

Glossary

This table provides the key definitions of elements in the response.

Parameter Description
QueryId Universally unique identifier (UUID) of the created query
Name Name provided in the request payload during query creation
Description Description provided in the request payload during query creation
Query Custom report query provided in the request payload during query creation
Type Set to userDefined for manually created queries
User User ID used to create the query
CreatedTime UTC Time when the query was created. Format: yyyy-MM-ddTHH:mm:ssZ
TotalCount Number of records in the Value array
StatusCode Result Code
The possible values are 200, 400, 401, 403, 500
message Status message from the execution of the API

Create report API

On creating a custom report template successfully and receiving the QueryID as part of Create Report Query response, this API can be called to schedule a query to be executed at regular intervals. You can set a frequency and schedule for the report to be delivered. For system queries we provide, the Create Report API can also be called with QueryId.

Request syntax

Method Request URI
POST https://api.partnercenter.microsoft.com/insights/v1.1/cmp/ScheduledReport

Request header

Header Type Description
Authorization string Required. The Microsoft Entra access token. The format is Bearer <token>.
Content Type string application/JSON

Path parameter

None

Query parameter

None

Request payload example

{
  "ReportName": "ISVUsageReport",
  "Description": "Normalized Usage and Estimated Financial Charges for PAID SKUs",
  "QueryId": "78be43f2-e35f-491a-8cd5-78fe14194f9c ",
  "StartTime": "2021-01-06T19:00:00Z ",
  "executeNow": false,
  "RecurrenceInterval": 48,
  "RecurrenceCount": 20,
  "Format": "csv",
  "CallbackUrl": "https://<SampleCallbackUrl>"
  "callbackMethod": "GET",
}

Glossary

This table provides the key definitions of elements in the request payload.

Parameter Required Description Allowed values
ReportName Yes User-friendly name assigned to the report String
Description No Description of the created report String
QueryId Yes Query ID that needs to be used for report generation String
StartTime Yes UTC Timestamp at which the report generation will begin. The format should be yyyy-MM-ddTHH:mm:ssZ String
ExecuteNow No This parameter should be used to create a report that is executed only once. StartTime, RecurrenceInterval, RecurrenceCount, and EndTime are ignored if this is set to true Boolean
QueryStartTime No Optionally specifies the start time for the query extracting the data. This parameter is applicable only for one time execution report which has ExecuteNow set to true. The format should be yyyy-MM-ddTHH:mm:ssZ Timestamp as string
QueryEndTime No Optionally specifies the end time for the query extracting the data. This parameter is applicable only for one time execution report which has ExecuteNow set to true. The format should be yyyy-MM-ddTHH:mm:ssZ Timestamp as string
RecurrenceInterval Yes Frequency in hours at which the report should be generated. Minimum value is 1 and maximum value is 17520 Integer
RecurrenceCount Yes Number of reports to be generated. Limit is dependent on Recurrence interval Integer
Format No File format of the exported file. Default format is CSV CSV/TSV
CallbackUrl No Publicly accessible URL that can be optionally configured as the callback destination String
CallbackMethod No Get/Post method that can be configured with callback URL GET/POST
EndTime Yes UTC timestamp at which the report generation will end. The format should be yyyy-MM-ddTHH:mm:ssZ String

Note

While creating the report, either EndTime or combination of RecurrenceInterval and RecurrenceCount is mandatory.

Sample response

The response payload is structured as follows:

Response code: 200, 400, 401, 403, 404, 500

Response payload:

{
  "Value": [
    {
            "reportId": "72fa95ab-35f5-4d44-a1ee-503abbc88003",
            "reportName": "ISVUsageReport",
            "description": "Normalized Usage and Estimated Financial Charges for PAID SKUs",
            "queryId": "78be43f2-e35f-491a-8cd5-78fe14194f9c",
            "query": "SELECT UsageDate, NormalizedUsage, EstimatedExtendedChargePC FROM ISVUsage WHERE SKUBillingType = 'Paid' ORDER BY UsageDate DESC TIMESPAN LAST_MONTH",
            "user": "142344300",
            "createdTime": "2024-01-06T05:46:00Z",
            "modifiedTime": null,
            "startTime": "2024-01-06T19:00:00Z",
            "reportStatus": "Active",
            "recurrenceInterval": 48,
            "recurrenceCount": 20,
            "callbackUrl": "https://<SampleCallbackUrl>",
            "callbackMethod": "GET",
            "format": "csv"
    }
  ],
  "TotalCount": 1,
  "Message": "Report created successfully",
  "StatusCode": 200
}

Glossary

This table provides the key definitions of elements in the response.

Parameter Description
ReportId Universally unique identifier (UUID) of the report you created
ReportName Name provided in the request payload during report creation
Description Description provided in the request payload during report creation
QueryId Query ID provided in the request payload during report creation
Query Query text that will be executed for this report
User User ID used to create the report
CreatedTime UTC Time the report was created in this format: yyyy-MM-ddTHH:mm:ssZ
ModifiedTime UTC Time the report was last modified in this format: yyyy-MM-ddTHH:mm:ssZ
ExecuteNow ExecuteNow parameter provided in the request payload during report creation
queryStartTime Query start time provided in the request payload during report creation. This is applicable only if ExecuteNow is set to "True"
queryEndTime Query end time provided in the request payload during report creation. This is applicable only if ExecuteNow is set to "True"
StartTime Start time provided in the request payload during report creation
ReportStatus Status of the report execution. The possible values are Paused, Active, and Inactive.
RecurrenceInterval Recurrence interval provided in the request payload during report creation
RecurrenceCount Remaining recurrence count for the report
CallbackUrl Callback URL provided in the request payload during report creation
CallbackMethod Callback method provided in the request payload during report creation
Format Format of the report files provided in the request payload during report creation
EndTime End time provided in the request payload during report creation. This is applicable only if ExecuteNow is set to "True"
TotalRecurrenceCount RecurrenceCount provided in the request payload during report creation
nextExecutionStartTime UTC timestamp when next report execution will start
TotalCount Number of records in the Value array
StatusCode Result Code. The possible values are 200, 400, 401, 403, 500
message Status message from the execution of the API

Get report executions API

You can use this method to query the status of a report execution using the ReportId received from the Create Report API. The method returns the report download link if the report is ready for download. Otherwise, the method returns the status. You can also use this API to get all the executions that have happened for a given report.

Important

This API has default query parameters set for executionStatus=Completed and getLatestExecution=true. Hence, calling the API before the first successful execution of the report will return 404. Pending executions can be obtained by setting executionStatus=Pending.

Request syntax

Method Request URI
Get https://api.partnercenter.microsoft.com/insights/v1.1/cmp/ScheduledReport/execution/{reportId}?executionId={executionId}&executionStatus={executionStatus}&getLatestExecution={getLatestExecution}

Request header

Header Type Description
Authorization string Required. The Microsoft Entra access token. The format is Bearer <token>.
Content type string application/json

Path parameter

None

Query parameter

Parameter name Required Type Description
reportId Yes string Filter to get execution details of only reports with reportId given in this argument.
executionId No string Filter to get details of only reports with executionId given in this argument. Multiple executionIds can be specified by separating them with a semicolon ";".
executionStatus No string/enum Filter to get details of only reports with executionStatus given in this argument.
Valid values are: Pending, Running, Paused, and Completed
The default value is Completed.
getLatestExecution No boolean The API will return details of the latest report execution.
By default, this parameter is set to true. If you choose to pass the value of this parameter as false, then the API will return the last 90 days execution instances.

Request payload

None

Sample response

The response payload is structured as follows:

Response codes: 200, 400, 401, 403, 404, 500

Response payload example:

{
    "value": [
        {
            "executionId": "a0bd78ad-1a05-40fa-8847-8968b718d00f",
            "reportId": "72fa95ab-35f5-4d44-a1ee-503abbc88003",
            "recurrenceInterval": 4,
            "recurrenceCount": 10,
            "callbackUrl": null,
            "format": "csv",
            "executionStatus": "Completed",
            "reportAccessSecureLink": "https://<path to report for download>",
            "reportExpiryTime": null,
            "reportGeneratedTime": "2021-01-13T14:40:46Z"
        }
    ],
    "totalCount": 1,
    "message": null,
    "statusCode": 200
}

Once report execution is complete, the execution status Completed is shown. You can download the report by selecting the URL in the reportAccessSecureLink.

Glossary

Key definitions of elements in the response.

Parameter Description
ExecutionId Universally unique identifier (UUID) of the execution instance
ReportId Report ID associated with the execution instance
RecurrenceInterval Recurrence interval provided during report creation
RecurrenceCount Recurrence count provided during report creation
CallbackUrl Callback URL associated with the execution instance
CallbackMethod Callback method provided in the request payload during report creation
Format Format of the generated file at the end of execution
ExecutionStatus Status of the report execution instance.
Valid values are: Pending, Running, Paused, and Completed
ReportLocation Location where report is downloaded.
ReportAccessSecureLink Link through which the report can be accessed securely
ReportExpiryTime UTC Time after which the report link will expire in this format: yyyy-MM-ddTHH:mm:ssZ
ReportGeneratedTime UTC Time at which the report was generated in this format: yyyy-MM-ddTHH:mm:ssZ
EndTime End time provided in the request payload during report creation. This is applicable only if ExecuteNow is set to "True"
TotalRecurrenceCount RecurrenceCount provided in the request payload during report creation
nextExecutionStartTime UTC timestamp when next report execution will start
TotalCount Number of datasets in the Value array
StatusCode Result Code
The possible values are 200, 400, 401, 403, 404 and 500
message Status message from the execution of the API

You can try out the APIs through the Swagger API URL.