1 of 100

3.4.3 Introduction

Exivity is a metering and billing software solution for public and private cloud environments that allows you to report on cloud consumption from any IT resource. Exivity enables you to apply your MSP/CSP business rules and makes any type of Pay-as-you-Go model work. It also facilitates internal charge-back and show-back requirements for Enterprise IT.

These things are done by extracting IT consumption data from various endpoints and then mapping this data to meaningful customer-specific information such as services, customer IDs, names and contracts.

There are four main steps involved in a successful deployment:

Extract
Transform
Report
Integrate (optional)

Extract

The Extract step defines your data sources such as:

APIs that return usage data, service catalogue, rate card, customer/subscriber lists and similarly available records from public or private clouds
APIs or ODBC queries that return contracts, customer names, IDs and other contextual lookup data from CMDB / CRM systems
Flat files on disk in CSV, JSON or XML format
Other HTTP/S sources

Exivity provides a rich scripting interface via its Unified Scriptable Extractor (USE) component which facilitates integration with almost any data source. For most of the big cloud platforms we provide template extractor scripts as part of the product. Additionally you can also write your own USE scripts from scratch in order to integrate with custom data sources.

Transform

The Transform step provides a powerful ETL framework for processing extracted data. Using it you can merge consumption metrics, contract details, customer information, custom metadata, service definitions or any other imported information to produce an enriched and/or normalised result.

This is done using the Transcript component, which executes user-definable scripts (termed tasks) in order to produce a meaningful set of data suitable for reporting against. Often this data will feed a consolidated bill of IT based on the various different consumed services.

Transcript also allows you to define and populate services and rates, either of which may be passed through from cloud data, defined as custom offerings or a mixture of the two.

Report

Exivity provides a modern responsive User Interface, that allows you to 'slice and dice' the processed data in any way you choose. Multiple Report Definitions can be created with ease which allow you to graphically and textually display both cost and usage statistics.

Integrate

We think that Exivity should be part of your automation landscape, where it can provide (for example) line items that can be digested by your ERP and/or invoicing system. Therefore we consider Integrate as the logical final step for any deployment where it is useful.

To this end we offer an open and fully featured REST API. Our GUI uses our own API for all back-end processes meaning that all textual data shown in the Exivity GUI is also obtainable via our API.

Detailed information about the REST API can be found at https://api.exivity.com

Getting started

Installation

Server

Exivity can be installed on any Microsoft Windows 2012 R2 or higher server in your on premise data center or in the cloud. Depending on the amount of data, Exivity recommends the following system configuration:

Both the Data sources and CUPR in above tables are recommended limits. All systems should have a standard C: OS drive. The storage recommendation in the table above is for a D: drive, which preferably is SSD.

Client

The Exivity front-end supports the following desktop browsers:

Google Chrome v59+
Microsoft Edge v41+ (EdgeHTML 16+ / Blink 80+)
Opera v46+
Mozilla Firefox v65+ (support added in Exivity v2.10.0)
Apple Safari v10.1+ (support added in Exivity v3.2.7)

We aim to provide the fastest metering and billing solution available today, and this means we have to rely on modern (web) technologies. Part of our speed comes from pre-processing the raw data, and part comes from having almost all processed data available right in the browser, and streaming the missing pieces on request.

To efficiently and reliably achieve this we use some very specific technologies not yet available in all browsers. When they do catch up, we'll fully support those browsers.

On-premises

Exivity can be installed in your on-premises data center using the provided installer. You can automatically deploy it using the silent installation command line options or execute it as an interactive installer.

To install Exivity, you'll need the following:

A system that complies with the Exivity minimal system requirements
The Exivity software installation executable
A valid Exivity license key

If you need help meeting one or more of the above requirements, please get in contact with our support department.

Interactive installation

To install Exivity interactively, execute the provided setup executable. Then follow the instructions depending on your intended architecture:

Single node deployment
Multi node deployment

Silent Installation

Silent installation is only recommended for experienced users requiring automated deployments

To execute a silent installation the following command line parameters are supported:

Example to install using all defaults:

<setup>.exe /S

Example to install using custom path and Exivity admin user:

<setup>.exe /S /EXIVITY_PROGRAM_PATH="C:\Program Files\Exivity\program" /EXIVITY_HOME_PATH=D:\Exivity\home /ADMIN_USER=master /ADMIN_PASSWORD=P@ssword

Updating minor versions

Updating your installation of Exivity to a more recent minor and/or bugfix release (i.e. 3.x.x --> 3.x.x) is straightforward and may be done by installing the new version over the top of the old.

Manually update

Execute the setup executable. It will detect the installed version of Exivity, and will automatically upgrade when you click Next

Silent update

When executing <setup>.exe /S, your existing installation will be automatically upgraded.

Upgrading major versions

When upgrading to a more recent major version (i.e. 2.10.2 --> 3.x.x) consult first the release notes to verify any breaking changes that may apply to your installation.

Installing a valid SSL certificate

Exivity comes as standard with an untrusted self-signed SSL certificate. It is therefore highly recommended to replace the default certificate with an official one, signed by your Certificate Authority. To install a signed certificate, follow this procedure:

Download the 32 bit version of openssl.exe from https://slproweb.com/products/Win32OpenSSL.html, and install this tool on the Exivity server
Use the openssl.exe executable to generate a valid key file on the Exivity server by executing the following command:

openssl.exe genrsa -out exivity.key 4096

Run the following command to create a certificate signing request file:

openssl.exe req -new -key exivity.key -out exivity.csr -addext "subjectAltName = DNS:example.com"

Replace example.com in the command above with the FQDN of the Exivity server.

You will be asked to enter general information like company name, city, etc. It is important to include the FQDN of the Exivity server when asked for Common Name (e.g. server FQDN or YOUR name) []:

NOTE: when asked, it is required to not provide a password (leave this field empty and press return), otherwise the Exivity application will not be able to use your certificate.

The generated CSR file should be sent to your Certificate Authority. After processing by your CA, you should receive back a .crt file. Rename this file to webcertificate.crt and rename your exivity.key to webcertificate.key and copy it to the directory %EXIVITY_PROGRAM_PATH%\server\nginx\conf. This should overwrite the existing .key and .crt files.
Restart the Exivity Web Service Windows service to activate your signed certificate.

Configuring a separate web server portal

In some environments it may be desirable to separate the webserver from the backend components. This can be achieved by installing two separate Exivity instances. One instance could be placed in a DMZ and the second instance would then typically be deployed within a local network as shown in the following diagram:

To achieve this, first install Exivity on the backend node using the standard procedure described here. Afterwards install the Exivity software on the system that should become the User Portal and only install the Web Component. When asked, specify the API Backend hostname and port to finalize the installation.

Make sure to replace HOSTNAME_BACKEND_PORTAL with the actual hostname or IP address of the system that serves as your Exivity Backend Portal.

When using SAML2 as an authentication mechanism for Single Sign On, and users also connect to a User Portal, take special attention to theX-Forwarded-Host and X-Forwarded-Port in the Nginx webproxy.conf. These are required when the User Portal is served on a different port (i.e. 443) compared to the backend portal API (i.e. 8002). When this is the case, the forwarded port needs to match the port number of the user portal.

After completing your installation, you should now be able to access your Exivity User Portal.

Using an Internet proxy when extracting data

In cases where Exivity instance requires internet connectivity (i.e. to obtain Azure or AWS consumption data), and your network infrastructure requires use of a proxy server, it is necessary to configure a system environment variable.

Right click on This PC in an Explorer Window and click on Properties:

Then go to Advanced System Settings, then click the Environment Variables button:

Now add a new System Variable with the name ALL_PROXY and fill in the address of your proxy server as the value for this variable:

In case you do not want to use the proxy for certain address or domains, it is also possible to add an additional variable NO_PROXY:

If the name in the noproxy list has a leading period, it is a domain match against the provided host name. This way ".example.com" will switch off proxy use for both "www.example.com" as well as for "foo.example.com".

After confirming the change, make sure to restart both the Exivity Windows Services.

Increase limit of filesize upload in the API

By default, Exivity API has a limit of 2048kb for filesize uploading, should you require to increase it, please modify the file located in %EXIVITY_PROGRAM_PATH%\server\php\php.ini

Adjust the variables post_max_size and upload_max_filesize to your desired value.

Enable PDF report exports

In order to generate PDF documents through the Exivity API, the Chrome browser needs to be installed and the directory where chrome.exe can be found should be made available in the systems Path environmental variable. Read how to add a directory to the Path environmental variable.

Depending on your installation method chrome.exe should be installed in one of those directories:

C:\Program Files\Google\Chrome\Application
C:\Users\{username}\AppData\Local\Google\Chrome\Application

To prevent server overload, generating PDF reports through the API is limited to take 1 minute. Very large reports (e.g. consolidated reports with instance details) may take more time and result in an API error. If that's the case, try generating reports for single accounts or without instance details.

Restart the Exivity API service after making these changes.

Single-node

Exivity can be easily deployed on any system running Windows Server 2016 or later.

Single Node System Architecture

In order to deploy a single node Exivity system, be aware of the following system architecture:

All components can be installed on a single node. However, even in a single node deployment, it might be desirable to run a the PostgreSQL database on a diffent system. Apart from installing PostgreSQL from the Exivity installer, ths Exivity software is also compatible with any PostreSQL compatible database engine (PostgreSQL on Linux, Amazon RDS, CockroachDB, etc) in order to achieve High Availability or Load Balancing.

Deploying Exivity on a Single Node

After starting the Exivity installer and providing a valid license, ensure to have all components selected and click Next:

Then make sure to provide a valid path for the Exivity program files:

And select a folder for the Exivity home files:

It is recommended to configure a dedicated volume with SSD performance for the Exivity home folder location

Provide a custom administrator username and password, or leave the default:

Then provide the details for the PostgreSQL database. The installer will configure a local PostgreSQL database by default:

When installing PostgreSQL on a single node host, it is recommended to use a dedicated volume for the PostgreSQL database folder (refered to as 'PGDATA')

In case you prefer to use a remote PostgreSQL database system, ensure to deselect 'Install Local PSQL Engine' and provide your remove PSQL server credentials:

When using a PostgreSQL database on a remote host, the database and user must have been created beforehand. To create the database, ask your database administator to execute a database create statement similar to the one below:

CREATE DATABASE exdb WITH OWNER = exadmin TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;

When you are finished configuring your PostgreSQL database settings, click the Install button to start the installation process.

Once the installation is finished, ensure to check 'Start the Exivity Windows Services' to start the Exivity services after clicking Finish.

Executing a Single Node Silent Installation

Follow below steps to execute an unintended setup using the silent installer flags supported by the setup program.

1 - installing Exivity on a single node with custom PostgreSQL data path

The below example will silently install Exivity with mostly default settings, except having the Exivity program, home and PGDATA in seperate locations:

Exivity_{version}_setup.exe /S /EXIVITY_PROGRAM_PATH=C:\Exivity\Program /EXIVITY_HOME_PATH=D:\Exivity\home /PGDATA=E:\PostgreSQL\PGDATA

2 - installing Exivity on a single node using a remote PostgreSQL database

The below example will silently install Exivity while using a remote PostgreSQL database:

Exivity_{version}_setup.exe /S /EXIVITY_PROGRAM_PATH=C:\Exivity\Program /EXIVITY_HOME_PATH=D:\Exivity\home /PGUSER=exivityadmin /PGPASSWORD=S3cret!123 /PGHOST=db.exivity.local /PGPORT=5432 /PGDB=exivitydb /PSQL_INSTALLED=0

Multi-node

Exivity can be deployment on a single node, or on multiple nodes for HA and load balancing. This guide walks you trough the steps to install some of the Exivity components on different nodes.

Multi Node System Architecture

The following diagram outlines the various components that can be deployed on seperate nodes:

PostgreSQL DB
- Apart from installing PostgreSQL from the installer, Exivity is compatible with any PostreSQL compatible database engine (PostgreSQL on Linux, Amazon RDS, CockroachDB, etc) in order to achieve High Availability
API/Backend
- The API and backend components can be installed on any Windows Server. To achieve HA, it is possible to leverage HA techniques from the underlying hypervisor. Or optionally it is possible to deploy Exivity on an Active/Passive Windows cluster
Web/UI
- The Exivity Web application and front end facing component can be deployed on as many instances as you like. In case multiple Web UI instances are required, this will require a load balancer in front of the Exivity Web application. Please note that the load balancer component is not shipped with the Exivity installer. However, Exivity is compatible with most load balancer software suits available (i.e. Nginx, Cisco, F5, AWS ELB, etc)

Deploying a PostgreSQL node

Exivity highly recommends to deploy your own PostgreSQL database cluster on Linux (or use a managed PSQL service from Azure, AWS or other vendors). To achieve High Availability, any PostgreSQL compatible cluster manager software may be used. At Exivity we have good experiences with pg_auto_failover, which is an open source PostgreSQL on Linux cluster manager (backed by Microsoft) in case you prefer to self-manage the Exivity PostgreSQL database.

CREATE DATABASE exdb WITH OWNER = exadmin TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;

In case you prefer to install PostgreSQL on Windows, Exivity recommends to install the PostgreSQL role together with the API/Scheduler backend components during the installation wizard.

Depoying an API/backend

In order to deploy a API/Backend node, the following steps needs to be executed manually or automatically thru the use of the silent installer CLI interface.

After starting the installer, click Next and provide a valid license key when asked. After clicking Next again, the component screen will be shown:

Ensure to deselect the Web Service component.

Click Next to continue. Then chose a folder for the Exivity program files, and afterwards select a folder for the Exivity home files.

Provide a custom administrator username and password, or leave the default:

Now specify a remote PostgreSQL database instance, or select to install the PostgreSQL database locally on the API/backend node:

When you are finished configuring your PostgreSQL database settings, click the Install button to start the installation process.

Once the installation is finished, ensure to check 'Start the Exivity Windows Services' to start the Exivity services after clicking Finish.

In order to achieve High Availability for the Exivity API/backend node, it is advisable to leverage the HA capabilities of your hypervisor (i.e. vSphere HA). In case you want to achieve OS level HA, you may consider implementing a Windows Failover Cluster. Please reach out to support@exivity.com / support.exivity.com to learn more about this kind of configuration.

Silently install API/Backend node

The below example will silently install an Exivity API/Backend node while using a remote PostgreSQL database instance:

Exivity_{version}_setup.exe /S /PGUSER=exivityadmin /PGPASSWORD=S3cret!123 /PGHOST=db.exivity.local /PGPORT=5432 /PGDB=exivitydb /PSQL_INSTALLED=0 /SCHEDULER_INSTALLED=1 /API_INSTALLED=1 /WEB_INSTALLED=0

Deploying a Web/UI node

In order to deploy a Web/UI only node, the following steps needs to be executed manually or automatically thru the use of the silent installer CLI interface.

After starting the installer, click Next and provide a valid license key when asked. After clicking Next again, the component screen will be shown:

Click Next to continue. Then chose a folder for the Exivity program files, and afterwards select a folder for the Exivity home files.

In the following screen, it will be required to specify the remote host and port of your Exivity API node:

Ensure that the Exivity API host is active and accepting connections. After clicking the Next button, the installer will issue a connection attempt to the Exivity API host

Once the installation is finished, ensure to check 'Start the Exivity Windows Services' to start the Exivity services after clicking Finish.

Silent install Web/UI node

The below example will silently install an Exivity Web/UI node:

Exivity_{version}_setup.exe /S /EXIVITY_PROGRAM_PATH=C:\Exivity\Program /EXIVITY_HOME_PATH=D:\Exivity\home /PSQL_INSTALLED=0 /SCHEDULER_INSTALLED=0 /API_INSTALLED=0 /WEB_INSTALLED=1 /PROXIMITYHOST=remote.api.local /PROXIMITYPORT=443

Azure Market Place

Introduction

Apart from installing Exivity in any on premise environment, Exivity can also be deployed from the Azure Market Place (AMP). Deploying Exivity on AMP is straight forward, and can be finised within a few minutes via your Azure Portal.

Azure Marketplace Offering

Once you've selected the Exivity offering, you should be presented with the following screen:

After clicking the Create button, you will be redirected to the VM deployment wizard

Deployment Wizard

Fill in a Windows user/pass and pick your deployment Resource Group:

Make sure to write down this username and password, as you will need these when connecting to the Exivity Windows server using the Remote Desktop Protocol.

Try to pick a recommended VM size type that has enough CPU's and Memory (see this page for general system requirements). Smaller machines are possible, but will influence performance:

You may select any additional options, but none are required for running Exivity succesfully, so you may skip this page simply by clicking the OK button:

Review the summary and click Create to deploy your Exivity VM:

This may take a few minutes. You may review the status of the Virtual Machine in your VM list:

Write down the Public IP address once it is available. Optionally you may configure a custom DNS name to have an easy way to connect.

Connecting to your Exivity instance

You can logon to your Exivity instance with RDP, but after deployment you should be able to connect to your instance using the public IP address or DNS name of your Exivity instance considering the following default URL:

https://<Your_Public_IP>:8001

The default admin username is admin with password exivity.

By default no data is loaded into the system, so you'll have to create a new Extractor for obtaining consumption data and a Transformer to process that data. A Report Definition is then created to be able to report on your consumption metrics and costs.

Next steps

A couple of getting started guides are provided here, but feel free to drop us an e-mail or create a ticket in our support portal. We will then assist you to get your started for your specific use case.

AWS Market Place

Introduction

Exivity can be deployed from the AWS Market Place allowing you to have a functional Exivity solution in a matter of minutes. This tutorial will get you up and running.

AWS Marketplace Offering

Click on Continue to Subscribe.
Read our Terms and Conditions, and when ready, click on Continue to Configuration.
Select the Region where you want to deploy Exivity and click on Continue to Launch.
In Choose Action select Launch though EC2 and click on Launch to access the Deployment Wizard.

Deployment Wizard

In the first screen, try to pick a recommended VM size type that has enough CPU's and Memory (see this page for general system requirements). When you are done with your selection click on Next: Configure Instance details.

In this section, you can select your VPC Configuration, or leave the default values.When you are done with your configuration click on Next: Add Storage.
In the Storage section, two drives are recommended, you can influence the Volume Type and the Size (GiB) parameters. When ready, click on Next: Add Tags.
In the Tags section, include the tags that are meaningful to you, as a minimum, a Name tag is recommended. Click on Next: Configure Security Group.

In the Security Group section, you can leave the default recommended security group or add more rules if needed. Click on Review and Launch.
Review the details and click on Launch, select your preferred Key Pair to connect to the instance.

In a few minutes your instance will be deployed, you can track the progress in your EC2 Dashboard:

Write down the Public IP address / Public DNS and the Instance ID once they are available.

Connecting to your Exivity instance

https://<Your_Public_DNS>:8001

The default admin username is admin with password Instance ID.

Next Steps

Introduction

Reports

The graphical user interface of Exivity is a pure client-side application, which means it runs inside your web browser. It communicates with the Exivity REST-API to obtain data records, report data and general configuration. This means all functionality available in the GUI can also be accessed programmatically.

The interface allows you to do the following:

Develop extractors
Create transformer (ETL) tasks
Configure report definitions
Run graphical usage & costs reports
Run textual usage & costs reports
Manage users & roles
General configuration
White labeling

More features are added on a regular basis.

Services

An introduction to Services

In Exivity Services can be anything that corresponds to a SKU or sellable item from your Service Catalogue. It should relate to a consumption record (or multiple records) from your extracted data sources.

For example: with most public cloud providers, the provider defines the chargeable items that are shown on the end of month invoice. However, when working through a Managed Services Provider, a Cloud Services Provider or a System Integrator, additional services can be sold on top of those. Potentially, you may want to apply an uplift to the rate or charge a fixed amount of money every month for a certain service. Different scenario's are possible here, it all depends on your business logic.

Terminology

A service is a named item with associated rates and/or costs used to calculate a charge that appears on a report, where rates represent revenue and costs represent overheads.

When discussing services and their related charges a number of terms are required. Exivity uses the following terminology in this regard:

Creating service definitions

When created during the ETL process, service definitions are created via the service and services statements in Transcript. During the execution of a Transcript task, service definitions created by these statements are cached in memory. Once the task has completed successfully, the cached services are written to the global database where they remain indefinitely (or until such time as they are manually deleted).

If the task does not complete successfully then the service definitions cached in memory are discarded, the expectation being that the task will be re-run after the error condition that caused it to fail has been rectified and the services will be written to the global database at that time.

Types of charges

There are different types of charge that can be associated with a service. Collectively these influence the total charge(s) shown on the report and Exivity supports the following charge types as described in the Terminology table above:

unit rate
fixed price
COGS rate
fixed COGS

At least one of these charge types must be associated with a service definition. Multiple combinations of the charge types may also be used.

Once the resulting charge has been calculated based on the charge types, it may be further modified through the application of adjustments, proration and minimum commit (all of which are detailed later in this article).

Charge intervals

In order to calculate the charge(s) associated with usage of a service Exivity needs to know the period of time for which each payment is valid. For example a Virtual Machine may have a daily cost associated with it, in which case using it multiple times in a single day counts as a single unit of consumption whereas Network Bandwidth may be chargeable per Gigabyte and each gigabyte transferred is charged as it occurs.

The charge interval (also termed simply interval) for a service can be one of the following:

individually - the charge for a service is applied every time a unit of the service is consumed, with no regard for a charging interval
daily - the charge is applied once per day
monthly - the charge is applied once per calendar month

Although hourly charge intervals are not currently supported directly, it is possible to charge per hour by aggregating hourly records and using the EXIVITY_AGGR_COUNT column created during the process to determine the units of hourly consumption as a result.

Charge models

Monthly services may be charged in one of two ways:

Peak

For each day of the month, a 'candidate' charge is calculated using Quantity * Unit Rate . The monthly charge will reflect the day of the month which resulted in the highest charge.

In the event that multiple days share the same highest charge then that charge will be associated with the first of those days seen, unless a subsequent day in that set has a higher quantity, in which case the charge will be associated with that subsequent day.

Average

The average unit rate for those days where usage was seen in the month is calculated, and multiplied by the average quantity for each day in the month. When calculating the average quantity, any days for which there was no consumption are factored in as having a quantity of 0.

If Average Charging is applied in combination with proration, then the resulting average unit price as shown on reports may be less than you expect to see. This is because the average unit price as shown on the reports is calculated using charge / average_quantity and proration will reduce the charge if there was no consumption on one or more days of the month, resulting in a lower average unit price.

Minimum commit

The minimum commit is the minimum number of units of consumption that are charged every interval, or (in the case of services with an interval of individually) every time the service is used. If fewer units than the minimum commit are actually consumed then the service will be charged as if the minimum commit number of units had been used.

Proration

After the charge for usage of a monthly service has been determined, it may be prorated by modifying that charge based on the frequency of the usage.

This process will reduce the charge based on the number of days within the month that the service was used. For example if consumption of a service with a monthly charge interval was only seen for 15 days within a 30 day calendar month then the final charge will be 1/2 of the monthly charge.

Service definitions

A service definition comprises two categories of information:

The service - Metadata describing fixed attributes of the service such as its name, description, group, interval, proration and charge type(s)
The rate revision - Information detailing the charge type(s) associated with the service (the rate, fixed price, COGS rate and fixed COGS values) and additional information detailing the date(s) for which those values should be applied

A service definition is associated with a specific a DSET as the units of consumption are retrieved from a column (named in the service definition itself) in usage data.

The following tables summarize the data members that comprise each of these categories:

Service attributes

RDF, rate_type and cogs_type are automatically derived from the parameters provided to the service and services statements

Rate revision attributes

The rate_col, fixed_price_col, cogs_col and fixed_cogs_col fields are used when the specific value to use is derived at report-time from the usage data, as opposed to explicitly being included in the rate revision itself.

A service may have any number of associated rate revisions so long as they have different effective_date or minimum commit values. This means that a service can have different charges applied depending on the date that the report is to be generated for, or depending on the specific values in the columns used by a report.

A service may use neither, either or both of rate and fixed_price, and neither or one of cogs and fixed_cogs. At least one of rate, fixed_price, cogs or fixed_cogs is required, but cogs and fixed_cogs may not both be used.

Any or all of rate, fixed_price, cogs and fixed_cogs may have a value of 0.0, in which case no charges will be leveraged against the service but the units of consumption will still be shown on reports.

Tutorials

For some specific use cases, we've created tutorials to get started.

Amazon AWS CUR

Pre-requisites

This tutorial assumes that you have CUR (Cost and Usage Report) set up in your AWS environment. In the event that this is not the case please follow the steps in Turning on the AWS Cost and Usage Report before proceeding.

Please note that we also need the following requisites:

S3 bucket where the CUR reports are placed
The AWS region of the S3 bucket
The path where the reports are placed inside the S3 bucket

To get this requirements you can go to the Amazon S3 service - Buckets and note the name and the region of our CUR S3 bucket

Once inside the CUR bucket, you must find the path where the monthly reports reside, please navigate the bucket until you find a folder like this one and note the path:

Obtaining the API Credentials

We have to create an IAM User that has read access to the CUR bucket. Please follow these steps:

Go to IAM - Policies and click on Create policy.

In the next screen: Service select S3, Actions select Read, Resources select Specific and in bucket click on Add ARN.

In the next screen, type the name of your bucket followed by /* in Bucket name (example: billing-bucket/*) and click on Add.
Click on Review Policy.
Give a Name and a Description to the policy and finally click on Create policy.

Now, we will attach the policy to a user:

Go to IAM – Users – Add user.

Add an User name and select Access type: Programmatic access.

Click on Attach existing Policies directly and filter by the name of the policy you just created.
Select the policy and click on Next:Tags.
Add a Key-Value pair for the Tags and click on Next:Review.
Click on Create User.
In the next screen click on Download .csv we will use this credentials to populate our Exivity Extractor in the next section.
Finally click on Close.

Configure Extractor

To create the Extractor in Exivity, browse to Data Sources > Extractors and click the Create Extractor button. This will try to connect to the Exivity Github account to obtain a list of available templates. For AWS, please click AWS_CUR_Extractor from the list. Provide a name for the Extractor in the name field, and click the Create button.

Once you have created the Extractor, go to first tab: Variables

Bucket: Type the name of your S3 CUR bucket.
AWS region: Name of your S3 CUR bucket region code you can find them here (column Region).
Access key: You can find it in the csv that you have downloaded in the previous section.
Secret key: You can find it in the csv that you have downloaded in the previous section.
Cur report: Type the name of your CUR report.
Bucket Directory: Type the path that you got in the previous section (if there is more than one folder separate them with /).

Once you have filled in all details, go to the Run tab to execute the Extractor for a single day:

The Extractor requires one parameters in yyyMMdd format:

from_date is the date for which you wish to collect consumption data.

When you click the Run Now button, you should get a successful result.

Configure Transformer

Once you have successfully run your AWS CUR Extractor, you should be able to create a Transformer template via Data Sources > Transformers and click the Create Transformer button. Select the AWS CUR Transformer and run it for a single day as a test. Make sure that it is the same day as for which you extracted consumption data in the previous step.

Create Report

Once you have run both your Extractor and Transformer successfully create a Report Definition via the menu option Report Definition via the menu Reports > Definitions:

Once this is done you should be able to run any of Accounts, Instances, Services or Invoices report types located under the Report menu for the date range you prepared the report for.

Amazon AWS CUR (Athena)

This Tutorial is for the AWS CUR Athena Extractor if you want to use the standard AWS CUR Extractor please use this tutorial.

Pre-requisites

Please note that in order to deploy this solution the S3 bucket to which CUR reports are written must reside in one of the following AWS regions:

Northern Virginia
Ohio
Oregon
Mumbai
Seoul
Singapore
Sydney
Tokyo
Frankfurt
Ireland
London

At this point in time, only the previous regions have all the necessary services deployed.

Introduction

This tutorial shows how to build a serverless solution for querying the AWS CUR Report using Exivity. This solution makes use of AWS serverless services such as Lambda and Athena, as well as other commonly used services such as S3, CloudFormation, and API Gateway. The following topics will be covered:

Solution Overview
Launching the CloudFormation Template
Creating the Lambda function and API Gateway
Configuring an Extractor
Configuring a Transformer
Creating your Report

Solution Overview

The Billing and Cost Management service writes your AWS Cost and Usage report to the S3 bucket that you designated when setting up the service. These files can be written on either an hourly or daily basis.

The CloudFormation template that accompanies this tutorial builds a Serverless environment containing a Lambda function which reads a CUR file, processes it and writes the resulting report to an output S3 bucket. The output data object has a prefix structure of "year=current-year" and "month=current-month". For example, if a file is written 13/09/2018 then the Lambda function outputs an object called "bucket-name/year=2018/month=09/file_name".

The next step in the template is to translate this processed report into Athena so that it can be queried. The following diagram shows the steps involved in the process:

Afterwards, we will create a Lambda function to query the Athena database, returning a URL with the results of the query in CSV format. We will also create an API EndPoint with the AWS API Gateway service, which is used by Exivity to retrieve the data.

Launching the CloudFormation template

In order to deploy this solution successfully the following information is required:

The name of your AWS Cost and Usage report.
The name of the S3 bucket in which the reports are currently stored.

Firstly, launch the CloudFormation template that builds all the serverless components that facilitate running queries against your billing data. When doing this, ensure that you choose the same AWS Region within which your CUR S3 bucket is located.

Now follow the instructions in the CloudFormation wizard, using the following options, and then choose Create.

For CostnUsageReport, type the name of your AWS Cost and Usage report.
For S3BucketName, type a unique name to be given to a new S3 bucket which will contain the processed reports.
For s3CURBucket, type the name of the bucket into which your current reports are written.

While your stack is building, a page similar to the following is displayed.

When the Status column shows CREATE_COMPLETE, you have successfully created four new Lambda functions and an S3 bucket into which your transformed bills will be stored.

Once you have successfully built your CloudFormation stack, you can create a Lambda trigger that points to the new S3 bucket. This means that every time a new file is added to, or and existing file is modified in, the S3 bucket the action will trigger the lambda function.

Create this trigger using the following steps:

Open the Lambda console.
Choose Functions, and select the aws-cost-n-usage-main-lambda-fn-A Lambda function (note: do not click the check box beside it).
There should be no existing triggers. Choose Trigger, Add trigger.
For Trigger type (the box with dotted lines), choose S3.

Select the S3 bucket withing which your CUR reports are stored.
For Event type, choose Object Created (All) and check Enable trigger.
Click Submit.

The database and table are not created until your function runs for the first time. Once this has been done, Athena will contain the database and table.

Athena stores query results in S3 automatically. Each query that you run has a results file in CSV format and a metadata file (*.csv.metadata) that includes header information such as column type, etc.

Testing (Optional)

Once you have successfully added the trigger to the S3 bucket in which the Billing and Cost Management services writes your CUR reports, test the configuration using the following steps.

In the S3 path to which AWS writes your AWS Cost and Usage Billing reports, open the folder with your billing reports, open the folder with your billing reports. There will be either a set of folders or a single folder with a date range naming format.

Open the folder with the data range for the current month. In this folder, there is a metadata file that can be found at the bottom of the folder. It has a JSON extension and holds the S3 key for the latest report.
Download the metadata file. Ensure that the name of the file on your machine is the same as the version stored on your S3 bucket.
Upload the metadata file to the same S3 path from which you downloaded it. This triggers the Lambda function aws-cost-n-usage-main-lmbda-fn-A.
In the S3 bucket that you created to hold your processed files, choose the "year=" folder and then the "month=" folder that corresponds to the current month. You should see the transformed file there, with the time stamp that indicated that it was just written.

Creating the Lambda function and API Gateway

To automate this process a CloudFormation template will be provided. This template will create an IAM role and Policy in order that our API can invoke Lambda functions. Then it will create a Lambda function with the capabilities of querying our previously created Athena Serverless DB, and save the output in a S3 bucket in .csv format, (this output will be later retrieved by Exivity). Finally, it will deploy an API Gateway allowing us to create an endpoint for our Lambda function, this is the endpoint that the Exivity extractor will consume. Make sure to launch the CloudFormation template in the same region that you have deployed the previous one.

Let's start by downloading the CloudFormation template (you only need to choose one of the formats, both are supported by AWS):

Then follow the next steps:

Go to the CloudFormation console.
Choose Create Stack.
Choose Upload a template to Amazon S3.
Select from your computer the template that you have downloaded.
Follow the CloudFormation wizard - Add a Name to the Stack and select I acknowledge that AWS CloudFormation might create IAM resources with custom names in the last step.
Once the stack is created you should see an CREATE_COMPLETE message.

Click on Output to take a note of your endpoint (you will need to input this in the Exivity extractor).

Next, we will associate an API Gateway trigger to our Lambda function:

Go to the Lambda console.
Choose the QueryAthena2 function.
Under Add Triggers select API gateway. You should see an image like the following:

Click on API Gateway figure to configure it.
On API select QueryAthena2.
On Deployment Stage select v1.
On Security select Open.
Choose Add.
Choose Save.

You should see a screen like this:

Finally, we will deploy the API Gateway:

Go to the API Gateway console.
Choose QueryAthena2.
In the Resources section, click on the ANY method.
In Actions, choose Delete Method.
Click on Delete.

In the Resources section, choose Actions.
Click on Deploy API

In Deployment Stage select V1.
Add a Deployment Description.
Choose Deploy.

Securing the API Gateway

Initially, the created API endpoint is public and as such is vulnerable to the possibility of misuse or denial-of-service attacks. To prevent this, associate an API Key with the endpoint as per the following steps:

Inside the API Gateway dashboard, select the QueryAthena2 API
In Resources, select Method Request
In Settings, change API Key Required to True

Click on Actions and choose Deploy API to effect the change
In Deployment Stage, select v1 and click on Deploy
Go to the API Keys section

Click on Actions and select Create API Key
In Name write ExivityAPIKey
Click on Save
Copy the API Key, as this will be required by the Exivity configuration

Go to Usage Plan
Click on Create.
In Name write ExivityUsagePlan
In the Throttling Section, change Rate to 100 and Burst to 10
In the Quota Section, change it to 50000 requests per Month
Click on Next
Click on Add API Stage
In API, select QueryAthena2 and in Stage select v1

Confirm the changes and click on Next
Click on Add API Key to Usage Plan
Select ExivityAPIKey, confirm the changes
Click on Done

The API Key is now required to access the API endpoint thus adding a layer of security to mitigate unauthorized access attempts.

Configure Extractor

Once you have created the Extractor, go to first tab: Variables

In the Bucket variable specify the name of the S3 bucket where the .csv with the output of the query will be saved (The S3BucketName previously specified when launching the CloudFormation template).
In the Api endpoint variable specify the API endpoint previously created plus the route /QueryAthena.
In the DBname variable specify the name of your DB, you can find it in the Athena main Dashboard.
In the Tablename variable specify the name of the table inside your DB, you can find it in the Athena main Dashboard.
In the API_Key variable specify the API Key that we have created in the Securing API Gateway Section.

Once you have filled in all details, go to the Run tab to execute the Extractor for a single day:

The Extractor requires two parameters in yyyMMdd format:

from_date is the date for which you wish to collect consumption data.
to_date should be the date immediately following from_date.

These should be specified as shown in the screenshot above, separated with a space.

When you click the Run Now button, you should get a successful result.

Configure Transformer

Create Report

Once you have run both your Extractor and Transformer successfully create a Report Definition via the menu option Report Definition via the menu Reports > Definitions:

Select the column(s) by which you would like to break down the costs. Once you have created the report, you should then click the Prepare Report button after first making sure you have selected a valid date selector shown when preparing the report.

Azure Stack

This article describes how to report Azure Stack consumption with Exivity

Introduction

When deploying the Azure Stack Extraction template for Exivity, some configuration is required within your Azure Stack environment and a lookup file needs to be created. The following process must be completed in order to report on Azure Stack consumption:

Create an Exivity Enterprise Application in your Azure AD for authentication
Configure a rate card lookup file
Configure an Extractor
Configure a Transformer
Create your Report

Creating an Enterprise Application

In order for Exivity to authenticate with Azure Stack, you will need to create an application in the Azure AD where you have registered your Azure Stack management node:

Make sure to write down the Application ID and its corresponding Secret, since you will need these when configuring the Extractor later.

When you create this application in your Azure AD make sure it has (at least) the Reader Role in your Default Provider Subscription:

Create a rate card

As Microsoft does not provide rate card information via the Azure Stack Consumption API you will only obtain usage metrics from Azure Stack for all of the Meter IDs that are mentioned here by Microsoft.

Exivity provides a template rate card that you can use for creating your own rates. Please bear in mind that these rates are fictional, thus you should update it with your preferred values. However to get started, you can use the file linked above by placing it as a csv file in your Exivity home folder at the following location:

%EXIVITY_HOME_PATH%\system\extracted\AzureStack\rates\azure_stack_example_rates.csv

Once loaded into the system using a Transformer you will be able to change the rates easily through the GUI. This will also enable you to test any draft rates.

Configure Extractor

To create the Extractor, browse to Data Sources > Extractors in the Exivity GUI and click the Create Extractor button. This will try to connect to the Exivity Github account to obtain a list of available templates. For Azure Stack:

Pick Azure_Stack_Extractor_(App+Secret) from the list
Provide a name for the Extractor in the name field
Click the Create button.

Once you've created the Extractor, go to first tab: Variables

Fill in all required variables marked within the red box in above screenshot. If you don't know some of the required GUIDs, most of these can be obtained by browsing to the Azure Stack management node URL:

https://adminmanagement.**<your.domain.com>**/metadata/endpoints?api-version=2015-01-01

Another way to obtain some of this information is using the Diagnostics button in your management portal:

When you click the Show Diagnostics link, it should download a JSON file containing most of the parameters you'll need, such as Provider GUID, Audience GUID etc.

Once you've filled in all details, go to the Run tab to execute the Extractor for a single day:

The Extractor requires two parameters in yyyyMMdd format:

from_date is the date for which you wish to collect consumption data
to_date should be the date immediately following from_date

These should be specified as shown in the screenshot above, separated with a space.

When you click the Run Now button, you should get a successful result.

Configure Transformer

Once you have successfully run your Azure Stack Extractor, you can create a Transformer template via Data Sources > Transformers in the Exivity GUI. Browse to this location and click the Create Transformer button. Make any changes that you feel necessary and then select the run tab to execute it for a single day as a test.

Make sure that when running the Transformer you select custom range in the drop-down menu labelled Run for and select the same day as for which you have extracted consumption data in the previous step.

Create a Report

Once you have run both your Extractor and Transformer successfully create a Report Definition via the menu option Reports > Definitions:

Select the column(s) by which you would like to break down the costs. Once you have created the report, you should then click the Prepare Report button after first making sure you have selected a valid date range from the date selector shown when preparing the report.

Once this is done you should be able to run any of Accounts, Instances, Services or Invoices report types located under the Report menu for the date range you prepared the report for.

Azure EA

Introduction

When deploying the Azure EA Extraction template for Exivity, some configuration is required within your Azure EA environment. The following process must be completed in order to report on Azure EA consumption:

Create an Access Key and Secret in your Azure EA portal
Configure the Azure EA Extractor
Configure your Azure EA Transformer
Create a Report definition

Creating an Access Key & Secret

In order for Exivity to authenticate with Azure EA, you will need to create an access key and secret in the Azure EA Portal. Also you will need to find your enrollment number. To do this, login to your Azure EA Portal on https://ea.azure.com and navigate to the Reports menu:

Just under the Windows logo in the left menu section you will find your enrollment number which you will need to provide later when configuring the Extractor. To create the Access Key & Secret, click on Download Usage and then on API Access Key. This brings you to the menu where you can manage your Access Keys and corresponding secret which you will need in order to configure the data Extractor.

Configure Extractor

Pick Azure EA from the list
Provide a name for the Extractor in the name field
Click the Create button.

Once you've created the Extractor, go to first tab: Variables

Fill in all variables in above screenshot, and feel free to encrypt any sensitive data using the lock symbol on the right.

Once you've filled in all details, go to the Run tab to execute the Extractor for a single day:

The Extractor requires two parameters in yyyyMMdd format:

from_date is the date for which you wish to collect consumption data
to_date should be the date immediately following from_date

These should be specified as shown in the screenshot above, separated with a space.

When you click the Run Now button, you should get a successful result.

Configure Transformer

Once you have successfully run your Azure EA Blob Extractor, you can create a Transformer template via Data Sources > Transformers in the Exivity GUI. Browse to this location and click the Create Transformer button. Make any changes that you feel necessary and then select the run tab to execute it for a single day as a test.

Create a Report

Once you have run both your Extractor and Transformer successfully create a Report Definition via the menu option Reports > Definitions:

Azure CSP

Introduction

When deploying the Azure CSP Extraction template for Exivity, some configuration is required within your Microsoft Cloud Solution Provider Potal. The following process must be completed in order to report on Azure CSP consumption:

Create a Partner Center Web Application
Configure Extractors for Azure CSP Usage, Billing & Ratecard
Configure Transformers
Create your Report
Create your Workflows

It is necessary to create independent Extractors/Transformers for the Usage and Billing, the Usage Extractors will be retrieving the data on a daily basis giving an estimation of your daily costs. The Billing Extractor will consolidate the rates based on the blended costs per service for the billing period.

Create a Partner Center Web Application

Perform the following to create the Azure AD configured to access the Partner Center API

Browse to Partner Center, https://partnercenter.microsoft.com, and login using credentials that have admin agent and global admin privileges
Click on the following Dashboard –> Account Settings –> App Management

Click Add key to create a new Application Key for you App ID that can be used with Exivity.

Make sure to write down the App ID and its corresponding Key, since you will need these when configuring the Extractor later.

Go to the Billing section.
Open the last month's invoice in pdf format.
Take note of your billing period

Make sure to write down the billing period, since you will need it when configuring the Extractor later.

Configure Extractors for Azure CSP Usage, Billing & Ratecard

Go into the Exivity GUI and browse to Data Sources -> Extractors. Then click on Create Extractor and you should get a list of templates. Unfold Azure CSP and pick the usage template:

After selecting the template, click the green Create button on the bottom right. Now make sure to give the new Extractor a name in field at the top:

Now click again on the green Create button at the bottom right. Then click on the Variables menu item:

Now make sure to fill in your Client ID, Secret and your onmicrosoft.com domain. When required, you can encrypt security sensitive field using the lock button on the right of each field. Once you are filling in these details, click the Update button.

Now test the Extractor by going into the Run tab and providing a from and to date like in this example:

Now click the Run Now button and confirm it works as expected:

Create a second extractor using the template Azure_CSP_Invoice_Extractor and give it a name.

This extractor uses the same variables in the Variables menu item as the previous extractor. You can now test the extractor by going to the Run tab, this script uses 3 arguments:

Positive offset: Starting on 0, it will retrieve the most recent invoice, a 1 will retrieve the previous report, etc.
Year of the report: Year of the report you want to retrieve.
Starting day of the billing period: If your billing period goes from 22nd to 21st, the input will be 22.

After filling the arguments you can test the extractor by clicking in Run Now.

Finally, follow the same steps for the Azure Rate Card Extractor, this extractor does not need any arguments.

Configure Transformers

Once you have successfully run your Azure CSP Usage, Billing & Rate Card Extractors, you can create the Transformers templates via Data Sources -> Transformers in the Exivity GUI. Browse to this location and click the Create Transformer button. You will need to create two separate transformers using these two templates:

The Azure_CSP_Daily-Usage Transformer will transform the daily usage data and the Azure_CSP_End-of-Month Transformer will consolidate the usage with the final blended rates.

Make any changes that you feel necessary and then select the Run tab to execute it for a single day as a test.Make sure that when running the Transformer you select custom range in the drop-down menu labelled Run for and select the same day as for which you have extracted consumption data in the previous step.

Create a Report

Once you have run both your Extractor and Transformer successfully create a Report Definition via the menu option Reports > Definitions:

Create your Workflows

You may want to automate the CSP ETL process, you can achieve it by leveraging on the Exivity's Workflow capabilities. You will create two Workflows, one will run on a daily basis calculating the usage of your CSP subscriptions and the other one will run on a monthly basis to consolidate the service rates.

Start by browsing to Administration -> Workflows in the Exivity GUI and click on the +Create button.

Fill the Name and Description fields, in the SCHEDULES section configure the workflow to run on a daily basis at a convenient time.

In the STEPS section you can create as many steps as needed by adding them with the + button. For the first daily workflow, a minimum of 4 steps are required, two steps for the Usage and Ratecard extractors, one for the Transformer and one for the Report. Make sure to input the right FROM and TO date offsets. Click on Update to finish the creation of your first Workflow.

Create a second Workflow, fill the Name, and Description fields and configure the SCHEDULES section to run on a monthly basis, preferably 2 to 3 days after your month's billing period has finished. For the monthly workflow, a minimum of 3 steps are required, one step for the Billing extractor, one for the Transformer and one for the Report. Make sure to input the right FROM and TO date offsets (to cover the entire billing period) and arguments. Click on Update to finish the creating of the second Workflow.

Google Cloud

This article describes how to setup Exivity to report on Google Cloud consumption.

Pre-requisites

This tutorial assumes that you have a Billing Account for your Google Cloud Projects in place. In the event this is not the case please follow the steps in this tutorial before proceeding.

Setting up BigQuery

Exivity will leverage on the GCP BigQuery service. This will enable the export of detailed Google Cloud billing data to a BigQuery dataset, giving Exivity the capability to query the billing data in a more granular manner than with the standard file export (which is going to be deprecated by Google).

Before proceeding further, make sure sure your user has the Billing Account Administrator and the BigQuery User roles associated

First and foremost, Exivity will create a dataset containing the billing table which will be periodically queried. Please follow these instructions:

Go to the BigQuery service page.
Select the project that will contain your dataset in the project drop down.
Click CREATE DATASET.
1. Enter a Dataset ID
2. Select Data location (region)
3. Set Default table expiration to Never
4. Set Encryption to Google-managed key
Finally, click Create dataset
Note the Project, BigQueryProject and Table, these parameters will be used by Exivity on a later stage.

Once you have the dataset created in BigQuery you need to enable Cloud Billing export to BigQuery:

Sign in to the Cloud Billing Console, select Organization and main Billing Account.
Go to the Billling Export tab.
Click Edit Settings to enable the export.
1. Select the project where you previously created the BigQuery dataset.
2. Select the specific dataset from the Billing export dataset list.
3. Click Save.

Creating Exivity Service Account

Exivity requires a GCP Service Account with the BigQuery User role asscociated in order to retrieve the billing data. Please follow this manual to create a Service Account, this manual to associate a Private Key with the service account, and finally this manual to associate the role with the service account.

Make sure to write down the Mail address / service account and its associated Private Key, as these parameters will be required for the Exivity Data Extractor

Configuring the Extractor

To create the Extractor in Exivity, browse to Data Sources > Extractors and click the Create Extractor button. This will try to connect to the Exivity Github account to obtain a list of available templates. For Google Cloud, please click Google_Cloud from the list. Provide a name for the Extractor in the name field, and click the Create button.

Once you have created the Extractor, go to Variables tab and fill the parameters:

Hostname: Default endpoint for Google BigQuery with version
Private: Provide the RSA private key PEM format

IMPORTANT make sure to replace \n with ${NEWLINE} in the RSA Private key using a text editor, before pasting the key in the Exivity Extractor field

Email: Mail address / service account associated with the private key, obtained in the Creating Exivity Service Account section.
Project: Main GCP Project name.
BigQuery project: GCP BigQuery Billing Project, obtained in the Setting up BigQuery section.
BigQuery table: GCP BigQuery Billing Table, obtained in the Setting up BigQuery section.

Finally, click on Update.

Once you have filled in all details, go to the Run tab to execute the Extractor for a single day:

The Extractor requires two parameters in yyyMMdd format:

from_date is the date for which you wish to start collecting consumption data.
to_date is the end date for which you wish to collect consumption data.

When you click the Run Now button, you should get a successful result.

Configure a Transformer

Once you have successfully run your Google Cloud Extractor, you should be able to create a Transformer template via Data Sources > Transformers and click the Create Transformer button.

Select the Google Cloud Transformer that you just created, go to the Run Tab and run it for a single day as a test. Make sure that it is the same day as for which you extracted consumption data in the previous step.

When you click the Run Now button, you should get a successful result.

Create your Report

Once you have run both your Extractor and Transformer successfully create a Report Definition via the menu option Report Definition via the menu Reports > Definitions:

VMware vCloud

This article describes how to report onVMware vCloud consumption with Exivity

Introduction

When deploying the vCloud Extractor template for Exivity, some input and configuration is required from your vCloud environment. The following process must be completed in order to report on vCloud consumption:

Create Exivity vCloud user (vCloud < 9.1)
Create Exivity vCloud user (vCloud >= 9.1)
Configure an Extractor
Configure a Transformer
Create your Report

Create Exivity vCloud user (vCloud < 9.1)

For environments with a vCloud previous to 9.1 Exivity needs a user with the sysadmin role on the system ORG. Please follow this procedure:

Click the Administration tab and click Users in the left panel.
Click New, fill the required details.
Note the username and password.
Finally, click OK.

Create Exivity vCloud user (vCloud >= 9.1)

For environments with vCloud version 9.1 or higher, you can create a user with more fine grained permissions. Exivity needs a user with a reader role on the system ORG. Please follow this procedure:

First you will need to create a new custom role for the Exivity user:

From the main menu, select Administration.
In the left panel, under Access Control, click Roles
Click New.
Enter a name and, optionally, a description for the new role.
Select the rights that you want to associate with the role. Exivity needs all the View Rights in the different tabs and also Perform Administrator Queries under the General tab.

Click Save.

Once you have the Role created, you need to setup a new user and assign the Role previously created:

On the VMware Cloud Service toolbar, click the VMware Cloud Services icon and select Identity & Access Management.
Click Add Users.
On the Active Users tab, fill the details of the user you want to add to the system organization.
In the Role in organization text box, assign the role previously created.

Configure an Extractor

Pick vCloud_Extractor_AdminVM from the list
Provide a name for the Extractor in the name field
Click the Create button.

Once you've created the Extractor, next go to theVariables tab:

Fill in all required variables with the values that you gathered in the previous step. You have the option to encrypt them. Click on Update.

Once you've filled in all details, go to the Run tab to execute the Extractor clicking on Run Now:

Configure a Transformer

Once you have successfully run your vCloud Extractor, you can create a Transformer template via Data Sources > Transformers in the Exivity GUI. Browse to this location and click the Create Transformer button. Make any changes that you feel necessary and then select the run tab to execute it for a single day (today) as a test.

Create a Report

Once you have run both your Extractor and Transformer successfully create a Report Definition via the menu option Reports > Definitions.

Select your vCloud dataset, and your preferred Reporting Columns to break down the report (we recommend only Org_name and VDC for the default report). When you are ready, click on Create.

Once you have created the report, you should then click the Prepare Report button after first making sure you have selected a valid date range from the date selector shown when preparing the report.

VMware vCenter

This article describes how to report on VMware vCenter consumption with Exivity

Introduction

When deploying the vCenter Extractor template for Exivity, it is required to configure a user with approperiate permissions. Additionally, the following process must be completed in order to report on vCenter consumption:

Create Exivity vCenter user
Configure an Extractor
Configure a Transformer
Create your Report

Exivity supports Out of the Box integration with vCenter version 6.5 and higher. For version before 6.5, please contact support@exivity.com.

Create Exivity vCenter user

Exivity needs a user with a reader role in order to retrieve consumption data. Please follow this procedure:

In your vCenter Configuration Manager go to Configuration > Local Users and Groups > Users.
Create a new User, insert username and password.
Take note of the username and password, they will be used later on to configure Exivity
Click on Create.
Under Users and Groups, click Add, select the previously created user.
On the Select Users and Groups dialog, click Add, and then click OK.
Add a Reader role in the dropdown.
Finally click on OK.

Configure an Extractor

Provide a name for the Extractor in the Name field above
Pick vCenter 6.5 (VM Inventory REST API) template from the list
Click the Create button.

Once you've created the Extractor, go to theVariables tab:

Fill in all required variables with the values that you gathered in the previous step. You have the option to encrypt a variable in case it contains sensitive information (i.e. password) by clicking the lock icon on the right of each variable field. When finished, click Update.

Once you've filled in all details, go to the Run tab to execute the Extractor clicking on Run Now:

If the variables are correct and your vCenter is reachable for Exivity, you should get a successful result.

Configure a Transformer

Once you have successfully run your vCenter Extractor, you can create a Transformer template via Data Sources > Transformers in the Exivity GUI. Browse to this location and click the Create Transformer button. Make any changes that you feel necessary and then select the run tab to execute it for a single day (today) as a test.

Create a Report

Once you have run both your Extractor and Transformer successfully create a Report Definition via the menu option Reports > Definitions:

Select your vCenter dataset, and your preferred Reporting Columns to break down the report (we recommend only cluster_name for the default report). When you are ready, click on Create.

Releases

Install / upgrade

A copy of the Exivity installer can be obtained on our website. Installing or upgrading to the latest release is a straight-forward process, refer to the on-premises article for more information.

Migration to PostgreSQL within the Exivity architecture As of version 3.0.0 Exivity stores all global configuration data and report results in PostgreSQL as opposed to SQLite. This is a mandatory and breaking change. Please refer to the version 3 upgrade guide for more information.

As of version 3.2.1, quantity adjustments are applied first and before any charge related adjustments. This may potentially affect your billing records. If you were depending on mixing charge and quantity adjustments, please reach out to support@exivity.com for guidance.

Release feed

You may register with the following JSON feed to obtain information about new releases.

Changelog

v3.4.3

March 22, 2021

New features

Usage data generation based on consumption start/stop events Added functionality to produce usage data from consumption start/stop/update events, including the consumption which spans several days. See event_to_usage statement description in Transform documentation for details.

v3.4.2

February 24, 2021

Bug fixes

Fixed an issue with SAML user account access provisioning Previously, user account access provisioning would grant access to a matching account, including all its child accounts. With this release, the SAML user account access provisioning only grants a user access to the first matching account (i.e. with the lowest depth) in the account hierarchy.
Fixed an issue where reports could become invalid for certain user account permission configurations
Revolved a race condition related to preparing a report On rare occasions the actions of multiple users administering accounts and reports simultaneously could result in duplicate charges in reports. This has now been fixed.
Fixed a rare cosmetic issue when updating rates in ETL When executing a 'services' statement in a Transformer, if there was an existing service rate revision with a different rate for the same effective date as a new revision then an error containing the text "incorrect binary data format" would be logged. This issue was cosmetic, but has now been fixed.
Fixed a very rare issue with minimum commit Resolved an issue whereby it was possible on extremely rare occasions for the minimum commit calculations applied to one service to be incorrectly applied also to other services in a report.

v3.4.1

December 08, 2020

Bug fixes

Fixed an issue with PDF export of the summary report In certain environments, the PDF export feature was broken. This issue has now been resolved.
Default SAML user group re-applied when a user logs in An issue has been resolved which caused the default user group to be re-applied when existing users were logging in through a SAML Identity Provider.

v3.4.0

November 17, 2020

New features

Executing a Reports now requires a valid license

Bug fixes

Fixed a minor issue with the calendar widget
Fixed a minor issue with the SAML SLS endpoint
Fixed an issue with custom rates and subscriptions
Sometimes a subscription would not reflect changes in custom rates for a service. This has now been fixed.

v3.3.0

November 02, 2020

Due to security considerations, starting with this release, each user is bound to the logon provider set in the source attribute. E.g. if a user has their source set to 'local', they can't log in with the SAML2 or LDAP SSO provider.

New features

Improved user provisioning (beta) Added options to provision user attributes from a SAML Identity Provider/AD response. It's possible to provision the users display name, username and email address.
Added claims based account access provisioning (beta) Added options to provision users permission levels using attributes from a SAML Identity Provider/AD. It's possible to provision both the usergroup and user account access (by matching either the account key or metadata value to a SAML/AD response attribute).

Bug fixes

Change to log filenames for extractors and transformers When scheduling extractors and transformers with workflows, the selected environment is now part of the log filename to distinguish their log files when they are running at the same time.
Fixed opening curly brace detection When opening curly brace wasn't preceded by space, sometimes it wasn't properly processed. It has been fixed
Improved error message on lookups screen Fixed an issue where a user might get a confusing error message when saving a lookup file.
Fixes initializing directories in home directory There was a bug when the home directories were initially created and two directories were wrongly created in the /home directory.
Improved support for UTF-8 in usernames

v3.2.7

October 09, 2020

New features

Added support for the Safari web browser
Exivity now supports the Safari web browser
Added ability to set custom escape character in Transformer Previewer Added a dropdown to the transformer previewer where the client can select which escape character to use during Transformer preview mode

Bug fixes

Fixed an issue with the Dataset Manager Resolved regression issues regarding the display of RDF dates
Fixed an issue with report filters There was a bug if a user accidentally submits a string instead of a number on filtering 'parent_account_id' with report. This is now being fixed
Fixed an issue with displaying workflow start times in the workflow list The time column of the workflow list omitted the hour at which the workflow would run for daily and monthly workflows
Resolve global variables in "uri" statement In some statements (i.e. uri) global variables weren't properly resolved. This has now been fixed
Fixed an issue with COGS charges for services with an average monthly charge model COGS charges for monthly services that used the average charge model could be slightly lower than they should be, as the COGS rate for the first day seen was not factored into the rate averaging. This has now been fixed
Fixed an issue when managing rates An aggregation_level error could occur when updating rates. This has now been resolved.

v3.2.5

September 30, 2020

Bug fixes

Improved error message when creating invalid revision When creating a duplicate rate revision for the same effective date, an appropriate error message is now shown
Improved error message for LDAP Added a more meaningful message when unable to connect to an LDAP server
Service Category names must be unique It was possible to have duplicate service category names. This could lead to confusion and has now been resolved. The Service Category name must now be unique
Resolved a timezone offset issue for Workflows Resolved an issue where timezones were incorrectly applied when creating workflows
Resolved an issue with the Reports menu Fixed an issue where the report screen would crash after selecting multiple reports

v3.2.4

September 9, 2020

Bug fixes

Increased the boundaries of some USE arithmetical operations
The operators +=, -=, *= and /= were limited to a 32-bit range and this could cause an overflow in certain real-world applications. The range has now been increased to match that of the other arithmentical operations which is based on the range of a 64-bit floating point value: 1.7E +/- 308 (up to 15 digits).
Fixed an issue where the CSV export of the instance report could fail The CSV export of the instance report did not work when the usage column was included and some of the instances did not have any usage data
One-off subscriptions should ignore the charge_day field Only recurring subscriptions require the "charge day" field, but it was required for all subscriptions. This has now been fixed
Fixed a decimal precision issue with the timeline graph The Y-axis of the timeline sometimes displayed long floats. To resolve this the precision has been fixed to two decimals
Fix for "space" as thousand separator It is possible to select different symbols as a thousand separator for large numbers shown to users. One of the options, space (" "), was not saving correctly. This has now been resolved
Fix error handling transformer Fixed an issue where it was possible that a failed transformer error was not processed properly which prevented the transformer from giving the user feedback
Added index to speed-up report preparation During report preparation, Edify executes several queries to the adj_lookup table. An index has been adding to this table to improve performance

v3.2.1

September 1, 2020

Quantity and Charge Adjustment are now handled in strict order As of this release, quantity adjustments are applied first and before any charge related adjustments. This in turn enables the ordering of individual adjustment policies.
Implemented translations settings (Beta) Implemented an automatic translation feature for Dutch, German, and French. This can be configured on a system or user level. Currently, this is released as a beta feature.
Delete old Nginx log files Old Nginx log files will now be deleted by the garbage collector. This will help reduce disk space.
Improve user error page Updated the error page to improve user experience. Customer logo will now be shown here, if it has been set.
Adjustment API endpoint will now accept order In the past, there was no way to reorder adjustments. Users had to delete and recreate them to do this. This has now been solved. The order field now takes in an order number. If this is not used, the current functionality will still work, using the creating date instead.
Reports can now include adjustment name The adjustment name can now be added to a report. This is useful for report users that do not have access to view the whole adjustment.
Support in UI for changing the order in which Adjustments are applied Since it is possible to create multiple Adjustments for a single account, they may affect each other. It is therefore desirable to have the ability to control the order in which they are applied. As of this version, it is possible to change the order in which Adjustments are applied.

Bug fixes

Fixed an issue when partial preparing manual services When partial prepare kicks in, it previously skipped manual services. This has now been resolve.
Fixed SAML ACS invalid schema error Some users were experiencing an Invalid Schema bug when accessing the /v1/auth/saml/acs endpoint. This has now been fixed.
Fixes "Nginx Log File" not found error The location of some log files could not be found. This has been resolved.
Improved invalid token handling Changed how this error is logged. It will now be treated as a notice instead of a warning.
Fixed an issue with running transformers
In release 3.2.0 there was a problem running transformers from the GUI. This has now been fixed.

v3.1.5

July 21, 2020

Bug fixes

Premature validation warning
Resolved an issue where a user could get a premature validation error for certain input fields.
Fixed an issue with the budget report
With certain budget configurations, the budget report would display a message instead of the report even if the budget configuration was valid. This has now been resolved.
Improved user message when execution time is exceeded
Improved the message when the execution time of an Extractor, Transformer or Report is being exceeded
Fixed an issue with budget leaf account distribution When configuring a budget for a budget leaf, the distribution setting will be forced to be of the type "shared"
Fixed an issue when deleting services Previously it wasn't possible to delete a service if it still had any associated rates. This behavior has now been corrected so that services including its relationships will be deleted
Fixed an issue with the Garbage collector exits code The Garbage collector would previously always return an exit code of 259 when invoked manually. This has been resolved
Fixed an issue where subscriptions could show up on more than one report In some cases, a subscription (created for an account associated with a specific report) could be shown when a different report was run. This has now been fixed
Fixed graph image export When exporting a graph chart (i.e. Pie, Bar, Line) in any of the reports (Accounts, Services Instances) the file download would not start. This has now been resolved
Restored indicators for account access list Pencil-like indicators will inform the user where nested accounts have been selected. This functionality was unintentionally removed from the user management screen but has been placed back
Horizon performance improvements Implemented two seperate changes to improve Horizon budget execution performance. (1) budget gets validated only once after a budget configuration change and (2) added a new database index to improve database query execution
Always show decimal values Resolved an issue where some values would be displayed using their scientific notation
Resolved issue when deleting services Fixed an issue where the user would get an error when making multiple delete requests for services

v3.1.2

July 3, 2020

New features

Ability to skip database backup when updating When updating the software, the user can now skip the PostgreSQL database backup to improve update speed

Bug fixes

Fixed an issue with the webproxy.conf proxy_pass URL
When updating the software, the proxy_pass URL for the webproxy.conf NGINX configuration was always reverted back to https://127.0.0.1:8002. This has now been resolved

v3.1.1

June 28, 2020

Fixed charges no longer available As of version 3.1.0, we're removing fixed charges (or: interval-based charges) because of the limited amount of use cases and low customer adoption with this feature. In case you are an Exivity customer and are still using this service parameter, please reach out to support@exivity.com so we can provide you with alternative solutions.

New features

Improved auditing when managing accounts manually Added a number of new audit points when performing manual account management
Auditing token creation correctly logs client IP address when API is behind a proxy server
Updating user profile information now requires providing the current password
Implemented current password validation A user is now required to enter his/her current password before any changes to its profile are applied
Added logging audit entries for metadata, service subscriptions, and budgets

Bug fixes

Fixed an issue where some users could select budgets even if it would not contain any details
Fixed an issue which sometimes caused the workflows screen to load for a long time A resource-intensive operation that ran whenever the workflow page is visited got removed
getCUPRs function ported to PSQL getCUPRs function was changed from using SQLite global DB to PSQL global DB
Mitigated a potential security issue (internal reference EXVT-3773)
Fixed an issue were sometimes log files would appear malformed in the Transformer run tab
Reverted the way a user deletes an RDF
Fixed form behavior in rates screen
Fixed "other" option behavior at services
Fix report filter When switching reports, some of the filters were not reset and appeared unused while they actually held a value not related to the currently selected report. This made the reports appear empty. This has been resolved.
When the API runs out of memory (possible for very large reports) it will respond with a descriptive message
Incorrect display of minimum_commit_delta_charge and minimum_commit_delta_quantity When a minimum commit quantity was set for a service rate revision, the resulting charges could show up incorrectly by having the quantity and minimum commit delta swapped on the reports

v3.0.5

May 26, 2020

Bug fixes

Fixed issue at /lookups where in specific cases an error was undefined
Fixed issue where user was shown an incorrect validation warning When providing a number with more then one decimal, the user interface would show an error message. This has been resolved.
Added cache check in partial preparation There was an issue that partial preparation code didn't check for cache table presence before analysis, which caused execution errors, and it has been fixed.
Fixed escape option behaviour in 'import' There was an error that '\"' sequence was always treated as escaped quoted even when 'escape' option switched off (default), it has been fixed.
Rate revision date never changes Transcript was trying to change rate revision date when it detects an attempt to create new revision with the same attributes but earlier date, which conflicted with database constraint, so it was removed in order to avoid execution errors.
Fixed issue where line breaks were not shown When providing an address for the summary report with multiple new lines, only the first would be shown. This has been resolved.
Fixed undefined tool tip at workflows The was a column which showed a tool tip with undefined as value. This has been resolved.
Select a single date for transformer run After introduction of a new calendar, the single date selection was missing. This has now been resolved.
Services and categories at subscriptions are now alphabetically sorted
Fixed an issue where dataset columns were missing After repreparing a report, the columns associated with a dataset were not visible. This has been resolved.
Fixed visibility newly created dataset When running a transcript, newly created datasets were only visible after refresh. This has been resolved.
Show red underline when incorrect value for textarea input
Show correct validation values when saving environment
When saving an environment, we will now show the correct error messages for missing values.

v3.0.4

April 30, 2020

New features

New feature: Subscriptions When Beta features are enabled, users can now create one-off and recurring daily, monthly or yearly subscriptions for services for which a metered data source is not available.
New feature: Workflows widget When Beta features are enabled, a new Workflow traffic-light widget will be shown on the main Dashboard page.
New feature: Global Variables When Beta features are enabled, users can now manage Global Variables under System Administration. This enables users to decouple Extractor and Transformer variable values from scripts thus supporting different values on a per Exivity instance basis without changing underlying scripts.
New feature: Metadata for Services Metadata can now be added to all services just like with accounts. Define a metadata definition first, then attach the definition to a dataset in Data pipelines > Datasets. All services in this dataset will now use this set of metadata fields. Metadata information itself can be added and modified in Services > Overview and is available in the services reports.
Increased rounding of set to match calculate statement The Transformer set and calculate statemets now have had their rounding precision increased to 12 decimal places.
Added more information to the summary report The summary report now contains the following additional columns: service_key, account keys (between 1 and 5 inclusive depending on the report), start_date, end_date. These columns are also included in CSV exports.
Improved quoting in exported CSVs To avoid potential complications with Excel, any cell values in CSVs exported from Exivity that are not numbers and begin with any of the characters =,+,- or @ are now preceded with a single quote.
A new setting has been added to allow users to stay logged in. If disabled (default), users need to log in each time they open Exivity in their browser.
Administrators can now set the token lifetime. When the token lifetime expires, users need to log in again.
Added ability for users to log out of all devices.
Mitigated a potential security issue (internal reference EXVT-3457)
Mitigated a potential security issue (internal reference: EXVT-3455)
Mitigated a potential security issue (internal reference EXVT-3270)
Implemented a new charge model for monthly services The charge for a monthly services may now be based on the usage of a specific day in the month.
Improvements in report performance Reports are now pre-loaded in the database which speeds up report loading times in the GUI.
Added support for LDAP authentication LDAP authentication was available as beta feature already, and is now generally available. A guide will be added to our documentation soon. Configuration options are available in the Settings screen (Single sign-on tab).

Bug fixes

Fixed an issue with manage_metadata_definitions Granting the "Manage Metadata" permission is now possible when creating a security group
Fixed an edge case for incorrect net value on accounts table Whenever cogs or charge had 0 as value the net value wouldn't be displayed correctly on the details table of the accounts report.
Fixed an issue when creating a manual service Manual per unit cogs type services will not require cogs column
Fixed invalid format issue metadata lists Previously empty lines we're persisted as list option. Options are now trimmed and validated before persisting.
The API now requires additional attributes for certain service types
Fixed a small issue with the metadata selector in the report management screen
Removed toolbar from PDF export The Summary report PDF export included the toolbar on the top of every page of the PDF document. This has now been removed.
Fixed an Extractor XML parsing issue USE could previously fail when iterating over an empty XML node inside a foreach loop. This has now been resolved.
Fixed modified display issue in Accounts Overview When switching from Account Name to Account Key in the Accounts Overview screen, the modified state / pencil icon disappeared previously. This has now been resolved.
Fixed an issue when scrolling in the Accounts Overview When having a large amount of Accounts in a report definition, the Accounts Overview screen could generate cosmetic glitches when scrolling
Fixed a Transformer issue with skip_corrupted_records When import option skip_corrupted_records was set, import could fail if last column in the record is missing closing quote. This has been resolved.
Fixed a Transformer issue with aggregate When using the aggregate max function, Transcript could pick the wrong value.
Fixed a false warning about invalid COGS type in the logfile When preparing reports, in some cases a lot of warnings would appear in the logfile that state: Invalid cogs_type (0) in database for service ID nnn where "nnn" is a service ID. This was a false warning and could be ignored but looked concerning and could lead to larger logfiles. This issue has now been fixed.
Removed "remainder" option from lowest level accounts when setting budgets When a budget is set for an account at the deepest report level, the "remainder" option is no longer shown, as there are no sibling accounts to share the budget with.
Fixed an issue where (harmless) SQL errors could be logged when deleting services When deleting services, SQL errors could be present in the logfile for days where reports that reference those services had not been prepared. This has now been fixed.

Older release notes can be found here.

Upgrading to version 3

Please ensure to read and understand all subjects that are mentioned here. Implement the suggested changes (where applicable) before upgrading to version 3 to avoid unexpected behavior.

Upgrading to a v3.x.x will require upgrading to version v2.10.x first. The installer will verify this and displays a warning when this requirement is not satisfied.

PostgreSQL

The biggest single change is the use of a new database engine powering all application state, audit logs and processed report data. Upgrading to this new database engine is transparent and the installer will take care of installing the database server as part of the regular installation process. After or during upgrade, it is possible to leverage an external database server.

Windows Services

In version 2.x.x there were only two Exivity Services installed:

With Exivity version 3.x.x, assuming all components are installed on a single host system, there will be total of 4 different services:

In case you were using a service account in Exivity version 2.x.x for the Exivity Scheduling Service and the Exivity Web Service, you will have to reconfigure this service account for both services, as well as the Exivity API Service. In most cases, the Exivity Database Service may continue to run un the Local System account.

In case your current Exivity version 2.x.x installation runs inside an Active/Passive Windows Cluster, you will need to re-register the Cluster Roles for the Exivity Scheduling Service. Additionally, a new Cluster Role should be created for the Exivity Database Service, in case you decide to not use an external database host.

Default TCP ports

In v2.x.x, the default port for the Exivity GUI was 8001 and 8002 for the Proximity API. Both services were already available through port 443 (the default port used for https traffic, which means clients don't have to explicitly specify the port) and in v3.x.x this will be used by default:

This is achieved by shipping a web proxy configuration for nginx, which routes all requests starting with /v1/ to port 8002 and all other requests to port 8001. The recommended configuration is to not expose port 8001 to the public and only accept incoming traffic on port 443. Port 8002 may still be opened to external hosts, typically in a configuration where the Web and API/Backend components are deployed seperate hosts. In such a scenario it is advisable to allow communication from the Web node towards the API/Backend node on port 8002.

Default Security Settings

As of Exivity version 3 more strict security settings are applied by default. These can be found under Administration > Settings. One important item which should be considered when upgrading a multi node environment, is the use of CORS. It is required to list all possible front end UI nodes in the CORS origins field:

Multiple hosts including https:// may be added, while seperating each URL using a , (comma) symbol. Wildcards may also be used as part of the hostname to match multiple URL's in one go such as: https://*.cors.exivity.io. An overview of all current security policies can be found here.

Transformer changes

@FILE_EXISTS and @FILE_EMPTY

The @FILE_EXISTS and @FILE_EMPTY functions in Transcript have been modified in a manner which may require changes to scripts that use them.

Previously, it would only check for files in the system and exported folders within the Exivity home directory, and if a specified path + filename did not start with system/ or exported/ then these would be prepended automatically before the check as done.

This behaviour has been changed in the following ways in v3.x.x:

Any path + filename within the Exivity home directory can now be checked
path + filenames are now accepted and treated as being relative to the Exivity home directory
The folders system/ and exported/ are no longer automatically prepended

Consider a file somefile.csv in the %EXIVITY_HOME_PATH%/exported folder. Previously with version 2.x.x a user could check for the existence of this file using the following example:

In version 3 it is required to include the entire path relative to the %EXIVITY_HOME_PATH% :

This change may require modifications to existing Transformer scripts. This is because system and exported will no longer be automatically prepended.

Extractor changes

HTTP server certificate validation

The default behaviour of the HTTP subsystem in USE was changed to fully validate server SSL certificates, which may cause some USE scripts to fail. This typically applies to data Extractors, which are connecting to on premise data sources that use self signed SSL certificated. In version 2.x.x, the default behavior was to ignore these certificate errors. You can identify these errors in your Extractor logs:

It is highly recommended to use valid SSL certificates, properly signed by a trusted CA (Certificate Authority). However it is still possible to switch certificate validation off by specifying set http_secure no in an Extractor script before executing an HTTP request.

The above will apply to all of your Data Extractors where you are connecting to (most likely internal) data sources which are using self signed certificates. Make sure to apply this change before upgrading to v3 to avoid any data extraction errors.

Extractor & Transformer Schedules

In version 2.x.x it was still possible to schedule an Extractor or Transformer from the editor screen. This feature is removed from version 3.x.x. In case you still have schedules which are configured thru this interface, you should Unschedule these and create an approperiate Workflow instead.

GUI changes

There are some minor changes in the GUI which are not backwards-compatible:

We removed the Excel export options from the reports. They were using the CSV format under the hood (i.e. they never actually produced valid Excel worksheets). In the future, we plan to implement proper Excel export formats for the reports, including a full summary report Excel export.
Removed functionality which would take a custom API port from the #port=xxx location hash parameter on the login screen. Specifying a custom API port (and hostname) is still possible by setting up a config.json file on your system.

Changes to data processing for reports

Due to changes to the processing of reports, when making changes to either services, rates or adjustments, associated reports should be prepared. In v2.x.x this was already required when making changes to services or rates. Since v3.x.x this will be also required when making changes to adjustments. We've planned further improvements making this more transparent (i.e. handling preparation of reports automatically in the background).

API changes

Normalised date/time data in responses

Some endpoints were returning dates and timestamps in different formats. This has been normalised in such a way that all responses use the same serialisation for dates and timestamps:

A date is always represented as ISO-8601 string: "yyyy-mm-dd", e.g. "2020-01-29"
A date/time is always represented as ISO-8601 string in UTC: "yyyy-mm-ddThh:mm:ssZ", e.g. "2020-01-29T11:26:52Z". Note that the Z suffix denotes the UTC time standard.

This affects the responses (attributes) for the following group of API endpoints:

/v1/audit
- created_at
/v1/budgetrevisions
- effective_from
/v1/dsets
- earliest_rdf
- latest_rdf
- rdf_detail.created
- rdf_detail.updated
/v1/extractors
- last_modified
/v1/log
- created
- lines.date
/v1/reports
- data_status.first_date
- data_status.last_date
- data_status.status.date
/v1/services
- created_at
- updated_at
/v1/transformers
- last_modified
/v1/workflows
- created_at
- updated_at
/v1/workflowschedules
- start_time
- next_run

Changed functionality

`/v1/reports/{id}/run` endpoint

The JSON format in the response is simplified. See examples below.

This is an example response from v2.x.x:

{
   "report": [
      {
         "meta": {
            // ...
         },
         "error": false,
         "data": [
            {
               // ...
            }
         ]
      }
   ]
}

This is an example response from v3.x.x:

{
   "report": [
      {
         // ...
      }
   ]
}

Remove deprecated functionality

`/v1/usergroups` endpoint

Removed deprecated permission aliases.

upload_files (use manage_files instead)
manage_configuration (use manage_settings instead)
manage_system (use manage_settings instead)

`/v1/reports/{id}/run` endpoint

Functionality deprecated in v2.x.x has been removed. If you were relying on any of the following functionality, please use the suggested replacement instead:

The pdf/invoice option for the format query parameter has been removed. Please use pdf/summary instead.
The invoice_options query parameter has been removed in favour of summary_options.

`/v1/configuration` endpoint

Configuration keys prefixed with INVOICE_ are replaced by respective keys prefixed with SUMMARY_.

`/v1/workflowsteplogs` endpoint

Also relevant to other endpoints including workflowsteplogs .

Removed the last_log attribute. Include the last log by specifying the query parameters include=steplogs&related[steplogs][limit]=1&related[steplogs][sort]=-start_ts.
Removed the timestamp attribute. Use start_timestamp instead.
Removed the message attribute. Use a combination of status and output instead.

`/v1/file` endpoint

Only applicable to POST requests to this endpoint.

The filename in the response from this endpoint will no longer include the import/ prefix to better align for other requests in this endpoint. See example below.

{
-    "filename": "/import/generic/2020/01/31_uploaded_001.txt"
+    "filename": "/generic/2020/01/31_uploaded_001.txt"
}

`/v1/usergroups` endpoint

The following permissions has been removed in favour of their new counterparts:

UPLOAD_FILES has become MANAGE_FILES
MANAGE_CONFIGURATION has become MANAGE_SETTINGS
MANAGE_SYSTEM has become MANAGE_SETTINGS

Known issues

This page lists known issues with the latest version of Exivity. All isues that are resolved and released are mentioned under the Releases section

Relative quantity discounts can cause negative charges with prorated monthly services

Description

When configuring a prorated monthly service, and then applying a relative quantity discount (adjustment), in certain cases the total charge can be less than 0 resulting in a credit. Although this might be an unusual configuration and might not always happen, it is important to be aware of this behaviour.

Status

This issue is pending. (internal reference: EXVT-1337)

Reports

Accounts

The Accounts report provides the ability to drill down into your metered based IT consumption costs. The way you've created your Report, determines on what values you can zoom into.

Once you've logged into the system, go to "Reports" > 'Accounts'. Here are a few key parameters you can use to define how your report is generated:

Date Selection

The date selector is important to limit the scope of data you're focusing on.

First select the date range you are interested in. This can be a single month, a 3-month time period, half a year, a full year, or a custom date range.

Report

Your Exivity solution can have more then one report configured. If this is the case, you will need to select the appropriate report containing the data you wish to examine. An end user will only see reports listed that they have permissions to, and the first of those is automatically selected.

Drill Down, Services & Report Depth

After selecting a date range and report you can start drilling down into your data in a number of ways:

Moving your mouse over one of the accounts will reveal a toolbar as shown above. For each account, you have the option to click on the Drilldown control in that toolbar. This will do the following:
1. Descends one level deeper into the report
2. Updates your view of the data to reflect that deeper level
3. Sets the 'Parent' filter to the account you selected to drill down on
As well as the ability to drill down you can also view the Services associated with an account on any level of your report. Note that this will change your view from the Accounts report to the Services report.

Services

The Services report provides the ability to report on your metered based IT consumption costs from a Services perspective.

Once logged into the system, go to Reports > Services. From here you will get a report grouped by the services consumed. This report can be refined using a number of filters.

Filters and Reporting Depth

Once you have selected your date range and report you can start viewing your data. By default it will show all consumed services for this report for the accounts you have permission to access. If you want to limit your view you can change the reporting 'Depth', and then apply additional filters such as:

Category - to only view certain Service Categories
Account - to limit your view to all services belonging to a certain account

When filtering the services for a specific account, it is recommended to start from the Accounts Report where you can drill down into a specific account. Once the account has been picked, switch the view to the services associated with that account using the buttons in the detailed report:

Instances

The Instances report will provide an overview of all chargable resources. It is recommended to apply filters to this report. Therefore the best approach is to drill down into Instances from the Accounts or Services report. This allows a user to easily drill down into all resource instances for a particular Account, Service or both:

Opening the Instances report without any filters applied can take a while to load depending on the amount of records in the selected reporting period.

Summary

The Summary report provides a detailed breakdown of costs in an invoice-like format. The services defined in the system determine what you'll see here.

Once logged into the system, go to Reports > Summary. From here you're able to generate different kinds of detailed costs reports, which can be used for billing, chargeback and showback.

Filters and Reporting Depth

Once you have selected your date range and chosen which report you to activate will be presented with the summary cost report as shown above. By default it will show all consumed services for an account. As accounts are heirarchical, this view will include consumed services for all children of the selected account. Therefore it is important that you select an appropriate Depth when running this report.

Grouping and display of Instances

The Summary report has a few options that you can turn on and off. These options allow you to tune the amount of detail shown and the grouping applied to it:

If you want to include a detailed grouping of Services and Service Categories, then ensure that the Services checkbox is enabled. The same goes for including Instance level information. The latter enables you to view resource level consumption data, such as the Virtual Machine hostname, Container or User Name.

Budget

Currently available as a beta feature

After configuring a budget, it is possible to report on current budget spendings. To do so, first browse to the Reports > Budget screen and select a Budget from the drop down list:

Also make sure to select a date range that matches with one or more of the configured budget revisions. Once selected, the budget report will start loading. This may take a few seconds, and a report similar to this will be shown on your screen:

Services

Manage

The services screen gives a user the ability to view and change the available services in the service catalogue of the Exivity deployment. When creating new services, it is required to use a Transformer with the service or services statement.

Obtaining details of a Service

To view the details of a service that has already been created, click on one of the services listed in the 'Services' > 'Overview' screen:

The numbered items from above screenshot refer to the following list:

The description or friendly name for this service
The unique key value of this service (see service)
The time stamp when the service was created
The time stamp when the service was updated
The DataSet where this service relates to
Where to obtain the service name from (in header or in data). The value will be used for the service description (see 1)
The source column that has the consumed quantity
The Instance column refers to the chargeable instance column value (i.e. VM ID) which is required for automatic Services
The interval that defeines the frequency of how often this service is being charged. Meaning: automatic (every occurrence/record/hour), daily or monthly
When using proration, this checkbox will be enabled. Proration takes into account whether to charge for a portion of a consumption interval. For example: when having 10 days of consumption for a monthly service with a rate configured of € 90 per unit that has proration enabled, will result in a line item of € 30 for that services monthly charge
The Billing Type provides information whether this is a service that has manual (using manually provided, adjustable rate value) or automatic (using rate column) rates configured
COGS (Cost of Goods) of a service will have its own rate configuration, which can be either manual/automatic per unit or manual/automatic per interval

Changing or Deleting a Service

In case you need to change the configuration of an already populated service, the GUI enables you to do so. To change an existing service you will need to make sure that you have first selected the appropriate report from the Report Selector and the left top of the screen. To change the configuration or delete a service, you will then need to follow these steps:

Navigate to the Services > Overview menu and click the white Edit button at the top of service list. The system will warn you that any changes made to existing service, may require you to re-Prepare the currently selected Report , found at Data pipelines > Reports.
If you have confirmed the warning message, you will be able to select one, multiple or all of the services within the currently selected Report . You can then select the Delete button next to the Edit button, to delete all selected services.
If you want to change the configuration of one of the services, you should first select the service which you'd like to change.
When you have the service that you want to change selected, you can change any of the available parameters such as the Instance Column, Interval, etc. Once you are satisfied with your changes, you may press the Update button.
Ensure to re-Prepare your Report in case you have made any changes.

Creating a Service

Although we recommend to automatically creating services from the Transformer ETL process, it is also possible to manually create a service in the GUI. To create a new service, navigate to the Services > Overview menu and click the Edit button at the top of service list. The system will warn you that any changes made to existing service, may require you to re-Prepare the currently selected Report , found at Data pipelines > Reports.

After confirmation of this warning message, the Create button is enabled:

Now it is possible to create a new service. Ensure to fill in all fields, since all fields are mandatory:

When your new service configuration satisfies your need, you may click the Create button.

Apply Metadata to Services

Once a Metadata Defenition has been applied to a Dataset, it is possible to apply tagging or other metadata key/values to a service. This can be achieved by selecting a service and then selecting the Metadata tab:

In this tab it is possible to configure all metadata fields which are available in the parent Dataset. To save your changes, click the Update button.

Rates

The 'Rates' screen allows you to configure manual rates for services that do not have a rate provided with their data source. Before you can use this screen, it is required to create the necessary service(s) via the Transcript engine. When that requirement has been fulfilled, you may configure Global and customer specific rate configurations. The following rate types are currently supported for automatic, daily and monthly services:

automatic per unit
automatic per interval
automatic per unit & interval
manual per unit
manual per interval
manual per unit & interval

Automatic services obtain the rate and/or interval value from a column you specify, whereas manual services allow the user to manually specify a rate and fixed interval money value. For manual services, if a service definition has proration enabled, the charge on the cost reports is calculated based on the actual consumption (see services).

By default each service has a global rate configured, which will be applied to all accounts that consume this service. However, it is possible to use customer specific rates by overriding a service rate for one ore multiple accounts.

Edit global rate for a manual service

A manual service can have up to 3 rate values that can be changed: the unit rate, the interval money value and the COGS rate. To change these values, go the 'Services' > 'Rates' screen and click on the service name for which you want to change the global rate value:

To change the rate values of this service, consider the following:

Effective date is the date from when this rate is applied to the service. A service can have one or multiple revisions. You may add new rate revisions by using the Add Revision button. Existing rate revision dates can be changed using the Change Date option
The Per Unit rate value is the value that the service charged for, every (portion of) configured interval service. In this example, if this would be a daily service that is charged 1 euro per Gigabyte of database usage, and each day a 100 GB database is consumed, a value of € 100 will be charged per day (and € 3100 if used for entire month of December)
The Per Interval charge is applied every occurrence of the consumed service, regardless of the total consumed quantity of that service. In the previous example at 2, this would mean every day a value of 110 euro will be charged for a 100 GB database: 100 euro because of the standard rate + 10 euro for the fixed interval charge. Considering the same consumption for the entire month of December, the total charge for that month will be 31 x € 110 = € 3410
It is possible to configure a COGS rate for this service. This is applied the same way as the Per Unit rate
To delete an invalid or wrong revision, use the Remove Revision button. Do bear in mind you cannot delete the last rate revision for a service
To save your changes, which will also initiate a re-preparation of the applicable Report. click the Save Revision button (see defining reports to learn more about report preparation)
If you are planning to make more changes to other services in the same report definition, use the Save Revision > Without Preparing option. This will avoid running the re-preparation several times, and allows you to start the re-preparation only after you've made all of the required rate changes.

Adjustments

Exivity enables you to create account specific rate adjustment policies. An adjustment policy allows you to apply a discount or a premium using one of these modifiers:

a certain amount of money (i.e. $ 100)
a certain quantity (i.e. 100 GB/hours)
a percentage (i.e. 10%)

This Adjustment can then be applied to a single service, multiple different services, or one or more service categories.

Create an Adjustment Policy

To create a new adjustment policy for an account, follow these steps:

From the menu on the left, select 'Services' > 'Adjustments'
Then select the Account from the list of accounts for which you want to create an adjustment policy
After selecting the account, click 'Add Policy', and provide a meaningful name for your policy in right screen where it says 'Adjustment name'
Provide the Start date, by selecting the initial month when this adjustment policy is applied
Provide the End date, by selecting the month when this adjustment policy will be discontinued. This is optional, since an adjustment policy can be applied permanently.
Select which Service or Service Category this policy is applied to. You are able to select multiple using the check boxes that are provided.
Select a Type for this adjustment. This can be either a Discount or a Premium
Select the Target, meaning: is this Adjustment targeting the total Charge or the total Quantity of the selected service(s)?
Select the Difference setting, to indicate an Absolute value (i.e. 100 units, or 100 dollars) or a Relative value (such as 10%)
Lastly provide the Adjustment value. In the example shown in the image above, there is a value of '10' provided in the Amount field, which will adjust the total charge with -10% given the provided parameters.
When you're done, click the Add Policy button. Your changes are now applied to all charge related reports.

Subscriptions

This feature is currently in beta. In order to use this feature, the checkbox "Use Beta Features" under the settings menu, should be enabled.

To create a subscription, first browse to the Accounts > Subscriptions menu. In the Subscriptions menu it is now first required to select a 'leaf account' (meaning: an account at the deepest level of a report definition). Once this account has been selected, a new Subscription can be created:

When creating a new subscription, it is mandatory to first give it a name and select the Service Category and Service where this Subscription applies to:

Once a Service has been selected, depending on the type of Service, you are either free to fill in a customer Subscription Rate and optional Cost. If this service has already rates configured in Exivity, it will automatically show and use those:

Next the Interval section will be required to fill in. First provide the Subscription Type, which can be either a Recurring subscription or a One-off transaction. In the case of a Recurring subscription, it is required to provide an Interval of Daily, Monthly or Yearly. This determines how often the Subscription is being charged. Then the Subscription Start date needs to be set, which determines the initial charge date. Also an optional End date be configured. If none is provided, it will be charged until the Subscription is removed, or changed later to include an End date.

When creating a Monthly or Yearly Subscription, it is also required to select a Charge day. This determines the day of the month when the Subscription is being charged. In case of a Yearly subscription, it will also be required to specify a Charge month, to indicate which month of the year the Subscription will be charged:

The last step in creating the subscription is specifying a consumed Quantity, which should reflect the amount of units being charged per interval. Optionally an Instance Identifier may be specified. If none provided, it will fall back to the name of the Subscription.

Click the Create button to create this subscription. In case you are creating a Subscription with a historical Start date, it will then be required to use the Create - with preparing button:

In case you are creating multiple Subscriptions, it is advisable to stack these creations to avoid having to execute Create - With preparing multiple times in a row.

ACCOUNTS

Budget management

This feature is currently released as beta

It is possible to define a budget on any level within your organisation. This enables different audiences to monitor costs across different clouds. It also enables any business owner to set thresholds informing customers, departments or project owners when they are reaching their configured budget.

Create a budget

In order to create a budget, navigate to Accounts > Budgets. Then click the Create button to create a new budget. In this menu, a couple of items are presented:

Global options

Global options apply on the entire budget, and aply to the following items:

Interval: determine whether the budget is applied Monthly, Quarterly or Yearly
Apply to: a configured budget is by default applied to the total Charge of the configured Interval. It is however also possible to create a budget which is applied to the Cost of Cogs (COGS) instead

Revisions

A budget configuration can potentially change year over year, and therefore it is possible to create different budget revisions. Each revision can have the following settings applied:

Revision start date: the start Month, Quarter or Year for this budget revision
Filter by: typically a budget is applied to a single or multiple Accounts. It is however possible to add additional filtering on Service or Service Category. By applying Service based filtering, it is possible to limit the scope for a configured budget

Accounts

When a budget is created, it is possible to set a budget money amount for one or multiple accounts. In case each account for which a budget is set, also has 1 or multiple levels of child accounts, it is possible to control how the budget 'trickles down' the organisational structure:

Account selection: it is required to select an Account from any level in your Report Definition. It is the possible to set a budget value (i.e. $100000) in the grey box next to the Account.
- The same applies to any Child Accounts for which you may set/overwrite a budget. You may add a Child Account to the list, by click the green button left of the account name
  - NOTE: a child account may also be excluded from a budget, by clicking the Exclude checkbox right of the name of the Account
Remainder: using the Remainder drop-down it is possible to control the distribution of the budget towards child accounts. The two options to pick by default are:
- even: each child account will get an even amount of budget. Example :Consider a top level account 'ACME Corp' with a monthly budget set of $100.000. When 'ACME Corp' has 10 child 'Business Unit' accounts , each of these 'Business Units' will get an even amount of the budget: In this case this will be $10.000.
- shared: when the distribution of the remainer is set to shared, the consumption of child accounts is ignored. As long as the total spendings of all child accounts does not go beyond the configured budget. Example: Consider a top level account 'ACME Corp' with a monthly budget set of $100.000. When 'ACME Corp' has 10 child 'Business Unit' accounts , each of these 'Business Units' combined should not use more then $100.000.
- none: the option to not distribute any remainders is only applicable when overriding the budget percentage for each child account. This means it is required set a distinct budget percentage manually for each child account.

To create the Budget, click the Create button. It is now possible to view the spendings under budget via the Budget Report

Changing a budget

An existing budget can be changed by navigating to Accounts > Budgets, and then clicking the budget which you want to change.

Changing an existing budget revision

Once a budget has been saved, you will be unable to change the start date unless you edit the Budget Revision:

Once you have enabled edit mode for an existing Budget Revision, you will be able to change the start date:

After making these changes you will need to save these by click the blue checkbox on the right. You can also cancel your changed using the blue x-sign, or delete the revision by clicking the red recycle bin.

Adding a new budget revision

Similar to changing a Budget Revision, it is possible to add a new revision that holds a different start date with revised budget plan. To do this, you will need to click the green + sign:

Data pipelines

Extract

Introduction

Extraction is the process by which USE (Unified Scriptable Extractor) retrieves data from external locations. The following types of data source are supported:

USE script

A USE script is required for USE to operate. Further information can be found via the links below:

An introductory overview of the scripting language:

A reference guide for the USE scripting language:

How to parse XML and JSON data

Template scripts that can be used as starting points for common data sources:

Configuration

The 'Data pipelines' menu allows an admin of the Exivity solution to manage USE 'Extractors'. USE has its own language reference, which is fully covered in a separate chapter of this documentation.

As described in the USE documentation, you are free to use your editor of choice to create and modify USE Extractors. However, the GUI also comes with a built-in USE Extractor-editor.

Creating Extractors

To create a new USE Extractor, follow these steps:

From the menu on the left, select "Data pipelines" > 'Extractors'
To create a new USE Extractor where you want pull usage or lookup data from, click the 'Add Extractors' button
When your Exivity instance has access to the Internet, it will pull in the latest set of Extraction Templates from our Github account. These templates are then presented to you, and you can pick one from the list to start Extracting. If you don't have access to the internet, you can download them directly from Github. You are also free to start creating your own Extractor from scratch.
Provide a meaningful name for your USE Extractor. In the above example we're creating an USE Extractor for VMware vCenter 6.5 and higher. Therefore we call this USE Extractor : 'vCenter 6.5'
When you're done creating your USE Extractor, click the 'Insert' at the bottom of the screen

Edit and Delete Extractors

When you want to change or delete an existing USE Extractor, first select one from the list of USE Extractors that you want to change:

After you select your USE Extractor, you can change its variable values at the 'Variables' tab.
At the "Editor" tab you can make more advanced changes or delete the original USE-script. Such as:
- changing existing API calls
- changing csv output format

Don't forget to save any changes with the "SAVE" button.

Run and Schedule Extractors

To test your USE Extractor, you can execute or schedule it directly from the Glass interface:

After you have selected the USE Extractor that you would like to run, click to the 'Run' tab next to the 'Editor' tab
Most Extractors require one or more parameters, usually in a date format such as 20171231. In this example, the USE Extractor requires two parameters: a from and to date
When you've provided the required run parameters, click 'Run Now' to execute the USE Extractor. After the USE Extractor has completed running, you will receive some success or failed message, after which you might need to make additional changes to your USE Extractor
Once you're happy with your output, you can schedule the USE Extractor via the 'Schedule' tab, which is located next to the 'Run' tab at the top of the screen.
USE Extractors can be scheduled to run once a day at a specific time. Also you should provide a from and (optionally) to date, which are provided by using an offset value. For example, if you want to use the day before yesterday as a from date, you should use the down pointing arrows on the right, to select a value of -2. If the to date should always correspond with yesterdays date, you should provide a value there of -1.
If your Use Extractor requires additional parameters, you may provide these as well in the 'Schedule with these arguments' text field.
When you're done with the schedule configuration, you may click the 'Schedule' button. In case you want to change or remove this schedule afterwards, click the 'Unschedule' button.

As of version 1.6, it is recommend to use the Workflow function instead of the Extractor schedule

Templates

Exivity provides a catalogue of USE extraction scripts that can be used to integrate with almost any cloud provider, hypervisor or legacy IT end point. We've published some of our templates on GitHub for your convenience.

This repository contains Extractors for VMware, Azure, Amazon and others. However, if you are currently missing an integration template and are unwilling or unable to create your own, feel free to drop us an e-mail at support@exivity.com.

Script basics

USE scripts are stored in <basedir>/system/config/use, and are ASCII files which can be created with any editor. Both UNIX and Windows end-of-line formats are supported but in certain circumstances they may be automatically converted to UNIX end-of-line format.

Statements

Each statement in a USE script must be contained on a single line. Statements consist of a keyword followed by zero or more parameters separated by whitespace. The USE Reference Guide contains documentation for each statement.

Quotes and escapes

By default, a space, tab or newline will mark the end of a word in a USE script. To include whitespace in a word (for example to create a variable with a space in it) then double quotes - " - or an escape - \ - must be used to prevent the parser from interpreting the space as an end of word marker. Unless within double quotes, to specify a literal tab or space character it must be escaped by preceding it with a backslash character - \.

Examples:

"This quoted string is treated as a single word"  
var myname = "Eddy Deegan"  
This\ is\ treated\ as\ a\ single\ word
"The character \" is used for quoting"

The following table summarises the behaviour:

Comments

Comments in a USE script start with a # character that is either of

the first character of a line
the first character in a word

Comments always end at the end of the line they were started on

# This is a comment
set http_header "Content-Type: application/x-www-form-urlencoded"     # This is a comment
var usage#1 = Usage1   # The '#' in 'usage#1' does not start a comment

Currently, comments should not be used on the same line as the encrypt statement as it will consider the comment as part of the value to encrypt

Variables

Overview

USE scripts often make use of variables. Variables have a name and a value. When a variable name is encountered on any given line during execution of the script, the name is replaced with the value before the line is executed.

To reference a variable, the name should be preceded with ${ and followed by }. For example to access the value of a variable called username, it should be written as ${username}.

The length (in characters) of a variable can be determined by appending .LENGTH to the variable name when referencing it. Thus if a variable called result has a value of success then ${result.LENGTH} will be replaced with 7.

Creation

Variables may be explicitly declared using the var statement, or may be automatically created as a consequence of actions performed in the script. Additionally, a number of variables are automatically created before a script is executed.

For a list of variables created automatically please consult the article on the var statement

Encryption

It may be desirable to conceal the value of some variables (such as passwords) rather than have them represented as plain text in a USE script. This can be accomplished via the encrypt statement.

Publishing to the user interface

Variables may be exposed in the GUI by prefixing their declaration with the word public as follows:

public var username = username
public encrypt var password = something_secret

Any variable so marked may be edited using a form in the GUI before the script is executed. If a public variable is followed by a comment on the same line, then the GUI will display that comment for reference. If there is no comment on the same line, then the line before the variable declaration is checked, and if it starts with a comment then this is used. Both variants are shown in the example below:

public var username = login_user  # Set this to your username
# Set this to your password
public var password = "<please fill this in>"

If a variable declaration has both kinds of comment associated with it then the comment on the same line as the variable declaration will be used

Named buffers

A named buffer (also termed a response buffer) contains data retrieved from an external source, such as an HTTP or ODBC request. Buffers are created with the buffer statement.

Once created, a buffer can be referenced by enclosing its name in { and } as follows:

# Example of buffer creation
buffer token = http POST "https://login.windows.net/acme/oauth2/token"

# Examples of referencing a buffer
save {token} as "extracted\token.data"
discard {token}

Buffer names may be up to 31 characters in length
Up to 128 buffers may exist simultaneously
Up to 2Gb of data can be stored in any given buffer (memory permitting)

Extracting data with Parslets

Parslets are used to extract data from from the contents of a named buffer.

Please refer to the full article on parslets for more information on parslets and their use.

Parslets

Overview

After populating a named buffer with data from an external source such as an HTTP request or a file, it is often necessary to extract fields from it for uses such as creating subsequent HTTP requests or rendering output csv files.

This is accomplished using parslets. There are two types of parslet, static and dynamic. In both cases, when a parslet is used in a script it is expanded such that it is replaced with the value it is referencing, just like a variable is.

Static parslets refer to a fixed location in XML or JSON data
Dynamic parslets are used in conjunction with foreach loops to retrieve values when iterating over arrays in XML or JSON data

Parslets can be used to query JSON or XML data. Although JSON is used for illustrative purposes, some additional notes specific to XML can be found further down in this article.

A quick JSON primer

Consider the example JSON shown below:

The object containing all the data (known as the root node) contains the following children:

Objects and arrays can be nested to any depth in JSON. The children of nested objects and arays are not considered as children of the object containing those objects and arrays, i.e. the children of the heading object are not considered as children of the root object.

Every individual 'thing' in JSON data, regardless of its type is termed a node.

Although different system return JSON in different forms, the JSON standard dictates that the basic principles apply universally to all of them. Thus, any possible valid JSON may contain arrays, objects, strings, boolean values (true or false values), numbers and null children.

It is often the case that the number of elements in arrays is not known in advance, therefore a means of iterating over all the elements in an array is required to extract arbitrary data from JSON. This principle also applies to objects, in that an object may contain any number of children of any valid type. Valid types are:

Some systems return JSON in a fixed and predictable format, whereas others may return objects and arrays of varying length and content. The documentation for any given API should indicate which fields are always going to be present and which may or may not be so.

Parslets are the means by which USE locates and extracts fields of interest in any valid JSON data, regardless of the structure. For full details of the JSON data format, please refer to http://json.org

Static parslets

Static parslets act like variables in that the parslet itself is expanded such that the extracted data replaces it. Static parslets extract a single field from the data and require that the location of that field is known in advance.

In the example JSON above, let us assume that the data is held in a named buffer called example and that the title and heading children are guaranteed to be present. Further, the heading object always has the children category and finalised. Note that for all of these guaranteed fields, the value associated with them is indeterminate.

The values associated with these fields can be extracted using a static parslet which is specified using the following syntax:

$JSON{buffer_name}.[node_path]

Static parslets always specify a named buffer in curly braces immediately after the $JSON prefix

The buffer_name is the name of the buffer containing the JSON data, which must have previously been populated using the buffer statement.

The node_path describes the location and name of the node containing the value we wish to extract. Starting at the root node, the name of each node leading to the required value is specified in square brackets. Each set of square brackets is separated by a dot.

The nodepaths for the fixed nodes described above are therefore as follows:

Putting all the above together, the parslet for locating the category in the heading is therefore:

$JSON{example}.[heading].[category]

When this parslet is used in a USE script, the value associated with the parslet is extracted and the parslet is replaced with this extracted value. For example:

print $JSON{example}.[heading].[category]

will result in the word Documentation being output by the statement, and:

var category = $JSON{example}.[heading].[category]

will create a variable called category with a value of Documentation.

Currently, a parslet must be followed by whitespace in order to be correctly expanded. If you want to embed the value into a longer string, create a variable from a parslet and use that instead:

var category = $JSON{example}.[heading].[category]
var filename = JSON_${category}_${dataDate}

When using JSON parslets that reference values that may contain whitespace it is sometimes necessary to enclose them in double quotes to prevent the extracted value being treated as multiple words by the script

Anonymous JSON arrays

It may be required to extract values from a JSON array which contains values that do not have names as shown below:

{
  "data": {
    "result": [
      {
        "account": {
          "name": "account_one"
        },
        "metrics": [
          [
            34567,
            "partner"
          ],
          [
            98765,
            "reseller"
          ]
        ]
      },
      {
        "account": {
          "name": "account_two"
        },
        "metrics": [
          [
            24680,
            "internal"
          ],
          [
            13579,
            "partner"
          ]
        ]
      }
    ]
  }
}

Extraction of values that do not have names can be accomplished via the use of nested foreach loops in conjunction with an empty nodepath ([]) as follows:

buffer json_data = FILE system/extracted/json.json

csv OUTFILE = system/extracted/result.csv
csv add_headers OUTFILE account related_id type
csv fix_headers OUTFILE

foreach $JSON{json_data}.[data].[result] as this_result {

    # Extract the account name from each element in the 'result' array
    var account_name = $JSON(this_result).[account].[name]

    print Processing namespace: ${account_name}

    # Iterate over the metrics array within the result element
    foreach $JSON(this_result).[metrics] as this_metric {

    # As the metrics array contains anonymous arrays we need to iterate
    # further over each element. Note the use of an empty notepath.

        foreach $JSON(this_metric).[] as this_sub_metric {
            if (${this_sub_metric.COUNT} == 1) {
                # Assign the value on the first loop iteration to 'related_id'
                var related_id = $JSON(this_sub_metric).[]
            }
            if (${this_sub_metric.COUNT} == 2) {
                # Assign the value on the second loop iteration to 'type'
                var type = $JSON(this_sub_metric).[]
            }
        }

        csv write_fields OUTFILE ${account_name} ${related_id} ${type}
    }    
}
csv close OUTFILE

The result of executing the above against the sample data is:

"account","related_id","type"
"account_one","34567","partner"
"account_one","98765","reseller"
"account_two","24680","internal"
"account_two","13579","partner"

If the anonymous arrays have a known fixed length then it is also possible to simply stream the values out to the CSV without bothering to assign them to variables. Thus assuming that the elements in the metrics array always had two values, the following would also work:

buffer json_data = FILE system/extracted/json.json

csv OUTFILE = system/extracted/result.csv
csv add_headers OUTFILE account related_id type
csv fix_headers OUTFILE

foreach $JSON{json_data}.[data].[result] as this_result {

    # Extract the account name from each element in the 'result' array
    var account_name = $JSON(this_result).[account].[name]

    print Processing namespace: ${account_name}

    # Iterate over the metrics array within the result element
    foreach $JSON(this_result).[metrics] as this_metric {

    # As the metrics array contains anonymous arrays we need to iterate
    # further over each element. Note the use of an empty notepath.

        csv write_field OUTFILE ${account_name}

        foreach $JSON(this_metric).[] as this_sub_metric {
                csv write_field OUTFILE $JSON(this_sub_metric).[]
        }        
    }    
}
csv close OUTFILE

Which method is used will depend on the nature of the input data. Note that the special variable ${loopname.COUNT} (where loopname is the label of the enclosing foreach loop) is useful in many contexts for applying selective processing to each element in an array or object as it will be automatically incremented every time the loop iterates. See foreach for more information.

Dynamic parslets

Dynamic parslets are used in to extract data from locations in the data that are not known in advance, such as when an array of unknown length is traversed in order to retrieve a value from each element in the array.

A dynamic parslet must be used in conjunction with a foreach loop and takes the following form:

$JSON(loopName).[node_path]

Note the following differences between a static parslet and a dynamic parslet:

A dynamic parslet does not reference a named buffer directly, rather it references the name of a foreach loop
Parentheses are used to surround the name of the foreach loop (as opposed to curly braces)
The nodepath following a dynamic parslet is relative to the target of the foreach loop

The following script fragment will render the elements in the items array (in the example JSON above) to disk as a CSV file.

# For illustrative purposes assume that the JSON
# is contained in a named buffer called 'myJSON'

# Create an export file
csv "items" = "system/extracted/items.csv"
csv add_headers id name category subcategory
csv add_headers subvalue1 subvalue2 subvalue3 subvalue4
csv fixheaders "items"

foreach $JSON{myJSON}.[items] as this_item
{
    # Define the fields to export to match the headers
    csv write_field items $JSON(this_item).[id]
    csv write_field items $JSON(this_item).[name]
    csv write_field items $JSON(this_item).[category]
    csv write_field items $JSON(this_item).[subcategory]

    # For every child of the 'subvalues' array in the current item
    foreach $JSON(this_item).[subvalues] as this_subvalue
    {
        csv write_field items $JSON(this_item).[0]
        csv write_field items $JSON(this_item).[10]
        csv write_field items $JSON(this_item).[100]
        csv write_field items $JSON(this_item).[1000]
    }
}
csv close "items"

In the example above, the first foreach loop iterates over the elements in the 'items' array, and each of the dynamic parslets extract values from the current element in that loop. The dynamic parslets use the current element, this_item as the root for their node paths.

If a parslet references a non-existent location in the XML or JSON data then it will resolve to the value EXIVITY_NOT_FOUND

XML parslets

XML parslets work in exactly the same way that JSON parslets do, apart from the following minor differences:

XML parslets are prefixed $XML
When extracting data from XML, the foreach statement only supports iterating over XML arrays (whereas JSON supports iterating over objects and arrays)
An XML parslet may access an XML attribute

To access an XML attribute, the node_path should end with [@atrribute_name] where attribute_name is the name of the attribute to extract. For example given the following data in a buffer called xmlbuf:

<note>
<to>Tove</to>
<from>
    <name comment="test_attribute">Jani</name>
</from>
<test_array>
    <test_child>
        <name attr="test">Child 1</name>
        <age>01</age>
    </test_child>
    <test_child>
        <name attr="two">Child 2</name>
        <age>02</age>
    </test_child>
    <test_child>
        <name attr="trois">Child 3</name>
        <age>03</age>
    </test_child>
    <test_child>
        <name attr="quad">Child 4</name>
        <age>04</age>
    </test_child>
</test_array>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>

The following script:

foreach $XML{xmlbuf}.[test_array] as this_child {
    print Child name ${this_child.COUNT} is $XML(this_child).[name] and age is $XML(this_child).[age] - attribute $XML(this_child).[name].[@attr]
}

will produce the following output:

Child name 1 is Child 1 and age is 01 - attribute test
Child name 2 is Child 2 and age is 02 - attribute two
Child name 3 is Child 3 and age is 03 - attribute trois
Child name 4 is Child 4 and age is 04 - attribute quad

Subroutines

Example subroutines which can be used in your data Extractors

The gosub function in a data Extractor allows invoking a subroutine in a data extractor script. Subroutines are useful to avoid having to duplicate code snippets.

Our customers and Solution Architects have created several useful subroutines over the last few years. We are keeping a small library of the most useful ones on our public docs. You can find them listed on this page.

check_dateformat

This Subroutine checks if a date is in the YYYYMMDD format, if not then it raises and error.

Syntax

gosub check_dateformat ("YYYYMMDD")

Code Snippet

subroutine check_dateformat {
	match date "^(([0-9]{4}(0[1-9]|1[0-2])(0[1-9]|1[0-9]|2[0-9]|3[0-1])))" ${SUBARG_1}
	if (${date.STATUS} != MATCH) {
		print Argument error: ${SUBARG_1} is not in YYYYMMDD format
		terminate with error
	}
}

check_dateargument

including 1 day mode

This Subroutines checks if the FROM and TO date are in order. In the case that there is only 1 day entered, it will automatically fill in the second day in a "1 Day Mode".

Syntax

gosub check_dateargument ()

Code Snippet

subroutine check_dateargument {
	# Validate that amount of input arguments is as expected
	if (${ARGC} != 2) {
		if (${ARGC} == 1) {
			print "Running in 1 day mode"
			var firstday = ${ARG_1}
			var lastday = (@DATEADD(${firstday}, 1))
		} else {
			print "This requires 1 or 2 arguments, the day to collect usage for, and the date following that day, both in YYYYMMDD format"
			terminate with error
		}
	} else {
		var firstday = ${ARG_1}
		var lastday = ${ARG_2}
	}
	
	# Validate that to date is not before from date
	if (${firstday} > ${lastday}) {
		print "TO date cannot be a date that lies before FROM date"
		terminate with error
	}
	# Validate that to date is not the same as from date
	if (${firstday} == ${lastday}) {
		print "TO date cannot be the same as FROM date"
		terminate with error
	}
}

format_date

This Subroutine extracts the day, month and year from a given date in YYYYMMDD format.

Syntax

gosub format_date ("YYYYMMDD")

Code Snippet

subroutine format_date {
    match day "^[0-9]{6}([0-9]{2})" ${SUBARG_1}
    if (${day.STATUS} != MATCH) {
        terminate with error
    } else {
        var day = ${day.RESULT}
    }
    match month "^[0-9]{4}([0-9]{2})[0-9]{2}" ${SUBARG_1}
    if (${day.STATUS} != MATCH) {
        terminate with error
    } else {
        var month = ${month.RESULT}
    }
    match year "^([0-9]{4})[0-9]{4}" ${SUBARG_1}
    if (${year.STATUS} != MATCH) {
        terminate with error
    } else {
        var year = ${year.RESULT}
    }
}

validate_response

This subroutine allows validating an HTTP response from a buffer.

Syntax

gosub validate_response({my_buffer})

Code snippet

subroutine validate_response {
    if (${HTTP_STATUS_CODE} != 200) {
        print Got HTTP status ${HTTP_STATUS_CODE}, expected a status of 200
        print The server response was:
        json format ${SUBARG_1}
        print ${SUBARG_1}
        terminate with error
    }
}

Language

This article links to detailed descriptions of all the statements supported by USE script.

These descriptions assume knowledge of the USE script basics.

Statement reference

aws_sign_string

The aws_sign_String statement is used to generate an AWS4-HMAC-SHA256 signature, used as the signature component of the Authorization HTTP header when calling the AWS API.

Syntax

aws_sign_stringvarNameusingsecret_key date region service

Details

The authentication method used by AWS requires the generation of an authorization signature which is derived from a secret key known to the client along with specific elements of the query being made to the API.

This is a fairly involved process and a full step-by-step walkthrough is provided by Amazon on the following pages (these should be read in the order listed below):

The aws_sign_string statement is used to generate the final signature as detailed on the calculate signature page listed above.

Note that in order to use this statement it is necessary to have the following strings available:

A string to sign, obtained by following the process creating a string to sign, containing meta-data about the request being made
A secret_key, obtained from Amazon which is used by any client application authorising against their API
The date associated with the API request, in YYYYMMDD format
The AWS region associated with the API request (for example eu-central-1)
The AWS service being accessed (for example s3)

The aws_sign_string statement will use these inputs to generate the HMAC-SHA256 signature which is a component of the Authorization header when connecting to the API itself.

The varName parameter is the name of a variable containing the string to sign. After executing aws_sign_string the contents of this same variable will have been updated to the base-16 encoded signature value.

If there are any errors in the string to sign, _date, AWS region or AWS service strings used as input to aws_sign_string then a signature will still be generated, but the AWS API will reject the request. In this case it is necessary to review the process by which these strings were created as per the AWS guide provided above.

Example

The following is an example USE script that implements everything described above.

#################################################################
# This USE script will download a file from an S3 bucket        #
#                                                               #
# It takes three parameters:                                    #
# 1) The name of the bucket                                     #
# 2) The name of the object to download                         #
# 3) The name of the file to save the downloaded object as      #
#                                                               #
# Created: 13th Jan 2018                                        #
# Author: Eddy Deegan                                           #
# --------------------------------------------------------------#
# NOTES:                                                        #
# - This script hardcodes the Region as eu-central-1 but this   #
#   can easily be changed or made a parameter as required       #
#################################################################

if (${ARGC} != 3) {
    print This script requires the following parameters:
    print bucketName objectName saveFilename
    terminate
}

# Set this to 1 to enable a debug trace output when the script is run
var DEBUG = 0

# This is the text that appears to the left and right of debug headings 
var banner = ________

######################################################################
# Customer specific values here (these can be encrypted if required) #
#                                                                    #
var bucket = "${ARG_1}"
var s3_object = "${ARG_2}"
var AWS_Region = "eu-central-1"
var AWS_Service = "s3"
encrypt var access_key = <YOUR ACCESS KEY>
encrypt var secret_key = <YOUR SECRET KEY>
#                                                                    #
# End customer specific values                                       #
######################################################################

# This is the SHA256 hash of an empty string (required if making a request with no body)
var hashed_empty_string = e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855

#########################################################################################
# SETUP                                                                                 #
# Create a number of variables to represent the various components that the steps       #
# below are going to use in order to construct a correct AWS request                    #
#---------------------------------------------------------------------------------------#
# This is the request syntax for retrieving an object from a bucket:                    #
# GET /<ObjectName> HTTP/1.1                                                            #
# Host: <BucketName>.s3.amazonaws.com                                                   #
# Date: date                                                                            #
# Authorization: authorization string                                                   #
#########################################################################################

var HTTP_Method = GET
var URI = ${s3_object}
var query_params                    # Must have an empty variable for 'no query parameters'
var host = ${bucket}.s3-${AWS_Region}.amazonaws.com
var date = ${OSI_TIME_UTC}

# Initialise config variables specific to this script
var save_path = "system/extracted"
var save_file = ${ARG_3}

#########################################################################################
# STEP 1                                                                                #
# Create a canonical request as documented at                                           #
# at https://docs.aws.amazon.com/general/latest/gr/sigv4-create-canonical-request.html  #
#########################################################################################

# 1a) Canonical Headers string
#     - This is part of the Canonical Request string which will be generated below.
#     - The Canonical Headers are a list of all HTTP headers (including values but
#       with the header names in lowercase) separated by newline characters and in
#       alphabetical order

var canonical_headers = "date:${date}${NEWLINE}host:${host}${NEWLINE}x-amz-content-sha256:${hashed_empty_string}${NEWLINE}"
if (${DEBUG} == 1) {
    print ${NEWLINE}${banner} Canonical Headers ${banner}${NEWLINE}${canonical_headers}
}

# 1b) Signed Headers string
#     - This is a list of the header names that were used to create the Canonical Headers,
#       separated by a semicolon
#     - This list MUST be in alphabetical order
#     - NOTE: There is no trailing newline on this variable (we need to use it both with and without
#             a newline later so we explicitly add a ${NEWLINE} when we need to)

var signed_headers = "date;host;x-amz-content-sha256"
if (${DEBUG} == 1) {
    print ${banner} Signed Headers ${banner}${NEWLINE}${signed_headers}${NEWLINE}
}

# 1c) Canonical Request
#     - The above are now combined to form a Canonical Request, which is created as follows:
#     - HTTPRequestMethod + '\n' + URI + '\n' + QueryString + '\n' + CanonicalHeaders + '\n' +
#       SignedHeaders + '\n' + Base16 encoded SHA256 Hash of any body content
#     - Note that the Canonical Headers are followed by an extra newline (they have one already)

var canonical_request = "${HTTP_Method}${NEWLINE}/${URI}${NEWLINE}${query_params}${NEWLINE}${canonical_headers}${NEWLINE}${signed_headers}${NEWLINE}${hashed_empty_string}"
if (${DEBUG} == 1) {
    print ${banner} Canonical Request ${banner}${NEWLINE}${canonical_request}${NEWLINE}
}

# 1d) Hash of the Canonical Request
#     - This is an SHA256 hash of the Canonical Request string

hash sha256 canonical_request as hashed_canonical_request

######################################################################################
# STEP 2                                                                             #
# Create a 'string to sign' as documented at                                         #
# at https://docs.aws.amazon.com/general/latest/gr/sigv4-create-string-to-sign.html  #
#------------------------------------------------------------------------------------#
# In a nutshell this is the following components separated by newlines:              #
# 2a) Hash algorithm designation                                                     #
# 2b) UTC date in YYYYMMDD'T'HHMMSS'Z' format                                        #
# 2c) credential scope (date/region/service/"aws4_request")                          #
# 2d) base16-encoded hashed canonical request                                        #
######################################################################################

# Extract the yyyyMMdd from the UTC time
match yyyyMMdd "(.{8})" ${date}
var yyyyMMdd = ${yyyyMMdd.RESULT}

var string_to_sign = AWS4-HMAC-SHA256${NEWLINE}${date}${NEWLINE}${yyyyMMdd}/${AWS_Region}/${AWS_Service}/aws4_request${NEWLINE}${hashed_canonical_request}
if (${DEBUG} == 1) {
    print ${banner} String to sign ${banner}${NEWLINE}${string_to_sign}${NEWLINE}
}

######################################################################################
# STEP 3                                                                             #
# Calculate the signature for AWS Signature Version 4 as documented at:              #
# at https://docs.aws.amazon.com/general/latest/gr/sigv4-calculate-signature.html    #
#                                                                                    #
######################################################################################

# 3a) Derive a signing key and apply it to the string to sign
#     Use the secret access key to create the following hash-based auth codes:
#     a) ksecret (our secret access key)
#     b) kDate = HMAC("AWS4" + kSecret, Date) NOTE: yyyyMMdd only
#     c) kRegion = HMAC(kDate, Region)
#     d) kService = HMAC(kRegion, Service)
#     e) kSigning = HMAC(kService, "aws4_request")
#     f) HMAC the string_to_sign with the key derived using steps a - e

var signature = ${string_to_sign}

if (${DEBUG} == 1) {
    print ${banner}Deriving Signing Key using these parameters${banner}${NEWLINE}${secret_key} ${yyyyMMdd} ${AWS_Region} ${AWS_Service}${NEWLINE}${NEWLINE}
}

# The following statement takes care of all the details listed above
# Notes: 
#       - The word 'signature' in the statement below is the NAME of a variable and
#         NOT a reference to its contents
#       - The contents of this variable are the string to sign, and after the statement
#         has completed these contents will have been modified to be the authorization
#         signature for that string
#
AWS_sign_string signature using ${secret_key} ${yyyyMMdd} ${AWS_Region} ${AWS_Service}

######################################################################################
# STEP 4                                                                             #
# Add the signing information to the request as documented at:                       #
# https://docs.aws.amazon.com/general/latest/gr/sigv4-add-signature-to-request.html  #
#                                                                                    #
######################################################################################

var credential_scope = "${yyyyMMdd}/${AWS_Region}/${AWS_Service}/aws4_request"
if (${DEBUG} == 1) {
    print ${banner} Credential Scope ${banner}${NEWLINE}${credential_scope}${NEWLINE}${NEWLINE}
}

var auth_header = "Authorization: AWS4-HMAC-SHA256 Credential=${access_key}/${credential_scope}, SignedHeaders=${signed_headers}, Signature=${signature}"

if (${DEBUG} == 1) {
    print ${banner} Authorization Header ${banner}${NEWLINE}${auth_header}${NEWLINE}
}
set http_header ${auth_header}

#######################################################
# STEP 5                                              #
# Execute the query                                   #
#-----------------------------------------------------#
# Note that all the headers that were included in the #
# signed_headers created in STEP 1 must be set before #
# the request is executed                             #
#######################################################

set http_header "Date: ${date}"
set http_header "x-amz-content-sha256: ${hashed_empty_string}"
set http_savefile ${save_path}/${save_file}

set http_progress yes
print "Downloading ${host}/${URI}:"
http GET https://${host}/${URI}
print ${NEWLINE}Done

basename

The basename statement is used to extract the filename portion of a path + filename string

Syntax

basenamevarName

basenamestringasvarName

Details

Given a string describing the full path of a file, such as /extracted/test/mydata.csv the basename statement is used to identify the filename (including the file extension, if any) portion of that string only. If there are no path delimiters in the string then the original string is returned.

The basename statement supports both UNIX-style (forward slash) and Windows-style (backslash) delimiters.

When invoked as basename varName, the varName parameter must be the name of the variable containing the string to analyse. The value of the variable will be updated with the result so care should be taken to copy the original value to a new variable beforehand if the full path may be required later in the script.

As a convenience in cases where the full path needs to be retained, the result of the operation can be placed into a separate variable by using the form basename string as varName where string is the value containing the full path + filename and varName is the name of the variable to set as the result.

When invoked using basename string as varName if a variable called varName does not exist then it will be created, else its value will be updated.

Examples

Example 1

The following script ...

var path = "extracted/test/testdata.csv"

# Copy the path as we'll need it later
var file = ${path}

# Note: use the NAME of the variable not the value
basename file

# The variable called 'file' now contains the result
print The basename of the path '${path}' is '${file}'

var path = "testdata.csv"
var file = ${path}
basename file

print The basename of the path '${path}' is '${file}'

... will produce the following output:

The basename of the path 'extracted/test/testdata.csv' is 'testdata.csv'
The basename of the path 'testdata.csv' is 'testdata.csv'

Example 2

The following script ...

var path = "extracted/test/testdata.csv"
basename ${path} as file
print The basename of the path '${path}' is '${file}'

... will produce the following output:

The basename of the path 'extracted/test/testdata.csv' is 'testdata.csv'

buffer

The buffer command is used to create and/or populate one of these named buffers with data.

Syntax

buffername=protocol protocol_parameter(s)

Details

The first argument to the buffer statement is the name of the buffer to create. If a buffer with this name already exists then any data it contains will be overwritten.

There must be whitespace on both sides of the 'equals' symbol following the buffer name.

The following protocols are supported:

file

bufferbuffername= filefilename

The file protocol imports a file directly into a buffer. This can be very useful when developing USE scripts, as the USE script for processing for a JSON file (for example) can be implemented without requiring access to a server.

If the specified buffer name already exists, then a warning will be logged and any data in it will be cleared before importing the file.

data

bufferbuffername= datastring

The data protocol populates the buffer with the literal text specified in string. This is useful when extracting embedded JSON. For example the JSON snippet below contains embedded JSON in the instanceData field:

"properties": {
"subscriptionId":"sub1.1",
"usageStartTime": "2015-03-03T00:00:00+00:00",
"usageEndTime": "2015-03-04T00:00:00+00:00",
"instanceData":"{\"Microsoft.Resources\":{\"resourceUri\":\"resourceUri1\",\"location\":\"Alaska\",\"tags\":null,\"additionalInfo\":null}}",
"quantity":2.4000000000,
"meterId":"meterID1"

}

In this case the instanceData field can be extracted using a parslet, placed into a new buffer and re-parsed to extract the values within it. Assuming the snippet is in a file called my_data.json this would be done as follows:

buffer properties = file my_data.json
var instanceData = $JSON{my_data}.[properties].[instanceData]

buffer embedded = data ${instanceData}
print The embedded resourceUri is $JSON{embedded}.[Microsoft.Resources].[resourceUri]

http

bufferbuffername= httpmethod url

!!! note For full details on the HTTP protocol and its parameters please refer to the http article.

Once the HTTP request has been executed, any data it returned will be contained in the named buffer, even if the data is binary in format (eg: images, audio files or anything else non-human readable).

If the HTTP request returned no data, one of the following will apply:

If the buffer does not already exist then the buffer will not be created
If the buffer already exists then it will be deleted altogether

For details of how to access the data in a named buffer, please refer to the USE script basics article.

odbc

bufferbuffername= odbcdsn [username password] query

username and password are optional, but neither or both must be specified

where:

dsn is the ODBC Data Source Name (this should be configured at the OS level)
username and password are the credentials required by the DSN
query is an SQL query

Once the query has been executed, the resulting data is located in the named buffer. It can subsequently be saved as a CSV file to disk using:

save {buffername} as filename.csv

The resulting CSV uses a comma (,) as the separator and double quotes (") as the quoting character. Any fields in the data which contain a comma will be quoted.

odbc_direct

bufferbuffername= odbc_directquery

where query is an SQL query.

Executes SQL query against ODBC datasource that is described in set's odbc_connect parameter.

Once the query has been executed, the resulting data is located in the named buffer. It can subsequently be saved as a CSV file to disk using:

save {buffername} as filename.csv

The resulting CSV uses a comma (,) as the separator and double quotes (") as the quoting character. Any fields in the data which contain a comma will be quoted.

Examples

The following examples retrieve data from ODBC and HTTP sources:

# Typical usage in USE script to retrieve all data from the usage table

buffer odbc_csv = odbc ExivityDB admin secret "select * from usage"
save {odbc_csv} as "odbc.csv"
discard {odbc_csv}

# Retrieve the service summary from a local CloudCruiser 4 server and place it in a buffer
set http_username admin
set http_password admin
set http_authtype basic

buffer services = http GET "http://localhost:8080/rest/v2/serviceCatalog/summaries"
# The 'services' buffer now contains the HTTP response data

csv

The csv statement is used to create and populate CSV files. It is typically combined with foreach loops to write values extracted from an array in a JSON and/or XML document stored in a named buffer.

Details

CSV files are produced via the use of multiple csv statements which perform the following functions:

Create a new empty CSV file
Define the headers
Finalise the headers
Write data to one or more rows of the file
Close the file

All CSV files created by the csv command use a comma - , - as the separator character and a double quote - " - as the quote character. Headers and data fields are automatically separated and quoted.

Create a new CSV file

The following is used to create a new, empty CSV file:

csvlabel = filename

The label must not be associated with any other open CSV file. Up to 16 CSV files may be open simultaneously and the label is used by suesequent csv statements to determine which of the open files the statement should operate on. Labels are case sensitive and may be from 1 to 15 characters in length.

The specified filename is created immediately, and if it is the name of an existing file then it will be truncated to 0 bytes when opened.

The filename argument may contain a path component but the csv statement does not create directories, so any path component in the filename must already exist. The path, if specified, will be local to the Exivity home directory.

Example

csv usage = "${exportdir}/azure_usage.csv"

Define the headers

This section refers to add_headers as the action, but either add_header or add_headers may be used. Both variants work in an identical fashion.

csv add_headerslabel header1 [header2 ... headerN]

All CSV files created by USE script must start with a header row which names the columns in the file. The number of columns can vary from file to file, but in any given file every data row must have the same number of columns as there are headers.

To create one or more columns in a newly created CSV file, the csv add_headers statement is used as shown above. The label must match the label previously associated with the file as described previously.

One or more header names can be specified as arguments to csv add_headers. Multiple instances of the csv add_headers statement may reference the same CSV file, as each statement will append additional headers to any headers already defined for the file.

No checks are done to ensure the uniqueness of the headers. It is therefore up to the script author to ensure that all the specified headers in any given file are unique.

Example

csv add_headers usage username user_id subscription_id

Finalise the headers

This section refers to fix_headers as the action, but either fix_header or fix_headers may be used. Both variants work in an identical fashion

csv fix_headerslabel

After csv add_headers has been used to define at least one header, the headers are finalised using csv fix_headers statement. Once the headers have been fixed, no further headers can be added to the file and until the headers have been fixed, no data can be written to the file.

Example

csv fix_headers usage

Write data

This section refers to write_fields as the action, but either write_field or write_fields may be used. Both variants work in an identical fashion

csv write_fieldslabel value1 [value2 ... valueN]

After the headers have been fixed, the csv write_fields statement is used to write one or more fields of data to the CSV file. Currently it is not possible to write a blank field using csv write_fields, however when extracting data from a buffer using a parslet, if the extracted value is blank then it will automatically be expanded to the string (no value).

USE keeps track of the rows and columns as they are populated using one or more csv write_fields statements, and will automatically write the fields from left to right starting at the first column in the first data row and will advance to the next row when the rightmost column has been written to.

It is the responsibility of the script author to ensure that the number of fields written to a CSV file is such that when the file is closed, the last row is complete, in order to avoid malformed files with one or more fields missing from the last row.

Example

csv write_fields usage Eddy 47EF-26EA-AAF1-B199 SUB_2311_89EFAA1273
csv write_fields usage Tim 2492-ACC2-8829-4444 SUB_2991_BBAFE20BBA

Close the file

csv closelabel

Once all fields have been written to a CSV file, it must be closed using the csv close statement. This will ensure that all data is properly flushed to disk, and will free the label for re-use.

Example

csv close usage

Example

Consider the file "\examples\json\customers.json" representing two customers:

    {
      "totalCount": 2,
      "items": [
        {
          "id": "1234-4567",
          "companyProfile": {
            "tenantId": "xyz-abc",
            "domain": "example.domain.com",
            "companyName": "Example, Inc"
          }
        },
        {
          "id": "9876-6543",
          "companyProfile": {
            "tenantId": "stu-vwx",
            "domain": "another.domain.com",
            "companyName": "A Company, Inc"
          }
        }
      ]
    }

Using a combination of foreach loops and parslets, the information in the above JSON can be converted to CSV format as follows:

        # Load the file into a named buffer
        buffer customers = FILE "${baseDir}\examples\json\customers.json"

        # Create an export file
        csv "customers" = "${baseDir}\exported\customers.csv"

        # Initialise and fix the headers (using two 'add_headers' statements for illustration)
        csv add_headers "customers" id tenant_id 
        csv add_headers "customers" domain company_name
        csv fix_headers "customers"

        # Iterate over the 'items' array in the JSON
        foreach $JSON{customers}.[items] as this_item
        {
            csv write_field "customers" $JSON(this_item).[id]
            csv write_field "customers" $JSON(this_item).[companyProfile].[tenantId]
            csv write_field "customers" $JSON(this_item).[companyProfile].[domain]
            csv write_field "customers" $JSON(this_item).[companyProfile].[companyName]
        }

        # Tidy up
        csv close "customers"
        discard {customers}

The resulting CSV file is as follows:

    "id","tenant_id","domain","company_name"
    "1234-4567","xyz-abc","example.domain.com","Example, Inc"
    "9876-6543","stu-vwx","another.domain.com","A Company, Inc"

clear

The clear statement is used to delete all HTTP headers previously configured using the set http_header statement.

Syntax

clear http_headers

Details

The clear statement will remove all the headers currently defined, after which a new set of headers can be specified using set http_header.

Example

set http_header "Accept: application/json"
set http_header "Authorization: FFDC-4567-AE53-1234"    
set http_savefile "d:\exivity\customers.json"
buffer customers = http GET "https://demo.server.com:4444/v1/customers"

clear http_headers   # Clear headers in order to use a different Authorization: value
set http_header "Accept: application/json"
set http_header "Authorization: ABCD-EFGH-8888-1234"    
set http_savefile "d:\exivity\addresses.json"
buffer customers = http GET "https://demo.server.com:4444/v1/addresses"

discard

The discard statement is used to delete a named buffer.

Syntax

discard{buffer_name}

Details

The discard statement will delete the named buffer and free the memory used to store its contents. The statement takes immediate effect and any attempt to reference the buffer afterwards (at least until such time as another buffer with the same name is created) will cause the USE script to log an error and fail.

Example

var server = "https://my_json_server.com"
buffer response = http GET ${server}/generatetoken        

# Create a variable called ${secret_token} from the 'access_token'
# string in the JSON in the {response} buffer
var secret_token = $JSON{response}.[access_token]

# We no longer need the {response} buffer as the value extracted
# from it is stored in a variable
discard {response}

encode

The encode statement is used to base16 or base64 encode the contents of a variable or a named buffer.

Syntax

encode base16|base64varName|{buffer_name}

Details

The encode statement will encode the contents of an existing variable or named buffer, replacing those contents with the encoded version.

The result of encoding the contents will increase their length. With base16 encoding the new length will be double the original. With base64 encoding the new length will be greater than the original but the exact size increase will depend on the contents being encoded.

When encoding a variable, if the size of the result after encoding exceeds the maximum allowable length for a variable value (8095 characters) then the USE script will fail and an error will be returned.

Encoding an empty variable or buffer will produce an empty result

Example

The following script ...

var testdata = "Text to be encoded"

print Encoding a variable ...
# Base16-encode a variable
var encode_me = ${testdata}
encode base16 encode_me
print Encoded base16 result is: ${encode_me}

# Base64-encode a variable
var encode_me = ${testdata}
encode base64 encode_me
print Encoded base64 result is: ${encode_me}

print Encoding a buffer ...
# Base16-encode a buffer
buffer encode_buf = data ${testdata}
encode base16 {encode_buf}
print Encoded base16 result is: {encode_buf}

# Base64-encode a buffer
buffer encode_buf = data ${testdata}
encode base64 {encode_buf}
print Encoded base64 result is: {encode_buf}

... produces the following output:

Encoding a variable ...
Encoded base16 result is: 5465787420746F20626520656E636F646564
Encoded base64 result is: VGV4dCB0byBiZSBlbmNvZGVk
Encoding a buffer ...
Encoded base16 result is: 5465787420746F20626520656E636F646564
Encoded base64 result is: VGV4dCB0byBiZSBlbmNvZGVk

encrypt

This article assumes knowledge of variables.

The encrypt statement is used to conceal the value of a variable, such that it does not appear in plain text in a USE script.

Syntax

encrypt varname = value_to_be_encrypted

Details

The encrypt statement differs from other statements in that it takes effect before execution of a USE script begins. In this regard is is effectively a directive to the internal script pre-processor which prepares a script for execution.

Comments, quotes and escapes in the value to be encrypted are treated as literal text up until the end of the line.

White-space following the value to be encrypted will therefore be included in the encrypted result.

White-space preceding the value to be encrypted will be ignored and will not be included in the encrypted result.

Encrypting one or more variables

Any variable prefixed with the word encrypt will be encrypted by the pre-processor and the script file itself will be modified as follows:

All text (including trailing white-space) from the word following the = character up to the end of the line is encrypted
The encrypted value is base64 encoded
The original variable value in the USE script is substituted with the result
The encrypt keyword for that variable is changed to encrypted
The USE script is overwritten on disk in this new form

This process is repeated for all variables preceded by the encrypt keyword.

As a side effect of the encryption process, it is not currently possible to encrypt a value that begins with a space or a tab. This functionality will be implemented in due course.

Using encrypted variables

Once encrypted a variable can be used just as any other, the only requirement being that the encrypted keyword preceding its declaration is not removed or modified.

To change the value of an encrypted variable simply replace the declaration altogether and precede the new declaration with encrypt. Upon first execution, the USE script will be updated with an encrypted version of the variable as described above.

Encrypted values can only be used on the system that they were created on. If an encrypted value is moved or copied to a different installation of Exivity then any attempt to reference or decrypt it will result in something other than the original value.

Example

Firstly, create the script as usual, with encrypt preceding any variables that are to be encrypted:

# ---- Start Config ----
encrypt var username = admin
encrypt var password = topsecret
var server = "http://localhost"
var port = 8080
var api_method = getdetails
# ---- End Config ----

set http_authtype basic
set http_username ${username}
set http_password ${password}

buffer {response} = http GET ${server}:${port}/rest/v2/${api_method}

Secondly, run the script. Prior to execution the script will be automatically modified as shown below:

# ---- Start Config ----
encrypted var username = AGF5dU0KJaB+NyHWu2lkhw==
encrypted var password = b0Sa29tyL+M8wix/+JokjMCdeMwiY9n5
var server = "http://localhost"
var port = 8080
var api_method = getdetails
# ---- End Config ----

set http_authtype basic
set http_username ${username}
set http_password ${password}

buffer {response} = http GET ${server}:${port}/rest/v2/${api_method}

environment

The environment statement specifies the name of environment to use for resolving global variables.

Syntax

environment name

Details

The environment statement selects the predefined environment to use for global variable lookup. It is and error to specify the environment which is not defined in global database.

If no environment specified, default environment (the one specified as default in global database) is assumed.

Environment can be changed many times without limitations, and change affects only global variables that are referenced first time within the script, e.g. all global variables, resolved (copied to local variables) retain their values.

escape

The escape statement is used to escape quotes in a variable value or the contents of a named buffer

Syntax

escape quotes invarName|{bufferName}[usingescape_char]

Details

If a variable value or named buffer contains quotes then it may be desirable to escape them, either for display purposes (to prevent USE from removing them before rendering the data as output) or in order to satisfy the requirements of an external API.

The escape statement will precede all occurrences of the character " with a specified escape character (backslash by default) as shown in the example below. This operation is not just temporary - it will update the actual contents of the variable or named buffer.

The escape statement does not take into account the context of existing quote characters in the data. Running it multiple times against the same data will add an additional escape character each time to each occurrence of a quote.

Example

Given an input file called 'escapeme.txt' containing the following data:

"this "is some text" with
some "quotes" in it"

The following script:

buffer test = FILE system/extracted/escapeme.txt
escape quotes in {test}
print {test}

var testvar = "\"This is \"a test\" string\""
escape quotes in testvar using \"
print ${testvar}

will produce the following output:

\"this \"is some text\" with
some \"quotes\" in it\"

""This is ""a test"" string""

exit_loop

The exit_loop statement will terminate the current loop.

Either exit_loop or loop_exit may be used. Both variants work identically.

Syntax

exit_loop

Details

The exit_loop statement will immediately terminate the current loop and script execution will jump to the statement following the } at the end of the current loop.

This can be done even if the exit_loop statement is within one or more if constructs inside the loop.

If no loop is in effect then an error will be logged and the script will terminate.

foreach

The foreach statement defines a block of zero or more statements and associates this block with multiple values. The block of statements is executed repeatedly, once for each value.

Syntax

foreachparsletasloop_label{

`# Zero or more USE script statements go here`

}

The opening { may be placed on a line of its own if preferred

Details

The foreach statement is used to iterate over the values in an array or object (identified by a parslet) within the data in a named buffer.

The loop will execute for as many elements as there are in the array, or for as many members there are in the object. For the purposes of this documentation, the term child will be used to refer to a single array element or object member.

If the array or object is empty, then the body of the loop will be skipped and execution will continue at the statement following the closing }.

The loop_label can be any string, but must not be the same as any other loop_label values in the same scope (ie: when nesting foreach loops, each loop must have a unique label). This label is used to uniquely identify any given loop level when loops are nested.

The foreach statement will execute the statements in the body of the loop once for every child. foreach loops can be nested, and at each iteration the loop_label can be used to extract values from an array or object in the current child using a dynamic parslet. See the examples at the end of this article for a sample implementation showing this in action.

As the foreach loop iterates over the children, a number of variables are automatically created or updated as follows:

Examples

Basic looping

Consider the following JSON in a file called samples/json/array.json:

{
    "totalCount" : 2,
    "items" : [
        {
            "name" : "Item number one",
            "id" : "12345678"
        },
        {
            "name" : "Item number two",
            "id" : "ABCDEFGH"
        }
    ]
}

To generate a list of IDs and names from the items array, the following would be used:

buffer data = file "samples/json/array.json"

foreach $JSON{data}.[items] as this_item
{
    print Customer ${this_item.COUNT}: $JSON(this_item).[id] $JSON(this_item).[name]
}

discard {data}

Nested looping

To extract values from an array using nested loops:

Given the source JSON in a file called example.json, the following USE script:

var JSON_dir = "examples\json"
buffer example = FILE "${JSON_dir}\doc.json"

var title = $JSON{example}.[title]

# For every element in the 'items' array ...
foreach $JSON{example}.[items] as this_item
{
    var item_name = $JSON(this_item).[name]

    # For every child of the 'subvalues' object ...
    foreach $JSON(this_item).[subvalues] as this_subvalue
    {
        var sub_name = ${this_subvalue.NAME}
        var sub_value = ${this_subvalue.VALUE}

        # Render an output line
        print ${title} -> Item: ${item_name} -> Subvalue:${sub_name} = ${sub_value} 
    }
}
discard {example}

will produce the following output:

Example JSON data -> Item: Item number one -> Subvalue:0 = 1
Example JSON data -> Item: Item number one -> Subvalue:10 = 42
Example JSON data -> Item: Item number one -> Subvalue:100 = 73
Example JSON data -> Item: Item number one -> Subvalue:1000 = 100
Example JSON data -> Item: Item number two -> Subvalue:0 = 10
Example JSON data -> Item: Item number two -> Subvalue:10 = 442
Example JSON data -> Item: Item number two -> Subvalue:100 = 783
Example JSON data -> Item: Item number two -> Subvalue:1000 = 1009

generate_jwt

The generate_jwt statement is used to generate an RFC 7515-compliant JWT (JSON Web Token) which can be used, for example, for Google Cloud OAuth 2.0 Server to Server Authentication.

Syntax

generate_jwt keykey component1 [... componentN]asresult

Details

The generate_jwt statement performs the following actions:

encodes all components as Base64URL
concatenates all components using a dot separator (.)
hashes the concatenated result using SHA256
signs the hash with a provided PEM-encoded key using the RSA algorithm
encodes the resulting signature as Base64URL
builds JWT by concatenating the two results using a dot separator (.)
stores the final result in th variable specified by the result parameter

The RSA key needs to be in PEM format. PEM format requires the header and footer to be on separate lines so it is important to separate the key contents with ${NEWLINE}as shown below:

var key = "-----BEGIN PRIVATE KEY-----${NEWLINE}Key-data-goes-here{$NEWLINE}-----END PRIVATE KEY-----"

Example

To acquire a Google Cloud OAuth 2.0 access token:

var private = "-----BEGIN PRIVATE KEY-----${NEWLINE}key goes here${NEWLINE}-----END PRIVATE KEY-----"
var email = "user@account.iam.gserviceaccount.com"
var url = "https://www.googleapis.com/oauth2/v4/token"
var scope = "https://www.googleapis.com/auth/cloud-platform"

var now = ${UNIX_UTC}
var expiry = (${now} + 3600)

var header = "{\"alg\":\"RS256\",\"typ\":\"JWT\"}"
var payload = "{\"iss\":\"${email}\",\"scope\":\"${scope}\",\"aud\":\"${url}\",\"iat\":\"${now}\",\"exp\":\"${expiry}\"}"

generate_jwt key ${private} ${header} ${payload} as JWT

# Make HTTP request according to https://developers.google.com/identity/protocols/OAuth2ServiceAccount
set http_header "Content-Type: application/x-www-form-urlencoded"
set http_body data "grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=${JWT}"
buffer token = HTTP POST "${url}"

if (${HTTP_STATUS_CODE} != 200) {
	print Got HTTP status ${HTTP_STATUS_CODE}, expected a status of 200
	print The server response was:
	json format {token} 
	print {token}
	terminate
}

var access_token = $JSON{token}.[access_token]
print Access token: ${access_token}

get_last_day_of

The days_in_month statement sets a variable to contain the number of days in the specified month

Syntax

get_last_day_ofyyyyMMasvarName

Details

The get_last_day_of statement will set the value of the variable called varName to contain the number of days in the month specifed by yyyyMM where yyyy is a four-digit year and MM is a 2-digit month.

The statement will take leap years into account.

Example

#
# Check a specific date to see if it is the last day of a month
#
var somedate = 20180228
gosub detect_end_of_month(${somedate})

if (${is_last_day} == TRUE) {
    print ${somedate} is the last day of a month
} else {
    print ${somedate} is not the last day of a month
}

#
# Check todays date to see if it is the last day of the month
#
gosub detect_end_of_month()
if (${is_last_day} == TRUE) {
    print Today is the last day of the month
} else {
    print Today is not the last day of the month
}

# This subroutine determines whether a date is the last
# day of a month or not
#
# If no argument is provided it defaults to the current system
# time, else it uses the supplied yyyyMMdd format argument
#
# It sets a variable called 'is_last_day' to TRUE or FALSE

subroutine detect_end_of_month {

    if (${SUBARG.COUNT} == 0) {
        get_last_day_of ${YEAR}${MONTH} as last_day

        if (${last_day} == ${DAY}) {
            var is_last_day = TRUE
        } else {
            var is_last_day = FALSE
        }
        return
    }

    # Verify argument format
    match date "^([0-9]{8})$" ${SUBARG_1}
    if (${date.STATUS} != MATCH) {
        print Error: the provided argument is not in yyyyMMdd format
        terminate with error
    }

    # Get the day portion of the argument    
    match day "^[0-9]{6}([0-9]{2})$" ${SUBARG_1}
    var day_to_check = ${day.RESULT}

    # Get the yyyyMM portion of the argument
    match yyyyMM "^([0-9]{6})" ${SUBARG_1}
    var month = ${yyyyMM.RESULT}

    get_last_day_of ${month} as last_day

    if (${last_day} == ${day_to_check}) {
        var is_last_day = TRUE
    } else {
        var is_last_day = FALSE
    }
}

gosub

The gosub keyword is used to run a named subroutine

Syntax

gosub subroutineName([argument1, ... argumentN])

The argument list may span multiple lines, so long as any given argument is contained on a single line and ends with a comma, eg:

gosub subroutineName (argument1,
      argument2,
      argument3,
      )

Details

The subroutineName provided to the gosub statement must be that of a subroutine defined elsewhere in the script using the subroutine statement.

If any argument contains white-space or a comma then it must be quoted:

gosub getfile("directory with spaces/filename.txt")

It is permitted to call a subroutine from within another subroutine, therefore gosub can be used within the body of a subroutine. This may be done up to 256 levels in depth.

The opening bracket after subroutineName may or may not be preceded with a space:

gosub getfile ("filename.txt")

To call a subroutine with no parameters, use empty brackets:

gosub dosomething()

Example

Please refer to the example in the documentation for the subroutine statement

gunzip

The functionality described in this article is not yet available. This notice will be removed when the appropriate release is made.

The gunzip statement is used to inflate a GZIP file

Syntax

gunzip filename as filename

gunzip {bufferName} as filename

Details

The gunzip statement can be used to extract the contents of a GZIP archive containing a single file. The GZIP archive may be a file on disk or may be the contents of a named buffer.

It is not possible to inflate GZIP data directly in memory, but the same effect can be achieved by extracting GZIP data in a named buffer to disk, and then loading the extracted data back into the named buffer as shown in the example below.

All paths and filenames are treated as relative to the Exivity home directory

Example

# Download an archive and extract it into a named buffer
buffer archivedata = http GET http://server/archived.csv.gz
gunzip {archivedata} as system/extracted/extracted.csv
buffer archivedata = FILE system/extracted/extracted.csv

# Download an archive and extract it to disk, automatically deriving the
# output filename from the input filename based on the .gz extension
var save_path = system/extracted
var archivefile = extracted.csv.gz
set http save_file ${save_path}/${archivefile}

match csv_name "(.*)\.gz$" ${filename}
if (${csv_name.STATUS} != MATCH) {
    print WARNING: Downloaded file does not end in .gz and will not be extracted
} else {
    gunzip "${save_path}/${archivefile}" as "${save_path}/${csv_name.RESULT}"
    print Extracted file: "${save_path}/${csv_name.RESULT}"
}

hash

The hash statement is used to generate a base-16 or base-64 encoded hash of data stored in a variable or named buffer.

Syntax

hash sha256 [HMAC [b16|b64]key] target|{target}asresult[b16|b64]

hash md5target|{target}asresult[b16|b64]

Details

The hash statement uses the contents of target as its input and places the final result into result. The SHA256 and MD5 hash algorithms are supported.

If target is surrounded with curly braces like {this} then it is taken to be the name of a memory buffer and the contents of the buffer will be used as input. Otherwise, it is treated as the name of the variable, the value of which will be hashed.

By default the resulting hash is base-16 encoded and the result placed into the variable specified by the result argument.

result is the name of the variable to put the output into, and not a reference to the contents of that variable. This is why it is not ${result}

If the optional HMACkey arguments are provided when the hash type is sha256 then the secret in key will be used to generate an HMAC-SHA-256 result. The optional b64 or b16 argument following the HMAC option indicates that key is base-64 or base-16 encoded. By default, a clear-text key is assumed.

If the optional b64 argument is used (base64 may also be specified) after the result variable, then the result will be base-64 encoded.

The optional b16 argument (base16 may also be used) after the result variable is provided for completeness, but need not be specified as this is the default encoding to use.

Example

Running the script:

var hash_me = "This is the data to hash"
var my_secret = "This is my secret key"

# SHA256
hash sha256 hash_me as result
print The SHA256 hash of '${hash_me}' in base-16 is:
print ${result}${NEWLINE}

hash sha256 hash_me as result b64
print The SHA256 hash of '${hash_me}' in base-64 is:
print ${result}${NEWLINE}

# HMACSHA256
hash sha256 hmac ${my_secret} hash_me as result
print The HMACSHA256 hash of '${hash_me}' (using '${my_secret}') in base-16 is:
print ${result}${NEWLINE}

hash sha256 hmac ${my_secret} hash_me as result b64
print The HMACSHA256 hash of '${hash_me}' (using '${my_secret}') in base-64 is:
print ${result}${NEWLINE}

results in the following output:

The SHA256 hash of 'This is the data to hash' in base-16 is:
1702c37675c14d0ea99b7c23ec29c36286d1769a9f65212218d4380534a53a7a

The SHA256 hash of 'This is the data to hash' in base-64 is:
FwLDdnXBTQ6pm3wj7CnDYobRdpqfZSEiGNQ4BTSlOno=

The HMACSHA256 hash of 'This is the data to hash' (using 'This is my secret key') in base-16 is:
cf854e99094ea5c2a88ee0901a305d5f25dfb5a0f0905eec703618080567b4b5

The HMACSHA256 hash of 'This is the data to hash' (using 'This is my secret key') in base-64 is:
z4VOmQlOpcKojuCQGjBdXyXftaDwkF7scDYYCAVntLU=

http

The http statement initiates an HTTP session using any settings previously configured using the set statement. It can also be used for querying response headers.

Syntax

httpmethod url

http dump_headers

http get_headerheaderNameasvarName

Details

Executing an HTTP request

The http statement performs an HTTP request against the server and resource specified in the url paramater. Any http-related settings previously configured using set will be applied to the request.

The method argument determines the HTTP method to use for the request and must be one of GET, PUT, POST or DELETE.

The url argument must start with either http: or https:. If https: is used then SSL will be used for the request.

The url argument must also contain a valid IP address or hostname. Optionally, it may also contain a port number (preceded by a colon and appended to the IP address or hostname) and a resource.

The following defaults apply if no port or resource is specified:

The format of the http statement is identical when used in conjunction with the buffer statement.

Querying response headers

To dump a list of all the response headers returned by the server in the most recent session use the statement:

http dump_headers

This will render a list of the headers to standard output, and is useful when implementing and debugging USE scripts. The intention of this statement is to provide a tool to assist in script development, and as such it would normally be removed or suppressed with a debug mode switch in production environments.

To retrieve the value of a specific header, use the statement:

http get_headerheaderNameasvarName

This will set the variable varName to be the value of the header headerName.

If headerName was not found in the response, then a warning will be written to the log-file. In this case varName will not be created but if it already exists then its original value will be unmodified.

Examples

Example 1

# A simple request using the default port and no SSL
set http_savefile "/extracted/http/customers.json"
http GET "http://localhost/v1/customers"

# A more complex request requiring setup and a custom port
clear http_headers
set http_header "Accept: application/json"
set http_header "Authorization: FFDC-4567-AE53-1234"    
set http_savefile "extracted/http/customers.json"
buffer customers = http GET "https://demo.server.com:4444/v1/customers"

Example 2

The following shows the process of retrieving a header. The output of:

buffer temp = http GET https://www.google.com
http dump_headers
http get_header Date as responseDate
print The Date header from google.com was: ${responseDate}

Takes the following form:

Last response headers:
HTTP/1.1 200 OK
Cache-Control: private, max-age=0
Date: Mon, 26 Mar 2018 13:50:39 GMT
Transfer-Encoding: chunked
Content-Type: text/html; charset=ISO-8859-1
Expires: -1
Accept-Ranges: none
P3P: CP="This is not a P3P policy! See g.co/p3phelp for more info."
Server: gws
Set-Cookie: 1P_JAR=2018-03-26-13; expires=Wed, 25-Apr-2018 13:50:39 GMT; path=/; domain=.google.co.uk
Set-Cookie: [redacted]; expires=Tue, 25-Sep-2018 13:50:39 GMT; path=/; domain=.google.co.uk; HttpOnly
Vary: Accept-Encoding
X-XSS-Protection: 1; mode=block
X-Frame-Options: SAMEORIGIN
Alt-Svc: hq=":443"; ma=2592000; quic=51303432; quic=51303431; quic=51303339; quic=51303335,quic=":443"; ma=2592000; v="42,41,39,35"

The Date header from google.com was: Mon, 26 Mar 2018 13:50:39 GMT

if

The if statement is used to conditionally execute one or more statements. In conjunction with an optional else statement it can cause one or other of two blocks of statements to be executed depending on whether an expression is true or false.

Syntax

if(expression){

`# Statements`

} [else {

`# Statements`

}]

Details

If the condition evaluates to true, then the first block of statements is executed, and the second block (if present) is skipped over. If the condition evaluates to false then the first block of statements is skipped and the second block (if present) is executed.

The opening { character at the start of each block may be placed on a line of its own if preferred but the closing } must be on a line of its own.

Multiple conditions can be used in a single expression and combined with the boolean operators && or || (for AND and OR respectively) so long as each condition is enclosed in braces. For example:

if (($JSON{example}.[status] == "OK") || (${override} == "enabled")) { 
    # Execute if the status is "OK" or if we have set ${override} to "enabled"
}

Example

Given the source JSON in a file called example.json, the following USE script:

var JSON_dir = "examples\json"
buffer example = FILE "${JSON_dir}\doc.json"

var title = 

# For every element in the 'items' array ...
foreach $JSON{example}.[items] as this_item
{
    # Extract the item name and id
    var item_name = $JSON(this_item).[name]
    var sub_id = $JSON(this_item).[id]

    if (${sub_id} == 02) {
        # For every child of the 'subvalues' object ...
        foreach $JSON(this_item).[subvalues] as this_subvalue
        {
            # Get the subvalue name and value
            var sub_name = ${this_subvalue.NAME}
            var sub_value = ${this_subvalue.VALUE}

            # Render an output line
            print ${title} (id:${sub_id} -> Item: ${item_name} -> Subvalue:${sub_name} = ${sub_value} 
        }
    } else {
            print Skipping unwanted id: ${sub_id}
        }

}
discard {example}
terminate

will produce the following output:

    Skipping unwanted id: 01
    Example JSON data (id: 02) -> Item: Item number two -> Subvalue:0 = 10
    Example JSON data (id: 02) -> Item: Item number two -> Subvalue:10 = 442
    Example JSON data (id: 02) -> Item: Item number two -> Subvalue:100 = 783
    Example JSON data (id: 02) -> Item: Item number two -> Subvalue:1000 = 1009

json

The json statement is used to format JSON in a named buffer.

Syntax

json format{buffername}

Details

In many cases an API or other external source will return JSON in a densely packed format which is not easy for the human eye to read. The json statement is used to re-format JSON data that has been previously loaded into a named buffer (via the buffer statement) into a form that is friendlier to human eyes.

After the JSON has been formatted, the buffer can be saved or printed for subsequent inspection

Example

Given the following single packed line of JSON in a named buffer called myJSON:

{"title":"Example JSON data","heading":{"category":"Documentation","finalised":true},"items":[{"id":"01","name": "Item number one","subvalues":{"0":1,"10":42,"100":73,"1000":100},"category":"Example data","subcategory":"First array"},{"id":"02","name":"Item number two","subvalues":{"0":10,"10":442,"100":783,"1000":1009},"category":"Example data","subcategory":"First array"}]}

The following USE script fragment:

json format {myJSON}
print {myJSON}

will result in the following output:

{
  "title": "Example JSON data",
  "heading": {
    "category": "Documentation",
    "finalised": true
  },
  "items": [
    {
      "id": "01",
      "name": "Item number one",
      "subvalues": {
        "0": 1,
        "10": 42,
        "100": 73,
        "1000": 100
      },
      "category": "Example data",
      "subcategory": "First array"
    },
    {
      "id": "02",
      "name": "Item number two",
      "subvalues": {
        "0": 10,
        "10": 442,
        "100": 783,
        "1000": 1009
      },
      "category": "Example data",
      "subcategory": "First array"
    }
  ]
}

loglevel

While executing a USE script, various messages are written to a logfile. The loglevel option determines the amount of detail recorded in that logfile.

Syntax

loglevelloglevel

Details

The table below shows the valid values for the loglevel argument. Either the numeric level or the label can be specified. If the label is used then it must be specified in CAPITAL LETTERS.

The log levels are cumulative, in that higher log-level values include lower level messages. For example a level of INFO will cause FATAL, ERROR, WARN and INFO level messages to be written to the log.

The loglevel statement takes immediate effect and may be used multiple times within a USE script in order to increase or decrease the logging level at any time.

loop

The loop statement executes one or more statements multiple times.

Syntax

looplabel [count][timeouttimelimit]{

 `# Statements`

}

The opening { may be placed on a line of its own if preferred but the closing } must be on a line of its own

Details

The loop statement will loop indefinitely unless one of three exit conditions cause it to stop. These are as follows:

The number of loops specified by the count parameter are completed
At least as many milliseconds as are specified by the timelimit parameter elapse
An exit_loop statement explicitly exits the loop

In all three cases when the loop exits, execution of the script will continue from the first statement after the closing } marking the end of the loop.

In the event that both count and timelimit parameters are specified, the loop will exit as soon as one or other of the limits have been reached, whichever comes first.

Both the count and timeout parameters are optional. If omitted then the default for both of them will be infinite.

The loop statement will automatically create and update a variable called loop_label.COUNT which can be referenced to determine how many times the loop has executed (as shown in the example below). This variable is not deleted when the loop exits which means that it is possible to know how many times any given loop executed, even after the loop has exited.

Any specified timeout value is evaluated at the end of each execution of the loop and as such the actual time before the loop exits is likely to be a short time (typically a few milliseconds) greater than the specified value. In practice this should be of no consequence.

Example

loop example 10 {
    This is loop number ${example.COUNT}
}

The loop shown above will result in the following output:

This is loop number 1
This is loop number 2
This is loop number 3
This is loop number 4
This is loop number 5
This is loop number 6
This is loop number 7
This is loop number 8
This is loop number 9
This is loop number 10

match

The match statement is to used search either a specified string or the contents of a named buffer using a regular expression.

Syntax

matchlabel expression target

Details

The three parameters serve the following purposes:

Label

The label associates a meaningful name to the search. Once the match has been attempted, two variables will be created or updated as follows:

These variables can be checked after the match in order to determine the result status and access the results.

Expression

The regular expression must contain one or more characters enclosed in brackets - ( ... ) - the contents of which are termed a subgroup. If a successful match is made then the portion of the target text that was matched by the subgroup will be returned in the _label.RESULT_variable.

Target

The target determines whether a supplied string or the contents of a named buffer are searched. By default the parameter will be treated as a string.

If the string contains white-space then it must be enclosed in double quotes

If the target argument is surrounded with curly braces - { ... } - then it is taken to be the name of a buffer and the expression will be applied to the contents of that buffer.

Regular expressions are generally used for searching ASCII data. Searching binary data is possible but may be of limited usefulness.

Examples

Search the contents of a variable for the text following the word 'connection:' with or without a capital 'C':

match varsearch "[Cc]onnection: (.*)" ${variable}
if (${varsearch.STATUS} = MATCH) {
    print Connection string is: ${varsearch.RESULT}
} else {
    print No match found
}

Search a text file previously retrieved from a HTTP request to locate the word 'Error' or 'error'

match error_check "([Ee]rror)" {text_data}
if (${error_check.STATUS} == MATCH) {
    print Found: ${error_check.RESULT}
} else {
    print No error was found
}

pause

The pause statement is used to suspend execution of a USE script for a specified time.

Syntax

pausedelaytime

Details

The delaytime parameter is the number of milliseconds to wait before continuing. A value of 0 is allowed, in which case no delay will occur.

The pause statement may be useful in cases where an external data source imposes some form of rate limiting on the number of queries that can be serviced in a given time-frame, or to slow down execution at critical points when debugging a long or complex script.

Example

This example makes use of script parameters which are provided when USE is executed. For more information on script parameters please refer to the Extract introduction.

var first = ${ARG_1}
var last = ${ARG_2}
var last += 1
var x = ${first}

# Retrieve a number of files from http://server.local/?.dat where ? is a number
# Wait for 1 second between each file
loop slurp {
    var url = http://server.local/datafiles/${x}.dat
    set http_savefile data/${x}.png
    print Getting datafile ${x}
    http GET ${url}
    if (${HTTP_STATUS_CODE} == 200) {
        print 200 OK
    }
    if (${HTTP_STATUS_CODE} == 404) {
        print Data file ${x} missing on server
    }
    var x += 1
    if (${x} == ${last}) {
        exit_loop
    }
        pause 1000   # Wait for 1 second
}
print ${x} files were downloaded
terminate

print

The print statement is used to display text to standard output while a USE script is executing.

Syntax

print [-n]word|{buffer_name} [... word|{buffer_name]

Details

The print statement enables user-defined output to be generated during the execution of a USE script. When retrieving data from external sources it may take some time for a lengthy series of operations to complete, so one use of the print statement is to provide periodic status updates during this time.

The print statement will process as many arguments as it is given, but at least one argument is required. If the first argument is -n then no newline will be output after the last argument has been echoed to standard output, else a newline is output after the last argument.

Arguments that are normal words will be sent to standard output followed by a space. Arguments referencing a named buffer will result in the contents of the buffer being displayed.

Note that print will stop output of data from a named buffer as soon as a NUL (ASCII value 0) character is encountered

Binary data

It is not recommended that print is given a buffer containing binary data to display, as when echoed to a console on screen this is likely to result in various control codes and other sequences to be sent to the console which may have undesired side effects.

Example

var server = "https://my_json_server.com"
print Obtaining token from server
buffer response = http GET ${server}/generatetoken        
print Token received:
print {response}

# Create a variable called ${secret_token} from the
# 'access_token' string in the JSON in the {response} buffer
var secret_token = $JSON{response}.[access_token]

# We no longer need the {response} buffer as the value
# extracted from it is stored in a variable
discard {response}
print Original server response now discarded

return

The return statement is used to exit a subroutine at an arbitrary point and return to the calling location

Syntax

return

Details

A subroutine will automatically return to the location it was called from when the end of its body is reached. However it may be desirable to explicitly exit the subroutine at some other point in which case the return statement is used.

The return statement cannot be used to return a value to the calling code (this should be done via the use of variables as described in the subroutine statement documentation)

Example

#
# Download two files into named buffers
# using a subroutine to do so
#
gosub getfile(data1, "http://intranet/datadump1.json")
gosub getfile(data2, "http://someotherserver/anotherfile.xml")

# (Script to do something with the data goes here)

#
# Argument 1: the name of the buffer to store the data
# Argument 2: the URL of the file to download
#
subroutine getfile {
    if (${SUBARG.COUNT} != 2) {
        print "Error: This subroutine requires two arguments
        return
    } 

    buffer ${SUBARG_1} = http GET "${SUBARG_2}"
    # There is an implicit 'return' here
}

save

The save statement is used to write the contents of a named buffer to disk.

Syntax

save{buffer_name}asfilename

Details

The save statement will write the contents of a named buffer to filename. As well as providing a means of direct-to-disk downloading this can be useful for retrieving server responses and capturing them for later examination, whether it be for analysis, debugging or audit purposes.

If the destination file already exists then it will be overwritten.

If the filename argument contains a path component, then any directories not present in the path will be created. If creation of the path destination file is not successful then an error will be logged and the USE script will fail.

The save statement is similar in effect to the http_savefile option supported by set, in that data from a server is written to disk. There is one important distinction however:

When set http_savefile has been used to specify a file to save, the next HTTP request will stream data to the file as it is received from the server
When a buffer statement is used to capture the server response, and a subsequent save statement is used to write it to disk, all the buffered data will be written to the file immediately

Example

var server = "https://my_json_server.com"
buffer response = http GET ${server}/generatetoken        

# Save a copy of the original server response for diagnostic purposes
save {response} as "${baseDir}\diagnostics\token.json"

# Create a variable called ${secret_token} from the 'access_token'
# string in the JSON in the {response} buffer
var secret_token = $JSON{response}.[access_token]

# We no longer need the {response} buffer as the value extracted
# from it is stored in a variable
discard {response}

set

The set statement is used to configure a setting for use by a subsequent http or buffer statements.

Syntax

setsetting value

Details

A protocol such as http offers a number of configuration options. Any given option is either persistent or transient:

The following settings can be configured using set:

http_progress

set http_progress yes|no

Persistent. If set to yes then dots will be sent to standard output to indicate that data is downloading when an HTTP session is in progress. When downloading large files if a lengthy delay with no output is undesirable then the dots indicate that the session is still active.

http_username

set http_usernameusername

Persistent. Specifies the username to be used to authenticate the session if the http_authtype setting is set to anything other than none. If the username contains any spaces then it should be enclosed in double quotes.

http_password

set http_passwordpassword

Persistent. Specifies the password to be used to authenticate the session if the http_authtype setting is set to anything other than none. If the password contains any spaces then it should be enclosed in double quotes.

http_authtype

set http_authtypetype

Persistent. Specifies the type of authentication required when initiating a new connection. The type parameter can be any of the following:

http_authtarget

set http_authtargettarget

Persistent. Specifies whether any authentication configured using the http_authtype setting should be performed against a proxy or the hostname specified in the http URL.

Valid values for target are:

server (default) - authenticate against a hostname directly
proxy - authenticate against the proxy configured at the Operating System level

http_header

set http_header"name: value"

Persistent. Used to specify a single HTTP header to be included in subsequent HTTP requests. If multiple headers are required, then multiple set http_header statements should be used.

An HTTP header is a string of the form name: value.

There must be a space between the colon at the end of the name and the value following it, so the header should be enclosed in quotes

Example: set http_header "Accept: application/json"

Headers configured using set http_header will be used for all subsequent HTTP connections. If a different set of headers is required during the course of a USE script then the clear statement can be used to remove all the configured headers, after which set http_header can be used to set up the new values.

By default, no headers at all will be included with requests made by the http statement. For some cases this is acceptable, but often one or more headers need to be set in order for a request to be successful.

Typically these will be an Accept: header for GET requests and an Accept: and a Content-Type: header for POST requests. However there is no hard and fast standard so the documentation for any API or other external endpoint that is being queried should be consulted in order to determine the correct headers to use in any specific scenario.

Headers are not verified as sane until the next HTTP connection is made

http_body

set http_body datastring - use the specified string as the body of the request

set http_body filefilename - send the specified file as the body of the request

set http_body{named_buffer} - send the contents of the named buffer as the body of the request

Transient. By default no data other than the headers (if defined) is sent to the server when an HTTP request is made. The http_body setting is used to specify data that should be sent to the server in the body of the request.

When using http_body a Content-Length: header will automatically be generated for the request. After the request this Content-Length: header is discarded (also automatically). This process does not affect any other defined HTTP headers.

After the request has been made the http_body setting is re-initialised such that the next request will contain no body unless another set http_body statement is used.

http_savefile

set http_savefilefilename

Transient. If set, any response returned by the server after the next HTTP request will be saved to the specified filename. This can be used in conjunction with the buffer statement, in which case the response will both be cached in the named buffer and saved to disk.

If no response is received from the next request after using set http_savefile then the setting will be ignored and no file will be created.

Regardless of whether the server sent a response or not after the HTTP request has completed, the http_savefile setting is re-initialised such that the next request will not cause the response to be saved unless another set http_savefile statement is used.

No directories will be created automatically when saving a file, so if there is a pathname component in the specified filename, that path must exist.

http_savemode

set http_savemodemode

Persistent.

If mode is overwrite (the default) then if the filename specified by the set http_savefile statement already exists it will be overwritten if the server returns any response data. If no response data is sent by the server, then the file will remain untouched.
If mode is append then if the filename specified by the set http_savefile statement already exists any data returned by the server will be appended to the end of the file.

http_timeout

set http_timeoutseconds

Persistent. After a connection has been made to a server it may take a while for a response to be received, especially on some older or slower APIs. By default, a timeout of 5 minutes (300 seconds) is endured before an error is generated.

This timeout may be increased (or decreased) by specifying a new timeout limit in seconds, for example:

set http_timeout 60    # Set timeout to 1 minute

The minimum allowable timeout is 1 second.

http_retry_count

set http_retry_countcount

Persistent. Sets the number of retries that will be made in case of transport-level failures, such as an inaccessible server or a name resolution issue. Server responses with non-200 HTTP code are not considered transport-level failures.

By default this option has a value of 1, which means one initial request and one retry. To disable retrying set the value to 0.

http_retry_delay

set http_retry_delaymilliseconds

Persistent. Set the pause between retries in milliseconds. Default value is 5000 milliseconds. Used only if http_retry_count is non-zero.

http_redirect_count

set http_redirect_countcount

Persistent. Set the maximum number to follow HTTP redirects. Valid values are in the range 0-32, where 0 disable redirects completely. By default redirects are disabled.

http_secure

set http_secure yes|no

Persistent. Switches on or off several server HTTPS certificate validation check, such as:

certificate is issued by trusted CA (Certificate Authority) or certificate chain of trust can be traversed to trusted CA (list of trusted CAs is located in common/certificates/cacert.pem file within Exivity home directory)
server name matches the name in the certificate

Other certificate checks, such as certificate expiration date, cannot be disabled.

Starting from Exivity version 3 this option is switched on by default.

odbc_connect

set odbc_connectconnection_string

Persistent. Sets the ODBC connection string for use by the buffer statement's odbc_direct protocol. The connection string may reference an ODBC DSN or contain full connection details, in which case a DSN doesn't need to be created.

A DSN connection string must contain a DSN attribute and optional UID and PWD attributes. A non-DSN connection string must contain a DRIVER attribute, followed by driver-specific attributes.

Please refer to the documentation for the database to which you wish to connect to ensure that the connection string is well formed.

An example connection string for Microsoft SQL Server is:

set odbc_connect "DRIVER=SQL Server;SERVER=Hostname;Database=DatabaseName;TrustServerCertificate=No;Trusted_Connection=No;UID=username;PWD=password"

subroutine

The subroutine keyword is used to define a named subroutine

Syntax

subroutine subroutine_name {
   # Statements
}

Details

Overview

A subroutine is a named section of code that can be executed multiple times on demand from anywhere in the script. When called (via the gosub statement), execution of the script jumps to the start of the specified subroutine. When the end of the code in the subroutine body is reached or a return statement is encountered (whichever comes first), execution resumes at the statement following the most recent gosub statement that was executed.

The code in the body of a subroutine statement is never executed unless the subroutine is explicitly called using gosub. If a subroutine is encountered during normal linear execution of the script then the code in it will be ignored.

Subroutines in USE do not return any values, but any variables that are set within the subroutine can be accessed from anywhere in the script and as such they should be used for returning values as needed.

Subroutine Arguments

When invoked via the gosub statement, arguments can be passed to the subroutine. These arguments are read-only but may be copied to normal variables if required.

Arguments are accessed using the same syntax as is used for variables as follows:

${SUBARG.COUNT} contains the number of arguments that were passed to the subroutine

${SUBARG_N} is the value of any given argument, where N is the number of the argument starting at 1

Every time a subroutine is called, any number of arguments may be passed to it. These arguments are local to the subroutine and will be destroyed when the subroutine returns. However, copying an argument to a standard variable will preserve the original value as follows:

subroutine example {
    if (${SUBARG.COUNT} == 0) {
        var return_value = "NULL"
    } else {
        var return_value = ${SUBARG_1}
    }
}

After the subroutine above has been executed the return_value variable will retain the value it was set to.

It is not permitted to nest subroutine statements. If used within the body of a subroutine statement, a subroutine statement will cause the script to terminate with an error.

Example

The following demonstrates using a subroutine to detect when another subroutine has been provided with an incorrect number of arguments:

if (${ARGC} == 0) {
    print This script requires a yyyyMMdd parameter
    terminate with error
} 

# Ensure the parameter is an 8 digit number
gosub check_date(${ARG_1})
#
# (script to make use of the argument goes here)
#
terminate

# ----
#     This subroutine checks that its argument
#     is an 8 digit decimal number
# ----
subroutine check_date {
    # Ensure this subroutine was called with one argument
    gosub check_subargs("check_date", ${SUBARG.COUNT}, 1)

    # Validate the format
    match date "^([0-9]{8})$" ${SUBARG_1}
    if (${date.STATUS} != MATCH) {
        print Error: the provided argument is not in yyyyMMdd format
        terminate with error
    }
}

# ----
#     This subroutine generates an error message for
#     other subroutines if they do not have the correct
#     number of arguments
#
#     It is provided as a useful method for detecting internal
#     script errors whereby a subroutine is called with the
#     wrong number of arguments
#
#     Parameters:
#        1: The name of the calling subroutine
#        2: The number of arguments provided
#        3: The minimum number of arguments permitted
#        4: OPTIONAL: The maximum number of arguments permitted
# ----
subroutine check_subargs {
    # A check specific to this subroutine as it can't sanely call itself
    if ( (${SUBARG.COUNT} < 3) || (${SUBARG.COUNT} > 4) ) {
        print Error: check_subargs() requires 3 or 4 arguments but got ${SUBARG.COUNT}
        terminate with error
    }

    # A generic check
    var SCS_arg_count = ${SUBARG_2}
    var SCS_min_args = ${SUBARG_3}
    if (${SUBARG.COUNT} == 3) {
       var SCS_max_args = ${SUBARG_3}
    } else {
       var SCS_max_args = ${SUBARG_4}
    }

    if ( (${SCS_arg_count} < ${SCS_min_args}) || (${SCS_arg_count} > ${SCS_max_args}) ) {
        if (${SCS_min_args} == ${SCS_max_args}) {
            print Error in script: the ${SUBARG_1}() subroutine requires ${SCS_min_args} arguments but was given ${SCS_arg_count}
        } else {
            print Error in script: the ${SUBARG_1}() subroutine requires from ${SCS_min_args} to ${SCS_max_args} arguments but was given ${SCS_arg_count}
        }
        terminate with error
    }
}

terminate

The terminate statement will exit the USE script immediately.

Syntax

terminate [with error]

Details

Normally a USE script will finish execution when an error is encountered or when the end of the script file is reached, whichever comes first.

When the terminate statement is encountered, the script will finish at that point. No statements after the terminate statement will be executed.

By default, the script will exit with a success status, however it may be useful to exit deliberately when an error such as an invalid or unexpected response from an HTTP session is detected. Adding the keywords with error to the statement will cause it to exit with an error status.

Example

set http_savefile = extracted/serverdata.txt
buffer serverdata = http GET "https://server.com/uri"
if (${HTTP_STATUS_CODE} != 200) {
    print Got HTTP status ${HTTP_STATUS_CODE}, expected a status of 200
    print The server response was:
    print {serverdata} 
    terminate with error
} else {
    print Received data from server successfully
}

unzip

The unzip statement is used to unzip the data in a named buffer.

Syntax

unzip{buffer_name}

Details

The unzip statement will extract a single file from a zip archive stored in a named buffer. In order for this to succeed, the buffer must have been previously populated using the buffer statement, and the data within the buffer must be a valid ZIP file.

Only ZIP files are supported. To extract GZIP files, use gunzip

A warning will be logged, the buffer left intact and the script will continue to execute if any of the following conditions arise:

The buffer is empty or does not contain a valid ZIP archive
The ZIP archive is damaged or otherwise corrupted
More than 1 file is present within the archive

After the unzip statement completes, the buffer will contain the unzipped data (the original ZIP archive is discarded during this process).

The filename of the unpacked file is also discarded, as the resulting data is stored in the buffer and can subsequently be saved using an explicit filename as shown in the example below.

Example

buffer zippedData = FILE system/extracted/my_source/${dataDate}_usage.zip
unzip {zippedData}
save {zippedData} as system/extracted/my_source/${dataDate}_usage.csv
discard {zippedData}

uri

The uri statement is used to encode the contents of a variable such that it does not contain any illegal or ambiguous characters when used in an HTTP request.

Syntax

uri encodevarname

uri component-encodevarname

uri aws-object-encodevarname

As well as uri component-encode you can use uri encode-component (the two are identical in operation). Similarly, uri aws-object-encode and aws-encode-object are aliases for each other.

Details

When sending a request to an HTTP server it is necessary to encode certain characters such that the server can accurately determine their meaning in context. The encoding involves replacing those characters with a percent symbol - % - followed by two hexadecimal digits representing the ASCII value of that character.

Note that the last parameter to the uri statement is a variable name, so to encode the contents of a variable called my_query the correct statement would be uri encode my_query and not uri encode ${my_query} (The latter would only be correct if the value of my_query was the name of the actual variable to encode)

USE script provides the following methods for encoding the contents of a variable:

encode

uri encodevarname

This method will encode all characters except for the following:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
a b c d e f g h i j k l m n o p q r s t u v w x y z
0 1 2 3 4 5 6 7 8 9 - _ . ~
: / ? # [ ] @ ! $ & ' ( ) * + , ; =

This is typically used to encode a URI which contains spaces (spaces encode to %20) but doesn't contain any query parameters.

encode-component

uri encode-componentvarname

This method will encode all characters except for the following:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
a b c d e f g h i j k l m n o p q r s t u v w x y z
0 1 2 3 4 5 6 7 8 9 - _ . ~

This is typically used to encode query components of a URI, such as usernames and other parameters. Note that this method will encode the symbols =, & and ? and as such a URL of the form:

server.com/resource?name=name_value&domain=domain_value

is usually constructed from its various components using the values of the parameters as shown in the example below.

aws-object-encode

uri aws-object-encodevarname

This method is specifically implemented to support the encoding of object names when downloading from Amazon S3 buckets. Amazon S3 buckets appear much like shared directories, but they do not have a heirarchical filesystem.

The 'files' in buckets are termed objects and to assist in organising the contents of a bucket, object prefixes may be used to logically group objects together.

These prefixes may include the forward slash character, making the resulting object name appear identical to a conventional pathname (an example might be billing_data/20180116_usage.csv). When downloading an object from S3 the object name is provided as part of the HTTP query string.

When referencing an S3 object name there is an explicit requirement not to encode any forward slashes in the object name. USE therefore provides the aws-object-encode method to ensure that any S3 object names are correctly encoded. This method will encode all characters except for the following:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
a b c d e f g h i j k l m n o p q r s t u v w x y z
0 1 2 3 4 5 6 7 8 9 - _ . ~ /

More information may be found at https://docs.aws.amazon.com/AmazonS3/latest/API/sig-v4-header-based-auth.html where it states:

URI encode every byte. UriEncode() must enforce the following rules:

URI encode every byte except the unreserved characters: 'A'-'Z', 'a'-'z', '0'-'9', '-', '.', '', and '~'._

The space character is a reserved character and must be encoded as "%20" (and not as "+").

Each URI encoded byte is formed by a '%' and the two-digit hexadecimal value of the byte.

Letters in the hexadecimal value must be uppercase, for example "%1A".

Encode the forward slash character, '/', everywhere except in the object key name. For example, if the object key name is photos/Jan/sample.jpg, the forward slash in the key name is not encoded.

The usr-object-encode method is compliant with the above requirements. For most trivial cases it should not be necessary to encode the AWS object name as it is relatively straightforward to do it by hand. However using uri aws-object-encode to URI-encode the object name may be useful for object names that contain a number of characters not listed above or for cases where the object name is provided as a parameter to the USE script.

Example

var name = "example/name"
var domain = "example@domain.com"

uri encode-component name
uri encode-component domain

var URL = "http://server.com/resource?name=${name}&domain=${domain}
print URL is now: ${URL}

The above script will output:

URL is now: http://server.com/resource?name=example%2Fname&domain=example%40domain.com

var

Overview

The var statement is used to create or update a variable which can subsequently be referenced by name in the USE script.

Syntax

[public] varname [ = value]

[public] varname operator number

[public] encrypt varname = value

For details on encrypted variables please refer to the encrypt article

Details

Variables are created in one of two ways:

Manually via the var command
Automatically, as a consequence of other statements in the script

If the word public precedes a variable declaration then the variable will be shown in, and its value can be updated from, the Exivity GUI. Only variables prefixed with the word public appear in the GUI (all others are only visible in the script itself). To make an automatic variable public, re-declare it with a value of itself as shown below:

# Convert the automatic NEWLINE variable to be public
public var NEWLINE = ${NEWLINE}

Manually defined variables

A variable is a named value. Once defined, the name can be used in place of the value for the rest of the script. Amongst other things this permits configuration of various parameters at the top of a script, making configuration changes easier.

The = value portion of the statement is optional, but if used there must be white-space on each side of the = character. To use spaces in a variable value it should be quoted with double quotes.

Once a variable has been defined it can be referenced by prefixing its name with ${ and post-fixing it with a }. For example a variable called outputFile can be referenced using ${outputFile}. If no value is specified, then the variable will be empty, eg:

var empty_var
print Variable value: "${empty_var}"

will result in the output:

Variable value:

Variable names are case sensitive, therefore ${variableName} and ${VariableName} are different variables.

If there is already a variable called name then the var statement will update the value.

There is no limit to the number of variables that can be created, but any given variable may not have a value longer than 8095 characters

Arithmetic

Variables that contain a numeric value can have the arithmetic operations performed on them. This is done using the following syntax:

varname operator number

The operator must be surrounded by white-space and following values are supported:

For example the statement var x += 10 will add 10 to the value of x.

When performing arithmetic operations on a variable, any leading zeros in the value of that variable will be respected:

var x = 000
var x += 5    # Result is 005
var x += 10   # Result is 015
var x += 100  # Result is 115
var x += 1000 # Result is 1115

Attempting to perform an arithmetic operation on a variable that does not contain a valid number will result in an error being logged, and the script will terminate.

Currently, only integer arithmetic is supported.

Automatic variables

Automatic variables are referenced in exactly the same way as manually created ones; the only difference is in the manner of creation.

The following variables are automatically created during the execution of a USE script:

To derive the short versions of the day and month names, use a match statement to extract the first 3 characters as follows:

match day "(...)" ${DAY_NAME_UTC}

var short_day = ${day.RESULT}

The .LENGTH suffix

On occasion it may be useful to determine the length (in characters) of the value of a variable. This can be done by appending the suffix .LENGTH to the variable name when referencing it. For example if a variable called result has a value of success then ${result.LENGTH} will be replaced with 7 (this being the number of characters in the word 'success').

A variable with no value will have a length of 0, therefore using the .LENGTH suffix can also be used to check for empty variables as follows:

    var myvar
    if (${myvar.LENGTH} == 0) {
        print The variable 'myvar' is empty
    } else {
        print The variable 'myvar' has a value of ${myvar}
    }

myvar.LENGTH is not a variable in its own right. The .LENGTH suffix merely modifies the manner in which the myvar variable is used.

Examples

Basic variable creation and use

    # Declare a variable
    var name = value

    # If the value contains whitespace then it must be quoted or escaped
    var sentence = "This sentence is contained in a variable"

    # Pathnames should be quoted to avoid any incidences of '\t' being expanded to tabs
    var exportfile = "C:\exivity\collected\Azure\customers.csv"

Creating encrypted variables

    # ---- Start Config ----
    encrypt var username = admin
    encrypt var password = topsecret
    var server = "http://localhost"
    var port = 8080
    var api_method = getdetails
    # ---- End Config ----

    set http_authtype basic
    set http_username ${username}
    set http_password ${password}

    buffer {response} = http GET ${server}:${port}/rest/v2/${api_method}

Transform

Transcript executes user-definable scripts (termed tasks) in order to produce one or more Reporting Database Files (RDFs) from one or more input Dataset files in CSV format. These RDFs are later used by the reporting engine to generate results.

Overview

Transcript tasks are located in system/config/transcript/ and are ASCII files which can be created with any editor. Both UNIX and Windows end-of-line formats are supported.

Statements

Each statement in a Transcript task must be contained on a single line. Statements consist of a keyword indicating the action to perform, followed by zero or more parameters, separated by white-space, required by the statement. Documentation for all the possible statements can be found in the Transcript language reference guide.

Quotes and escapes

By default a space, tab or newline will mark the end of a word in a Transcript task. To include white-space in a parameter (for example to reference a column name with a space in it) then this can be done by enclosing it in double quotes or escaping it by preceding it with \.

Examples: create columns from "Meter Name" using Quantity create columns from Meter\ Name using Quantity

The following table summarises the behaviour of quotes and escapes:

Comments

Comments in a Transcript task start with a # character that is either of:

the first character of a line in the Transcript task
the first character in a word

Comments always end at the end of the line they are started on.

# This is a comment
import usage from Azure # The text from '#' onwards is a comment
import usage#1 from Azure # The '#' in 'usage#1' does not start a comment

Variables

Transcript statements may contain variables. Variables have a name and a value. When a variable name is encountered during execution of the task, the name is replaced with the value of the variable with that name.

To separate them from normal statement words, variable names are always preceded with ${ and followed by }. Therefore the variable with the name dataDate is referenced as ${dataDate} in the transcript task. As well as user defined variables (created using the var statement), the following default variables are supported by Exivity:

Variable names ...

may be used multiple times in a single statement
are case sensitive - ${dataDate} is different to ${datadate}
may not be nested
may be embedded within surrounding text - xxx${dataDate}yyy
may be used within quotes: import "${baseDir}\to_import\AzureJuly${dataDate}.ccr" source AzureJuly
may appear as words of their own in a transcript statement - create column Date value ${dataDate}

Regular Expression variables

A regular expression variable is a special type of variable used to match the name of a column in a DSET. It is enclosed by ${/ and /} and the text within this enclosure can take either of the following two forms:

${/expression/}
- The regular expression described by expression will be applied to the default DSET
${/dset.id/expression/}
- If the text preceding the central / character is a valid DSET ID then the expression after that / will be applied to the column names in that DSET
- If the text preceding the / character is not a valid DSET ID then the entire text of the variable between the ${/ and /} enclosure is treated as a regular expression and will be applied to the default DSET

Once the DSET ID and the expression have been established by the above, the expression is tested against each column name in the DSET and the first matching column name is returned. If no match is found, then an error is logged and the transcript task will fail.

The regular expression may contain a subgroup, which is enclosed within parentheses - ( and ). If no subgroup is present, and a match is made, then the entire column name will be returned. If a subgroup is present and a match is made, then only the characters matching the portion of the expression within the parentheses are returned. For example:

# Rename a column with 'Operations' in its name such that its
# new name is whatever came before 'Operations' in the original name
var prefix = ${/(.*)Operations/}
rename column ${/.*Operations/} to ${prefix}

The expression does not have to match the entire column name. Assuming no subgroup is specified, as long as a match is made then the variable will be expanded to the whole column name.

Regular expression variables are powerful tools when combined with the rename statement, as they can be used to transform an uncertain column name into a known one.

Examples:

# Rename a column containing 'Transfer' or 'transfer' in
# its name, such that it is called 'Transfer':
rename column ${/.*[Tt]ransfer/} to Transfer

# As above, but specifically for the 'Azure.usage' DSET
rename column ${/Azure.usage/.*[Tt]ransfer/} to Transfer

# Rename a column with 'Operations' in its name such that its
# new name is whatever came before 'Operations' in the original name
var prefix = ${/(.*)Operations/}
rename column ${/.*Operations/} to ${prefix}

Importing Data

A Transcript task cannot manipulate data on disk directly, so it is necessary to import one or more Datasets in CSV format at runtime in order to process the data within them. When a Dataset is imported the following sequence of actions takes place:

The Dataset (in CSV format) is read from disk
A number of checks are done on the data to ensure it meets the requirements to qualify as a Dataset
The data is converted into an internal format called a DSET
The DSET is assigned two tags (source and alias) which when combined together form a unique ID to identify the DSET (see Core concepts for more information)
An index is constructed, which facilitates high speed manipulation of the data in the DSET
The DSET is added to the list of DSETs available for use by subsequent statements in the Transcript task

Once these actions have been completed, a DSET can be identified through the unique combination of source.alias. This permits Transcript statements to specify which DSET to operate on.

In addition, a default DSET can be specified, which will be used if no alternative DSET is specified. Full details of these mechanism are detailed in the reference guide, specifically in the import and default articles.

Exporting Data

Data can be exported in one of two ways during the execution of a Transcript task:

Export on demand

Many Transcript statements change the data in the DSET in some way. Columns may be created, renamed or deleted and rows may be added and removed for example.

At any point in the Transcript process the current state of a DSET can be rendered to disk as an output CSV file. This is accomplished via use of the export statement. This permits snapshots of a DSET to be created for debugging or audit purposes, as well as the creation of partially processed CSV files for import into a later Transcript process.

Finishing

The finish statement creates a Reporting Database File (RDF) containing the data in a DSET. This RDF can then be used by the reporting engine.

Transform Preview

When developing your Transformer, it is possible to view intermediate results by utilizing the preview functionality in the Transformer editor. To use the Transformer Preview, place your cursor at a line number in your script uptill where the Transformer should execute. In the example below, the cursor is placed at line numer 27:

Make sure to select a Preview Date for which data is available in the system. Then execute the preview by clicking the Update and Preview button.

By default, the preview will load the first 1000 records for each DSET being imported. In case you want to increase / decrease this amount, you can do so by adjusting the value in the Output Limit field:

Additionally, the preview will by default return the contents of the Default DSET. In case you prefer to preview a static DSET, you can control this by selecting a custom DSET from the drop down selector:

When executing a Transformer in Preview mode, it will not write any daily RDF files to disk, nor will it populate any services.

Configuration

The 'Data Pipelines' menu allows an admin of the Exivity solution to manage Transcript 'Transformer' scripts. Transcript has its own language reference, which is fully covered in a separate chapter of this documentation.

As described in the Transcript Documentation, you are free to use your editor of choice to create and modify Transformers. However, the GUI also comes with a built-in Transformers-editor.

Creating Transformers

To create a new Transformer for Transcript, follow these steps:

From the menu on the left, select "Data Pipelines" > 'Transformer'
To create a new Transformer to normalise and enrich USE Extractor consumption and lookup data, click the 'Create Transformer' button
When your Exivity instance has access to the Internet, it will pull in the latest set of Transformer Templates from our Github account. These templates are then presented to you, and you can pick one from the list to start Extracting. If you don't have access to the internet, you can download them directly from Github. You are also free to start creating your own Extractor from scratch.
Provide a meaningful name for your Transformer. When we create a Transformer for a consolidated bill of various IT resources we would, for example, name it: 'IT Services Consumption'.
When you're done creating your Transformer, click the 'Insert' at the bottom of the screen.

The Transformer editor has syntax highlighting and auto completion, to simplify the development of your scripts

Edit and Delete Transformers

When you want to change or delete an existing Transformer, first select one from the list of Transformer that you want to change:

When you've selected your Transformer from the "Data Pipelines" > 'Transformers' list, you can change the Transformer script in the editor
In this example, we're adding a 'services' statement using auto completion, to simplify the creation of services
In case you want to save your changes, click the 'Save' button at the bottom of the 'Editor' screen. To delete this Transformer, you can do so by clicking the 'Remove' button, after which you'll receive an confirmation pop-up where you'll have to click 'OK'.

Run and Schedule Transformers

To test your Transformer, you can execute or schedule it directly from the Glass interface:

After you have selected the Transformer that you would like to run, click to the 'Run' tab next to the 'Editor' tab
Manual execution of a Transformer can only be done for a single day. Provide the date you want to run this transformer for in dd-MM-yyyy format. You can also use the date picker, by clicking on the down facing arrow, on the right side of the date field
When you've provided the required date, click 'Run Now' to execute the Transformer. After the Transformer has completed running, you will receive some success or failed message, after which you might need to make additional changes to your Transformer. For further investigations or troubleshooting, consult the "Log Viewer" found under the administration dropdown menu top right of the screen.
Once you're happy with your output, you can schedule the Transformer via the 'Schedule' tab, which is located next to the 'Run' tab at the top of the screen
Transformer can be scheduled to run once a day at a specific time. Also you should provide a date, which is provided by using an offset value. For example, if you want to execute this Transformer against yesterdays date with every schedule run, you should provide a value there of -1
When you're done with the schedule configuration, you may click the 'Schedule' button. In case you want to change or remove this schedule afterwards, click the 'Unschedule' button.

As of version 1.6, it is recommend to use the Workflow function instead of the schedule tab to schedule transformers.

Language

Syntax

Within the individual reference articles for each statement, the syntax is described using the following conventions:

bold for keywords
italics for arguments
Square brackets for optional keywords and arguments [likethis]
Vertical pipe for alternative keyword options just|exactly as shown
Ellipses for a variable length list of arguments: Column1 ... ColumnN

Refer to the core concepts page for more information regarding datasets, fully qualified column names and related information.

Reference

The following statements (in alphabetical order) are supported by Transcript:

aggregate

Overview

The aggregate statement is used to reduce the number of rows in a DSET while preserving required information within them

Syntax

aggregate[dset.id][notime|daily] [offsetoffset][nudge] [default_functionfunction] colname function [... colname function]

Details

The aggregate statement is a powerful tool for reducing the number of rows in a DSET. Aggregation is based on the concept of matching rows. Any two rows that match may be merged into a single row which selectively retains information from both of the original rows. Any further rows that match may also be merged into the same result row.

A quick introduction

A match is determined by comparing all the columns which have a function of match associated with them (further information regarding this can be found below). If all the column values match, then the rows are merged.

Merging involves examining all the columns in the data that were not used in the matching process. For each of those columns, it applies a function to the values in the two rows and updates the result row with the computed result of that function. For a full list of functions, please refer to the table further down in this article.

To illustrate this consider the following two row dataset:

id,colour,location,quantity
1234,blue,europe,4.5
1234,green,europe,5.5

If we don't care about the colour value in the above records, we can combine them together. We do care about the quantity however, so we'll add the two values together to get the final result.

The statement to do this is:

aggregate notime id match location match quantity sum

id match means that the values in the id columns must be the same
location match means that the values in the location columns must be the same
quantity sum means that the resulting value should be the sum of the two existing values
by default, a function of first is applied to the columns, such that the original row retains its value

Applying these rules to the above example we get the following single result record:

id,colour,location,quantity,EXIVITY_AGGR_COUNT
1234,blue,europe,10,2

A column calledEXIVITY_AGGR_COUNT is automatically created by the aggregate statement and for each row in the output it will contain the number of source rows that were merged together to create that result row

Parameters

The aggregate statement accepts a range of parameters as summarised in the table below:

If two records are deemed suitable for merging then the function determines the resulting value in each column. The available functions are as follows:

Non time-sensitive aggregation

When the notime parameter is specified, the aggregation process treats any columns flagged as start and end times in the data as data columns, not timestamp columns.

In this case when comparing two rows to see if they can be merged, the aggregation function simply checks to see if all the columns with a function of match are the same, and if they are the two rows are merged into one by applying the appropriate function to each column in turn.

De-duplication

The following illustrates the aggregate statement being used to remove duplicate rows from a DSET:

# The first column in the DSET being aggregated
# is called subscription_id

aggregate notime default_function match subscription_id match

The analysis of the statement above is as follows:

notime - we are not interested in timestamps
default_function match - by default every column has to match before records can be aggregated
subscription_id match - this is effectively redundant as the default_function is match but needs to be present because at least one pair of colname function parameters is required by the aggregate statement

The resulting DSET will have no duplicate data rows, as each group of rows whose column values were the same were collapsed into a single record.

Row reduction while preserving data

The example shown at the top of this article used the sum function to add up the two quantity values, resulting in the same total at the expense of being able to say which source record contributed which value to that total.

The sum function can therefore accurately reflect the values in a number of source rows, albeit with the above limitation. By using a function of sum, max or min, various columns can be processed by aggregate in a meaningful manner, depending on the specific use case.

Time-sensitive aggregation

When aggregating, columns containing start time and end time values in UNIX epoch format can be specified. Each record in the DSET therefore has start and end time markers defining the period of time that the usage in the record represents. As well as taking the start times and end times into account, time-sensitive aggregation can perform additinal manipulations on these start and end times.

A quick example

Consider the following CSV file called aggregate_test.csv:

startUsageTime,endUsageTime,id,subscription_id,service,quantity
2017-11-03:00.00.00,2017-11-03:02.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:03.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:06.00.00,ID_3456,SUB_efgh,Medium VM,2
2017-11-03:00.00.00,2017-11-03:04.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:05.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:06.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:07.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:02.00.00,ID_3456,SUB_efgh,Large VM,2
2017-11-03:00.00.00,2017-11-03:03.00.00,ID_3456,SUB_efgh,Medium VM,2
2017-11-03:00.00.00,2017-11-03:04.00.00,ID_3456,SUB_efgh,Large VM,2
2017-11-03:00.00.00,2017-11-03:05.00.00,ID_3456,SUB_efgh,Large VM,2
2017-11-03:00.00.00,2017-11-03:07.00.00,ID_3456,SUB_efgh,Large VM,2
2017-11-03:00.00.00,2017-11-03:06.00.00,ID_3456,SUB_efgh,Medium VM,2

It is possible to aggregate these into 3 output records with adjusted timestamps using the following Transcript task:

import system/extracted/aggregate_test.csv source aggr alias test

var template = YYYY.MM.DD.hh.mm.ss
timestamp START_TIME using startUsageTime template ${template}
timestamp END_TIME using endUsageTime template ${template}
timecolumns START_TIME END_TIME
delete columns startUsageTime endUsageTime

aggregate aggr.test daily nudge default_function first id match subscription_id match service match quantity sum

timerender START_TIME as FRIENDLY_START
timerender END_TIME as FRIENDLY_END

Resulting in:

id,subscription_id,service,quantity,START_TIME,END_TIME,EXIVITY_AGGR_COUNT,FRIENDLY_START,FRIENDLY_END
ID_1234,SUB_abcd,Large VM,12,1509667200,1509692399,6,20171103 00:00:00,20171103 06:59:59
ID_3456,SUB_efgh,Medium VM,6,1509667200,1509688799,3,20171103 00:00:00,20171103 05:59:59
ID_3456,SUB_efgh,Large VM,8,1509667200,1509692399,4,20171103 00:00:00,20171103 06:59:59

As can be seen, for each unique combination of the values in the id,subscription-id and service columns, the start and end times have been adjusted as described above and the quantity column contains the sum of all the values in the original rows.

When performing time-sensitive aggregation, any records with a start or end time falling outside the current data date will be discarded.

Further notes

The daily parameter to aggregate means that the START_TIME and END_TIME columns are now recognised as containing timestamps. When aggregating with the daily option, timestamps within the current dataDate are combined to result in an output record which has the earliest start time and the latest end time seen within the day.

Optionally, following daily an offset may be specified as follows:

aggregate aggr.test daily offset 2 id match subscription_id match quantity sum

In this case the start and end timestamps are adjusted by the number of hours specified after the word offset before aggregation is performed. This permits processing of data which has timestamps with timezone information in them, and which may start at 22:00:00 of the first day and end at 21:59:59 of the second day, as an offset can be applied to realign the records with the appropriate number of hours to compensate.

The nudge parameter shaves 1 second off end times before aggregating in order to avoid conflicts where hourly records start and end on 00:00:00 9the last second of the current hour is the same as the first second of the next hour)

append

Overview

The append statement is used to append one DSET to the end of another.

Syntax

appendsource_dset.idtodestination_dset.id

Details

If the source DSET has any column names not present in the destination DSET then additional columns are automatically created in the destination DSET. These additional columns will contain blank values by default.

If one or more column names are present in both DSETs then the columns copied from the source DSET may be re-ordered into the same order as that used by the destination DSET.

At the end of the operation, the destination DSET will contain all the data from both DSETs, and the source DSET is unchanged.

Both DSETs must exist and both should have data. To verify a DSET existents or to check whether a DSET is empty, use one of the following functions:

Additionally, it is not possible to append a DSET to itself.

Example

Given the following DSETs:

DSET ID: example.data

VMSize,RAM,CPU
small,2,2
medium,4,2
large,8,4
huge,16,8

DSET ID: example2.data

VMSize,storage
small,50
medium,100
large,500
huge,1000

The statement append example2.data to example.data will result in the following destination DSET (example.data):

VMSize,RAM,CPU,storage
small,2,2,
medium,4,2,
large,8,4,
huge,16,8,
small,,,50
medium,,,100
large,,,500
huge,,,1000

calculate

Overview

The calculate statement is used to perform arithmetic operations using literal and column values.

Syntax

calculate columnResultColas source operation source

where source is either of columncolName or valueliteral_value

and operation is one of the characters + - * / % for addition, subtraction, multiplication, division and modulo respectively.

There must be whitespace on each side of the _operation`_character

Examples: calculate column ResultCol as column Amount * value 1.2 calculate column Net as column total - column cogs calculate column constant_7 as value 3.5 + value 3.5

Details

The ResultCol parameter is the name of the column that will hold the results. This column may or may not exist (if necessary it will be created automatically).

Both of the two source parameters can specify a literal value, or the name of a column containing the value to use when performing the calculation.

A literal value is specified using valueN where N is the literal number required
A column name is specified using columncolName where ColName is the name of the column containing the values required

The ResultCol may be the same as a column specified by one of the source parameters in which case any existing values in it will be updated with the result of the calculation.

Additional notes:

Any blank or non-numeric values in a source column will be treated as 0
An attempt to divide by zero will result in 0
When performing a modulo operation, the two source values are rounded to the nearest integer first
If the result column already exists then if option overwrite is set to no, only blank cells in the result column will be updated.

Examples

Add 1.5 to the values in the Rate column:

calculate column Rate as column Rate + value 1.5

Multiply the values in the Rate column by those in the Quantity column
Store the result in a new column called Charge

calculate column Charge as column Rate * column Quantity

capitalise

Overview

The capitalise (the spelling capitalize is also supported) statement is used to modify the name of a column and/or the values in a column such that the first character is a capital letter and the remaining characters are lower case.

Syntax

capitalise values|heading [and values|heading] in column|columnsColName1 [... ColNameN]

After the keyword in, either of the keywords column or columns may be used

Details

The heading and values keywords refer to the name of an existing column and the values in each row for that column respectively.

Only the first character in the column name or value is converted to upper case. If this character is not a letter, then the statement will have no effect on it. For example applying the statement:

capitalise heading in column _vmname

will have no effect on the column name as the first character is an underscore. However, applying the same statement to a column called _VMName would result in a new name of _vmname as after attempting to make the first character a capital (which in the case of the underscore has no effect), the remaining characters are converted to lower case.

Any number of column names may be specified, and any of these may or may not be fully qualified. When applied to values in a column, blank values are ignored.

Examples

capitalise heading in column servicegroup

capitalize heading and values in columns servicegroup Azure.usage.resourcepool chargeinterval

capitalise values in column Region

convert

Overview

The convert statement is used to convert values in a column from base-10 to base-16 or vice-versa.

Syntax

convertcolNameto decimal|hex from decimal|hex

The keywords decanddecimaland the keywordshexandhexadecimalare equivalent.

Details

When converting values in a column, the following considerations apply:

Values in the column are replaced with the converted values
The colName argument must reference an existing column, and may optionally be fully qualified (else the column is assumed to be in the default DSET)
If any values in the column are not valid numbers, they will be treated as 0
Blank values are ignored
The convert statement may be used in the body of a where statement
If a value in colName contains a partially correct value such as 123xyz then it will be treated as a number up to the first invalid character, in this case resulting in a value of 123.
The hex digits in the original value can be either upper or lower case
The hex digits from A-F will be rendered in upper case in the converted output
The convert statement only supports integer values (floating points will be treated as floored to the nearest integer)

Example

convert decimal_count from decimal to hex
convert unique_id from hexadecimal to dec

copy

This article covers both the copy and move statements. They both work in the same way apart from the fact that move deletes the source row after copying it.

Overview

The copy statement is used to copy rows from one DSET to another

Syntax

copy rows todset.id

move rows todset.id

Details

Both copy and move must be used within the body of a where statement. Only rows that match the expression will be copied (or moved).

The DSET from which rows will be copied or moved is automatically determined from the expression used by the where statement.
The DSET to which rows will be copied or moved is determined by the dset.id parameter

The source and destination DSETs must be different (it is not possible to copy or move a row within the same DSET).

The destination DSET may or may not exist. If it does not exist then it will be created. If it does exist then the following logic is applied:

If the destination DSET has more columns than the source DSET then the new rows in the destination DSET will have blank values in the rightmost columns
If the destination DSET has fewer columns than the source DSET then the destination DSET will be extended with enough new columns to accomodate the new rows. In this case, existing rows in the destination DSET will have blank values in the rightmost columns

If the destination DSET is extended to accomodate the source rows then the new (rightmost) columns will have the same names as the equivalent columns in the source DSET. In the event that this would cause a naming conflict with existing columns in the destination DSET, one or more new columns in the destination DSET will heve a suffix added to their name to ensure uniqueness. This suffix takes the form _N where N is a number starting at 2.

To illustrate this, if the source DSET has columns called subscription,name,address,hostname and the destination DSET has a single column called name then the resulting extended destination DSET would have columns called name,subscription,name_2,address,hostname.

Example

# Move all rows in usage.data where hostname is "test server"
# to a new DSET called test.servers

where ([usage.data.hostname] == "test server") {
    move rows to test.servers
}

correlate

Overview

The correlate statement is used to enrich the default DSET by adding new columns to it, and/or updating existing columns with useful values. The new column names are derived from other DSETs and the values in those columns are set using a lookup function based on the value in a key column shared between the DSETs.

Syntax

correlateColName1 [ ... ColNameN]usingKeyColumn[assumingassumeDSET][defaultDefaultValue]

Details

The ColName1 ... ColNameN arguments are column names that will be copied from their original DSETs and merged into the default DSET.

Column names must be fully qualified, unless the assuming parameter is used, in which case any column names that are not fully-qualified will be assumed to belong to the DSET specified by assumeDSET.

Source and Destination columns

Source columns are those from which a cell is to be copied when the KeyColumn matches. Destination columns are columns in the default DSET into which a cell will be copied. Destination column names are derived from the names of the source columns as follows:

The source column is the argument in its original form, for example: Azure.usage.MeterName
The destination column is the same argument, but with the DSET ID replaced with that of the default DSET. For example if the default DSET is Custom.Services then the destination column for the above would be Custom.Services.MeterName.

If a destination column name doesn't exist in the default DSET then a new column with that name will automatically be created.

The Key Column

The KeyColumn argument is a column name which must not be fully qualified and which must exist in the default DSET and all of the DSETs referenced by the ColNameN arguments.

Default values

The DefaultValue argument, if present, specifies the value to write into the destination column if there is no match for the KeyColumn. If the DefaultValue argument is not specified then any rows where there is no match will result in a blank cell in the destination column.

For each row in the default DSET, the source DSET is searched for a matching KeyColumn value, and if a match is found then the value in the source column is used to update the default DSET. The row of the first match found in the source DSET will be used.

Overwriting

When matching the KeyColumn values, the logic in the following table is evaluated against every row in the destination DSET.

✘ means no or disabled, ✔ means yes or enabled

Examples

Given two Datasets as follows, where the default DSET is MyData.Owners:

Dataset 'MyData.Owners'

owner,id
John,100
Tim,110
Fokke,120
Joost,130
Jon,140

Dataset 'Custom.Services'

service,description,id
Small_VM,Webserver,130
Medium_VM,App_Server,100
Large_VM,DB_Server,110
Medium_VM,Test_Server,120

The statement: correlate service description using id assuming Custom.Services

Will enrich the MyData.Owners Dataset such that it contains:

owner,id,service,description
John,100,Medium_VM,App_Server
Tim,110,Large_VM,DB_Server
Fokke,120,Medium_VM,Test_Server
Joost,130,Small_VM,Web_Server
Jon,140,,

The statement: correlate service description using id assuming Custom.Services default unknown

Will produce:

owner,id,service,description
John,100,Medium_VM,App_Server
Tim,110,Large_VM,DB_Server
Fokke,120,Medium_VM,Test_Server
Joost,130,Small_VM,Web_Server
Jon,140,unknown,unknown

create

Overview

The create statement is used to add one more more new columns to an existing DSET.

Syntax

create columnNewColumnName[valueValue]

create columns fromColumnName[usingValueColumnName]

create mergedcolumnNewColumn[separatorsep]from [stringliteral] Column [/regex/] [Column [/regex/]|stringliteral]

Details

Explicit single column creation

Syntax

create columnNewColumnName[valueValue]

Details

This statement is used to create a new column called NewColumnName. The NewColumnName argument may be a fully qualified column name, in which case the new column will be created in the DSET specified as part of that name.

Note: If no default DSET has been explicitly defined using the default dset statement then the DSET created by the first use or import statement in the Transcript task is automatically set as the default DSET.

A column called NewColumnName must not already exist in the DSET. If NewColumnName contains dots then they will be converted into underscores.

The new column will be created with no values in any cells, unless the optional value *Value* portion of the statement is present, in which case all the cells in the new column will be set to Value.

Examples

Create a new empty column called Cost in the default DSET: create column Cost

Create a new column called Cost with a value of 1.0 in every row of the default DSET: create column Cost value 1.0

Create a new column called Cost with a value of 1.0 in every row of the DSET custom.charges: create column custom.charges.Cost value 1.0

Automated single/multiple column creation

Syntax

create columns fromColumnName[usingValueColumnName]

Details

This statement is used to create multiple columns in a single operation. As is the case for create columns above, if the using ValueColumnName portion of the statement is not present, then all newly created columns will have no values in any cells.

Given this example dataset:

The statement create columns from ServiceName using Count will create the result shown below:

The names of the new columns to create are derived from the contents of the cells in the column called ColumnName, and the values (if opted for) are derived from the contents of the cells in the column called ValueColumnName. Duplicates are ignored. If all the cells in ColumnName have the same contents, then only a single new column will be created. To illustrate this, consider the following:

SubscriptionID,ServiceName,Quantity
FE67,StorageGB,30
1377,Small_VM,2
EDED,Medium_VM,8
8E1B,Large_VM,1
99AA,Small_VM,99

When applied to the data above, the statement create columns from ServiceName will produce the following result (note that only a single column called Small_VM is created, and that empty cells are represented with a separator character, which in the case of the below is a comma):

SubscriptionID,ServiceName,Quantity,StorageGB,Small_VM,Medium_VM,Large_VM
FE67,StorageGB,30,,,,
1377,Small_VM,2,,,,
EDED,Medium_VM,8,,,,
8E1B,Large_VM,1,,,,
99AA,Small_VM,99,,,,

If opting to set the values in the new columns, then for each row the value in ValueColumnName will be copied into the column whose name matches ColumnName. When applied to the same original data, the statement create columns from ServiceName using Quantity will produce the following result:

SubscriptionID,ServiceName,Quantity,StorageGB,Small_VM,Medium_VM,Large_VM
FE67,StorageGB,30,30,,,
1377,Small_VM,2,,2,,
EDED,Medium_VM,8,,,8,
8E1B,Large_VM,1,,,,1
99AA,Small_VM,99,,99,,

When using create columns the new columns are always created in the default DSET. This means that when no values are being set, it is possible to specify a different DSET for ColumnName. If the default DSET is Azure.usage, then the statement create columns from custom.data.Services will derive the names of the new columns from the cell contents in the Services column in the custom.data DSET.

This is only possible in the absence of the using ValueColumnName option. When values are to be set, both the ColumnName and ValueColumnName arguments must belong to the default DSET.

Example

The following transcript task will import the datasets Azure.usage and system/extracted/Services.csv, and create new (empty) columns in Azure.usage whose names are taken from the values in the column ServiceDefinitions in Services.csv.

import system/extracted/Services.csv source custom
import usage from Azure
default dset Azure.usage
create columns from custom.Services.ServiceDefinitions

Merging column values to create a new column

Syntax

create mergedcolumnNewColumn[separatorsep]from [stringliteral] Column [/regex/] [ ... Column [/regex/]|stringliteral]

If preferred, the wordusingmay be used instead of the wordfrom(both work in an identical fashion)

Details

This form of the statement is used to generate a new column containing values derived from those in one or more existing columns (termed source columns). The parameters are as follows:

The separator may be more than one character in length (up to 31 characters may be specified)

If a regex is specified then it must contain a subgroup enclosed in parentheses. The portion of the text in the source column matched by this subgroup will be extracted and used in place of the full column value.

The '/' characters surrounding the regular expression in the statement are not considered to be part of the expression itself - they are merely there to differentiate an expression from another column name.

If a regex is not specified, then the entire value in the source column will be used.

Options

By default the value extracted from a source column will be blank in the following two cases:

There is a blank value in a source column
No match for a regular expression is found in the value of a source column

In such cases the merged result will simply omit the contribution from the source column(s) in question. If all the source columns yield a blank result then the final merged result will also be blank.

This behaviour can be overridden through the use of the option statement. The options associated with the create mergedcolumn statement are as follows:

option merge_blank = some_text_here

This option will use the string some_text_here in place of any blank source column value.

option merge_nomatch = some_text_here

This option will use the string some_text_here if the result of applying the regular expression to a column value returns no matches.

Specifying the literal string <blank> as the merge_blank or merge_nomatch value will reset the option such that the default behaviour is re-activated.

Examples

Given the following dataset:

name,user_id,department
Eddy,123-456-123456,Development
Tim,654-321-654321,Project Management
Joram,555-222-999111,Development
Joost,826-513-284928,Sales and Marketing

The following examples illustrate some uses of the create mergedcolumn statement:

Example 1

# Create a new column called 'key' which combines the 'department'
# with the middle three digits of the 'user_id', separated by :

create mergedcolumn key separator : from department user_id /[0-9]{3}-([0-9]{3})/

# Result:
name,user_id,department,key
Eddy,123-456-123456,Development,Development:456
Tim,654-321-654321,Project Management,Project Management:321
Joram,555-222-999111,Development,Development:222
Joost,826-513-284928,Sales and Marketing,Sales and Marketing:513

Example 2

If no regular expression is specified then the values in the source column will be used in their entirety:

# Create a new column called 'key' which combines the 'department'
# and 'user_id' columns separated by ":", with prefix

create mergedcolumn key separator : from string prefix department user_id

# Result:
name,user_id,department,key
Eddy,123-456-123456,Development,prefix:Development:123-456-123456
Tim,654-321-654321,Project Management,prefix:Project Management:654-321-654321
Joram,555-222-999111,Development,prefix:Development:555-222-999111
Joost,826-513-284928,Sales and Marketing,prefix:Sales and Marketing:826-513-284928

Example 3

Let us add a new row to the sample dataset which has a non-compliant value for the user_id:

name,user_id,department
Eddy,123-456-123456,Development
Tim,654-321-654321,Project Management
John,xxx-xxx-xxxxxx,Pending
Joram,555-222-999111,Development
Joost,826-513-284928,Sales and Marketing

By default a non-matching value will result in a blank component of the merged result:

# Create a new column called 'key' which combines the 'department'
# with the middle three digits of the 'user_id', separated by :

create mergedcolumn key separator : from department user_id /[0-9]{3}-([0-9]{3})/

# Result:
name,user_id,department,key
Eddy,123-456-123456,Development,Development:456
Tim,654-321-654321,Project Management,Project Management:321
John,xxx-xxx-xxxxxx,Pending,Pending
Joram,555-222-999111,Development,Development:222
Joost,826-513-284928,Sales and Marketing,Sales and Marketing:513

In this case, the resulting key for John has no separator characters in it. We can force a default value for the missing user_id portion as follows:

option merge_nomatch = [none]
create mergedcolumn key separator : from department user_id /[0-9]{3}-([0-9]{3})/

# Result:
name,user_id,department,key
Eddy,123-456-123456,Development,Development:456
Tim,654-321-654321,Project Management,Project Management:321
John,xxx-xxx-xxxxxx,Pending,Pending:[none]
Joram,555-222-999111,Development,Development:222
Joost,826-513-284928,Sales and Marketing,Sales and Marketing:513

default

Overview

The default statement is used to explicitly define the default DSET to use when specifying a column name as an argument in subsequent statements.

Syntax

default dsetsource.alias

Details

Given that multiple DSETS can be loaded at once, it is necessary to specify which DSET to operate on when performing actions such as creating a new column with create. A column name in a Transcript statement is assumed as belonging to the default DSET unless it is a fully qualified column name.

If there is no default statement in the Transcript, then the first CSV file imported via the import statement will automatically be designated as the default DSET.

The default statement can be used multiple times throughout a Transcript, in which case the default DSET will be whichever was specified by the last default statement executed.

Lastly, when executing a finish statement, unless otherwise specified, the default DSET will be used to populate the reporting database created as a result.

When changing the default DSET, any service definitions that referenced the default DSET at the time they were created will be updated with the new default DSET.

Examples

Set custom.datafile as the default DSET:

default dset custom.datafile

delete

Overview

The delete statement is used to delete one or more columns or rows from one or more DSETs.

Syntax

delete columns [except]ColName1 [... ColNameN]

delete blankcolumns

delete rows

delete dsetdset_id

Details

Deleting columns

The list of column names following the initial delete columns statement may be used in one of two ways:

The columns listed will be deleted
All columns except those listed will be deleted

Which method to use is determined by the presence (or otherwise) of the except keyword:

delete columns One Two Three         # Delete the listed columns
delete columns except One Two Three  # Delete all but the listed columns

When using the except keyword, all column names following it must belong to the same DSET

It is not possible to delete a column from a DSET which only contains a single column. When deleting a column, the memory used to index it, along with the memory used to store the contents of its cells is released. This may be useful in situations where memory is limited.

Deleting unwanted columns early in a Transcript task will also increase the the performance of many subsequent operations performed by the task.

The second keyword may be column instead of columns. Either way, the statement behaves identically.

Deleting blank columns

The delete blankcolumns statement will delete columns that only contain blank values from the default DSET.

It is not possible to completely empty a DSET using delete blankcolumns. In the event that the last remaining column in a DSET is blank then it will not be deleted.

Deleting rows

In order to delete rows from a DSET a local filter must be in effect. A local filter is created using the where statement.

When used within the body of a where statement, delete rows will delete all rows in the DSET associated with the local filter where the condition is true. For more information on conditions, please refer to the where article.

Either delete rows or delete row may be used (both variations work in an identical manner)

Deleting a DSET

The delete dset statement can be used to remove a DSET from memory. This may be useful in cases where a DSET is no longer required, for example after it has been used to enrich another DSET via the correlate statement.

It is not possible to delete the default DSET.

Examples

Delete the column temp from the default DSET and the column Interim from the azure.Usage DSET: delete columns temp azure.Usage.Interim

Delete any columns in the default DSET that have no values in any rows: delete blankcolumns

Delete all rows where the VMID is 1234

where ([VMID] == 1234) {
    delete rows
}

Delete the DSET azure.rates delete dset azure.rates

environment

The environment statement specifies the name of environment to use for resolving global variables.

Syntax

environment name

Details

The environment statement selects the predefined environment to use for global variable lookup. It is and error to specify the environment which is not defined in global database.

If no environment specified, default environment (the one specified as default in global database) is assumed.

event_to_usage

Overview

The event_to_usage statement generates new CSV file with usage records from START/STOP/UPDATE events in source DSET.

Syntax

event_to_usage fromsource.aliastofile{ options }

See Details section for options description

Details

This statement produces usage records from events in source DSET. Three times of events are supported:

START event - marks the start of consumption
STOP event - marks the end of consumption
UPDATE event - marks the change of consumption attributes, such as quantity

There are several situations when the usage record is created:

from START to first matching STOP event
from START to first matching UPDATE event
from UPDATE to first matching STOP event
from UPDATE to first matching UPDATE event
from the beginning of the day to STOP event (if consumption started during previous days)
from the beginning of the day to UPDATE event (if consumption started during previous days)
from START event to the end of the day (if no matching STOP/UPDATE events found)
from UPDATE event to the end of the day (if no matching STOP/UPDATE events found)
for the whole day (if consumption started during previous days, and there was no STOP or UPDATE event during processing day)

Event B is considered matching to event A if happened after event B and has matching key fields.

Options

Several options control the behaviour of this statement:

Conditions for events are valid SQL conditions, which are copied verbatim into query's WHERE, therefore it can be any legal SQL expression, such as "state = 'started' AND (prev_state = 'stopped' OR prev_state IS NULL)". Remember using SQL-standard single quotes for string literals.

Initial data load

If epoch_date option is specified, it is possible to perform initial data load - load running consumptions on specific date. Transcript performs following checks:

there are no events loaded for the specified DSET
processing date matches specified epoch_date

If any of these checks fail, Transcript stops with error.

Data integrity checks

It is very important to load events in correct order, therefore Transcript performs following checks:

the data for the same day for the specified DSET cannot be loaded twice
there cannot be gaps in processed dates (except for Initial data load)
there is a matching START event before STOP/UPDATE event (ignored in permissive mode)

If any of these checks fail, Transcript stops with error.

If there is a need to re-process data for specific day, event-related state in the database must be rolled back to the preceding date, and after data for all following days processed in correct order.

Examples

Only START and STOP events, single-column key:

event_to_usage from usage.events to "exported/usage.csv" {
      start_event = "state = 'STARTED' AND (prev_state = 'STOPPED' OR prev_state IS NULL)"
      stop_event = "state = 'STOPPED' AND prev_state = 'STARTED'"
      key_columns = resource_id
      timestamp_column = time
    epoch_date = 20200109
}

START, STOP and UPDATE events, complex key:

event_to_usage from usage.events to "exported/usage.csv" {
      start_event = "state = 'STARTED' AND (prev_state = 'STOPPED' OR prev_state IS NULL)"
      update_event = "state = 'STARTED' AND prev_state = 'STARTED'"
      stop_event = "state = 'STOPPED' AND prev_state = 'STARTED'"
      key_columns = resource_type,resource_id
      timestamp_column = time
}

export

Overview

The export statement is used to snapshot the data in a DSET and write it to disk as a Dataset.

Syntax

exportsource.aliasasfilename

Details

The exported file will be created under <base_dir>/exported. The filename parameter may include a path as well as the filename to export, so long as it does not contain the substring ".." and is not an absolute path. If the path contains one or more directories that do not exist then they will be created.

Exporting usage

The source.alias argument is the DSET ID to export (see Core concepts for more information on DSET IDs).

The export statement can be used at any point during this process to save a CSV (conforming to Dataset format) which snapshots the data in the DSET at that moment in time. This can be used for a number of purposes including:

Examining the state of data part-way through processing for debugging purposes
Creating custom exports for subsequent import into 3rd part systems
Producing output CSV files (which may potentially contain merged data from multiple sources) for subsequent processing with another Transcript

Examples

When specifying a Windows path it is advisable to use UNIX-style forward slashes as the path delimiters, or to put the path+filename in double quotes to avoid occurrences of\t being interpreted as a TAB character

The following Transcript will import a quoted CSV file, add a ProcessedFlag column to it with a value of 1 in each row, and save it back out again without quotes:

option quote = \"
use usage from azure
create column ProcessedFlag value 1
option noquote

# Will be exported as <basedir>\exported\azure\flagged.csv
export azure.usage as "azure\flagged.csv"

finish

Overview

The finish statement creates a Reporting Database File (RDF) from a DSET. The RDF can subsequently be used by the reporting engine.

Syntax

finish[dset.id]

Details

The finish statement is used to create an RDF from a DSET. Only a single DSET can be used to create an RDF, but multiple finish statements may be used within the same task file. If there is no dset.id parameter then the default DSET will be used.

The RDF created by finish will be saved as <BaseDir>\system\report\<yyyy>\<MM>\<dd>_source.alias.rdf

where: