Custom CSV/JSON Data Sources

In addition to built-in integrations with third-party systems, you can load both structured CSV and semi-structured JSON data into minware.

Custom data is fully accessible from minQL and can be integrated with any custom report.

To create a new custom CSV/JSON integration, navigate to Settings > Data Sources > Integrations in your account. You must be an account owner to configure custom integrations.

From this page, click on the "Custom CSV/JSON" option at the bottom of the page. This will bring you to the dialog for creating a new integration.

After you create an integration, you will see it at the top of the Integrations page. You can click 'Edit' to update your configuration, or 'Upload' to upload a new file from your browser.

General Notes

Here are general notes about custom CSV/JSON integrations:

• For each integration, you can upload both CSV and JSON files, and do not have to specify one format when creating the integration.
• If certain properties/headers are not present in all of the files that you upload for an integration, they will be null when you reference them for files that do not have those properties.
• Any options you set for CSV files when creating an integration will only apply to CSV uploads and will be ignored for any JSON files that you upload.
• Any changes you make to your custom integration configuration after it is created will apply to all files that have ever been uploaded for that integration, not just new ones.
• ID properties are optional. If you specify ID properties, later rows with the same ID properties will replace earlier rows.
• When uploading files from your browser, the file name must end in .csv, .json, or .jsonl, which indicates the type of file (json and jsonl are interchangeable and either can be used for JSON files). When using the API, you must specify json or jsonl for the file type if the file is JSON, otherwise it will be interpreted as CSV.

Options for All File Types

The following configuration options are available when creating a custom integration, and they apply to all files that you upload (both CSV and JSON):

• Integration Display Name - This is the name that will be shown on the Integrations admin page.
• Description - (Optional) A text description to show on the Integrations admin page.
• lowerCamelCase minQL Reference Name - This is the name you will use to reference custom report data in minware reports using minQL. For example, with a reference name of employeeInfo, you would write custom.employeeInfo.get('Favorite Food') to retrieve the Favorite Food field from a CSV file header or JSON property name.
• Historical Data Handling - Custom integrations offer three different options for how to handle multiple file uploads:
  1. Replace - Each time you upload a new file, it will completely replace any previously uploaded data.
  2. Keep All - The data from every file you upload will be combined. However, newer rows with the same unique ID will replace previous rows with the same ID from previously uploaded files or earlier rows in the same file by row number (if you specify a unique ID property, otherwise all rows will be kept).
  3. Use Upload Time - When a new file is uploaded, rows with ID properties that match rows in previous files will have their values updated to new values as of the upload time. The time series values will be available in minQL. For example, adding the value custom.employee.getOrig('team') when reporting on completed tickets would get the employee's team from the file uploaded most recently prior to the ticket completion date.
• ID Properties - (Optional) If specified, the property or list of properties specified here will be used to construct a unique ID for each row. Later rows having the same ID properties will replace or update (depending on historical data handling) values from earlier rows with those ID properties. If left blank, no rows will be deduped by ID.
• Property Type Overrides - (Optional) By default, all CSV values will be strings, and all JSON values will be one of the native JSON types. You can always convert values with minQL in reports, but this option lets you convert types at the integration level for convenience. This is especially helpful for things like timestamps. If a value fails to convert (such as if it's an invalid timestamp), it will be set to null.

CSV-Specific Options

The following options are available for CSV files. These options are ignored for JSON uploads.

• CSV Header Handling - There are three different options for how to treat CSV headers:
  1. Standard First-Row Headers - Each value in the first row will be used as a property name for values in the rows that follow. If the header properties change between uploads, each file will have its properties set based on the headers in the current file, and properties that only exist in headers from other files will be empty/null. Headers are case-sensitive and can include special characters.
  2. None - The CSV files will be treated as not having headers. You can reference the values in minQL as col1, col2, etc.
  3. Projected First-Row Headers - Values in the first row after a specified column index will be used as dynamic property values, effectively splitting each row based on the number of columns in the first row after that index. This option can be used to support matrix-style CSV files where new columns are added dynamically to indicate new data.
• The following options are only available for projected headers. They are all required:
  • Project Headers At - Starting at this 1-based column index (so 1 would project all columns), header values will be assigned to a property name and columns after this index will be split into multiple rows. Required for projected headers.
  • Projected Header Value Property Name - The header values will be assigned to a property with this name.
  • Projected Row Value Property Name - The values for each cell underneath the projected header will be assigned to a property with this name.
• CSV Null Handling - There are three different ways to handle null values in CSV files:
  1. None - Do not replace any values with null. Empty values will be treated as emptry strings.
  2. Empty Strings - Replace empty strings with null.
  3. Placeholder - Replace strings that match a particular placeholder (e.g., \N) with null.

JSON-Specific Notes

These notes only apply to JSON files:

• If there are nested objects or arrays in uploaded JSON files, they will appear as those types in minQL and you can use minQL functions to reference nested values.
• JSON files should have each row separated by a newline, not be a single array. Individual JSON rows may contain newlines as well and will be parsed properly.

Referencing Data With minQL

Here are some tips for referencing custom upload data from minQL in reports:

• To create a report that shows all data from the uploaded file, you can use custom.<integrationName>.event('existsAtEnd') as a measure value, which will output the total row count.
• If you want to aggregate values from a column instead of counting rows, you can replace the value, like this: custom.<integrationName>.event('existsAtEnd').value(custom.<integrationName>.get('columnName')).sum().
• Then, to break down the data by column values from the uploaded file, you can use custom.<integrationName>.get('<columnName>') as a breakdown value, where <columnName> is the case-sensitive name of the column in the uploaded file.
• If you want to look up data from a file in an existing report, you can use lookupObject(). For example, if the CSV file has additional information about ticket projects that you want to add to a report with tickets, you can add a breakdown of: ticket.get('project').lookupObject(custom.<integrationName>, 'project').get('info') where 'project' contains the project name in the uploaded file, and 'info' contains the additional information you want to add to the report.

Uploading Via API

If you would like to programmatically upload files for an integration via minware's API, the first step is to create an API key. You can do so by going to Settings > API Keys.

API keys carry the permissions of the user who created them. Only users who are currently have owner permissions for org can use API keys to upload files to a custom integration. If a user is downgraded to another role, removed from the org, or the API key is expired, the key will no longer be able to access the upload endpoint.

To use an API key, provide it in the Authorization header as a Bearer token when making requests. For example, if your API key is minware_k_1234, you would include this header:

Authorization: Bearer minware_k_1234

Please treat your API keys like a password. Keep them secret and rotate them regularly.

Upload API Endpoint

To view the API endpoint for a custom integration, you can click 'Edit' or 'Upload' on the integrations page next to the integration. This will show you a url like:

POST /api/org/<orgId>/custom-integrations/<integrationId>/upload

The request body should contain the file contents. The following query parameters can also be added at the end of the URL:

• dataTimestamp - The timestamp of the data in the file. Optional. Defaults to the current time. This timestamp indicates the start time for this file's data. When "Use Upload Time" is selected for the integration, the file with the most recent data timestamp prior to events or durations in reports will be used. If not provided, the current time will be used for this file.
• fileType - The type of the file. One of: csv, json, or jsonl. Optional for CSV files. Required for JSON files. (json and jsonl are interchangeable and do not affect file processing.)

The response will include the following properties in a JSON object.

• In the case of a successful request, these properties will be set:
  • success - Will be true if the request was successful, otherwise won't be present
  • rowsLoaded - If successful, the number of rows loaded including headers.
• If a error occurs, these properties will be set in addition to a standard 4xx or 5xx HTTP response code:
  • error - Brief summary of the error type.
  • details - Will be set if there are additional details about the error.