Reports

The reports feature offers developers the capability to securely expose data queries in various formats such as XML, JSON, and CSV. This ensures that data can be accessed externally while maintaining security through authentication and tokenization.

Token

Reports make use of a secure random (384bit) token to access the specified query. This token should be kept private and not shared with anyone as they will be able to access the report with it.

Access

Depending on the sensitivity of the data you can use 3 options to access a report.

• IdWithToken: You can pass the token as a query parameter (e.g. https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea?token=TOKEN)

• AuthorizationHeader (Recommended): This method uses the Authorization header to pass the token. The main benefit is that the token won’t be accidentally exposed in places where the request url is logged (i.e. diagnostics).

  curl -H 'Authorization: Bearer TOKEN' 'https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea'

• AuthorizationSignature (Best security): This method needs custom code to generate a HMAC256 signature for the requested resource. The benefits are that the key is never send across the wire and that the request can’t be replayed at a later time.

You can configure the minimum access level that should be used on a report. When it is accessed using a lesser secure option it will be flagged as compromised and it will not be available unless a new token has been generated.

Format

Reports return XML by default. You can use the format query parameter or Accept header to request the data as json (application/json), csv (text/csv) or tsv (text/tab-separated-values).

Examples:

• https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea?token=TOKEN&format=json

• https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea?token=TOKEN&format=csv

• curl:

  curl -H 'Accept: application/json' 'https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea'

AuthorizationHeader

This option expects the token to be passed as Authorization header using the Bearer scheme when accessing the report.

curl

curl -H 'Accept: application/json' -H 'Authorization: Bearer YOUR_TOKEN_HERE' 'https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea'

JavaScript

var token = "YOUR_TOKEN_HERE";
var reportUrl = "https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea";
var headers = {
  "Accept": "application/json",
  "Authorization":"Bearer " + token
};
fetch(reportUrl, { headers: headers })
  .then(function (response) {
    return response.json();
  })
  .then(function (json) {
    var data = json.d; // Array with the objects
    console.log(data)
  });

PowerShell

$token = 'get token from secure setting/vault'
$reportUrl = 'https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea'
$headers = @{"Accept"='application/json';"Authorization"='Bearer '+$token}
$data = (Invoke-WebRequest $reportUrl -Headers $headers | ConvertFrom-Json).d

AuthorizationSignature

The signature is generated by including a Date header (containing a RFC1123 formatted UTC date and time), an Accept header (containing application/json, text/xml, text/csv or text/tab-separated-values) and using HMAC256 to sign the combination of the request url, accept and timestamp using a newline separator with the token as key. The Date header will be validated that it is no more than 5 minutes in the past or future (to allow for some clock skewing).

.NET

var token = "YOUR_TOKEN_HERE"; // It's recommended to get this from a setting/vault/configuration/...
var reportUrl = "https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea";
var accept = "application/json";
var date = System.DateTime.UtcNow;
var timestamp = date.ToString("R", System.Globalization.CultureInfo.InvariantCulture);
var messageSignature = string.Join("\n", reportUrl, accept, timestamp);

string signature;
using (var hmac = new System.Security.Cryptography.HMACSHA256(System.Text.Encoding.UTF8.GetBytes(token)))
    signature = Convert.ToBase64String(hmac.ComputeHash(System.Text.Encoding.UTF8.GetBytes(messageSignature)));

var request = System.Net.WebRequest.CreateHttp(reportUrl);
request.Accept = accept;
request.Date = date;
request.Headers.Add("Authorization", "Bearer " + signature);

PowerShell

$token = 'get token from secure setting/vault'
$reportUrl = 'https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea'
$accept = 'application/json'
$timestamp = [System.DateTime]::UtcNow.ToString('R')
$messageSignature = "$reportUrl`n$accept`n$timestamp"

$hmac = New-Object System.Security.Cryptography.HMACSHA256
$hmac.Key = [Text.Encoding]::UTF8.GetBytes($token)
$signature = [Convert]::ToBase64String($hmac.ComputeHash([Text.Encoding]::UTF8.GetBytes($messageSignature)))

$headers = @{"Accept"=$accept;"Date"=$timestamp;"Authorization"='Bearer '+$signature}
$data = (Invoke-WebRequest $reportUrl -Headers $headers | ConvertFrom-Json).d

Postman

For testing you can also use Postman as it allows for Pre-request scripts and variables:

// Pre-request Script
var token = pm.environment.get("token");
var reportUrl = request.url;
var accept = "application/json";
var timestamp = (new Date()).toGMTString();
var messageSignature = [reportUrl, accept, timestamp].join("\n");

var signature = CryptoJS.HmacSHA256(messageSignature, token).toString(CryptoJS.enc.Base64);

pm.globals.set("accept", accept);
pm.globals.set("timestamp", timestamp);
pm.globals.set("encrypted_signature", "Bearer " + signature);

Compromised

When a report is accessed using a less secure option than configured or using http instead of https it will be flagged as compromised. A log entry will be written with information about the requested url, ip and user-agent.

You can re-enable the report by giving it a new token. On a production deployment the patching feature can be used to regenerate the token.

Excel

Reports can be used to import data in an Excel file. The data will be linked to the Excel table so that it can be refreshed manually or automatically when the file is opened.

This can be done by starting the From Web wizard in Excel:

In the wizard you can switch to Advanced mode where you can add the Authorization header:

Paging / Sorting

Using top and/or skip also requires sorting to provide consistent results.

Examples:

• https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea?token=TOKEN&format=json&$orderBy=FirstName&$top=100&$skip=100
• https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea?token=TOKEN&format=csv&$orderBy=LastName+DESC&$top=100

For the AuthorizationSignature option you’ll need to include the whole url as requested (which shouldn’t contain the token and format in that case, the format should be in the Accept header) for the signature calculation.

Filtering

You can use the $filter query parameter to include the text-search (as used inside a Vidyano application in the search box on a query) that should be used to filter.

Examples:

• https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea?token=TOKEN&format=json&$filter=FirstName:Steve
• https://localhost:44332/GetReport/4cb4854ca87a4e4aaf66c8f2469d58ea?token=TOKEN&format=csv&$filter=FirstName:Steve+LastName:Hansen

For the AuthorizationSignature option you’ll need to include the whole url as requested (which shouldn’t contain the token and format in that case, the format should be in the Accept header) for the signature calculation.