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

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

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

Login to your AWS portal and access the Exivity offering here.

1. Click on Continue to Subscribe.

2. Read our Terms and Conditions, and when ready, click on Continue to Configuration.

3. Select the Region where you want to deploy Exivity and click on Continue to Launch.

4. In Choose Action select Launch though EC2 and click on Launch to access the Deployment Wizard.

Deployment Wizard

1. 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.

2. 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.

3. 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.

4. 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.

5. In the Security Group section, you can leave the default recommended security group or add more rules if needed. Click on Review and Launch.

6. 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

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_DNS>:8001

The default admin username is admin with password Instance ID.

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

Exivity can be installed in your on premise data center using the provided installer. You can automatically deploy it using the silent installation command line options. But you may also execute it as a interactive installer.

Before you can install Exivity, you'll need the following:

1. A system that complies with the Exivity minimal system requirements

2. The Exivity software installation executable

3. A valid Exivity license key

If you need help on fulfilling 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 on screen:

• Provide a valid license key

• Pick a folder to install the Exivity program Files

• Pick a folder that Exivity will use to save its configuration, extraction and reporting files. This is called the Exivity home directory

• Provide the TCP port for the Exivity API (default: 8002)

• Provide the TCP port for the Exivity GUI (default: 8001)

• Choose a admin username and password

• After the installer completes, you can automatically start it.

Silent Installation

To execute a silent installation, consider the following command line parameters:

<setup>.exe /S /EXIVITY_PROGRAM_PATH=[path] /EXIVITY_HOME_PATH=[path] /ADMIN_USER=[user] /ADMIN_PASSWORD=[password]

EXAMPLE:

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

Upgrading

Upgrading your installation of Exivity is very simple and fast:

Manually upgrade

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

Silent upgrade

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

Installing a valid SSL certificate

Exivity comes standard with an insecure 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:

1. Download the 32 bit version of openssl.exe from https://slproweb.com/products/Win32OpenSSL.html, and install this tool on the Exivity server

2. Use the openssl.exe executable to generate a valid key file on the Exivity server by executing the following command:

   • C:\TEMP>c:\location\to\openssl.exe genrsa -out exivity.key 4096

3. Then run the following command to create a certificate signing request file:

   • C:\TEMP>c:\location\to\openssl.exe req -new -key exivity.key -out exivity.csr

4. You will be asked to enter general information like company name, city, etc. However, 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.

5. The generated CSR file should be send to your Certificate Authority. After processing by your CA, you shoud receive back a .crt file. Rename this file to exivity.crt and copy it, together with your exivity.key file, in your %EXIVITY_PROGRAM_PATH%\server\nginx\conf. This should overwrite the existing key and crt files

6. Now restart the Exivity Web Service Windows service, and your signed certificate should now be active.

Configuring a separate web server portal

In some cases it might 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 goal, first install Exivity on both nodes using the standard procedure described here. After installing the Exivity software on the system that should become the User Portal, create the following file in the directory%EXIVITY_PROGRAM_PATH%/server/nginx/conf/sites-enabled:

server {
    listen                  443 http2 ssl;
    server_name             localhost;

    ssl_certificate         exivity.crt;
    ssl_certificate_key     exivity.key;

    error_page              497 301 =307 https://$host:$server_port$request_uri;

    root                    web/glass;
    index                   index.html;

    charset                 utf-8;

    location ~ /v[0-9]+?/ {
        proxy_pass          https://HOSTNAME_BACKEND_PORTAL:8002;
        proxy_buffering     off;
    }
    
    location / {
        proxy_pass          https://127.0.0.1:8001;
        proxy_buffering     off;
    }

    access_log              logs/access-webproxy.log;
    error_log               logs/error-webproxy.log error;

    location = /favicon.ico {
        access_log          off;
        log_not_found       off;
    }
    location = /robots.txt  {
        access_log          off;
        log_not_found       off;
    }

    client_max_body_size    100m;
}

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

The second item that requires configuration is the config.json in the %EXIVITY_PROGRAM_PATH%/web/glass directory on the User Portal:

{
  "whiteLabel": false,
  "apiHost": "https://HOSTNAME_USER_PORTAL"
}

Replace HOSTNAME_USER_PORTAL with the actual hostname or IP address of the system that serves as your Exivity User Portal. In case the User Portal should be accessible from the internet, you will need to ensure to provide the fully qualified domain of the User Portal.

Once you have applied these changes, restart the Exivity Web Service Windows services. You should now be able to access your Exivity User Portal.

The following articles provide information regarding the basic concepts around which Exivity is built. These concepts are referenced repeatedly throughout the documentation and as such the below articles are recommended reading:

• CSVs Datasets and DSETs - The input files to Transcript and how their contents are accessed by Transcript tasks

• Data Date - The date against which data is to be processed by Transcript

• Base Working Directory - the directory within which Transcript and Edify operate

• Services - Definitions of chargeable items created by Transcript and used by Edify to generate financial information on reports

• Reporting Database Files - the files produced by Transcript and used by Edify to generate reports

When Transcript is executed one of the command line arguments it requires is a date in yyyyMMdd format which is termed the data date. The activities performed by Transcript are associated with this date in several ways, most notably:

• when importing using automatic source tagging which specific Dataset file to import from \collected

• to determine the concept of 'today' when processing files containing data with timestamps spanning multiple days

• to determine the output directory into which RDF files will be generated

• to generate the filename of the RDF files generated

The data date is made available to a Transcript task through the automatic creation of the ${dataDate} variable

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:

1. Extract

2. Transform

3. Report

4. 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.

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 runs fine in these desktop browsers (with at least the specified version):

• Google Chrome 63

• Microsoft Edge 16

• Opera 50

We don't support Apple Safari and Mozilla Firefox at the moment due to missing features in these browsers.

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

Login to your Azure Portal at https://portal.azure.com and then go to the Marketplace to search for the Exivity offer:

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

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

3. 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:

4. 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.

Next steps

The main program directory as it should be installed by the Exivity installer:

root
├─── bin                        Backend binaries
|    ├─── exivityd.exe
|    ├─── eternity.exe
|    ├─── edify.exe
|    ├─── transcript.exe
|    └─── use.exe
├─── server                     Frontend dependencies
|    ├─── nginx
|    ├─── php
|    └─── redis
├─── web                        Compiled frontend repositories
|    ├─── glass
|    └─── proximity
├─── *.bat
└─── uninstall.exe

CSV files

CSV files are a way of storing data in a table format. A table consists of one or more rows, each row containing one or more columns. There is no formal specification for CSV files although a proposed standard can be found at https://www.ietf.org/rfc/rfc4180.txt.

Although the 'C' in 'CSV' stands for the word comma, other characters may be used as the separator. TAB and semicolons are common alternatives. Exivity can import CSVs using any separator character apart from an dot or an ASCII NUL and uses the comma by default.

Datasets

A Dataset is a CSV file, usually produced by USE, which can be imported for processing by a Transcript task. To qualify as a dataset a CSV file must meet the following requirements:

1. The first line in the file must define the column names

2. Every row in the file must contain the same number of fields

3. A field with no value is represented as a single separator character

4. The separator character must not be a dot (.)

5. There may be no ASCII NUL characters in the file (a NUL character has an ASCII value of 0)

When a DSET is exported during execution of a Transcript task, the exported CSV file will always be a Dataset, in that it can be imported by another Transcript task.

Although datasets are generated by USE as part of the extraction phase, additional information may be provided in CSV format for the purposes of enriching the extracted data. This additional CSV data must conform to the requirements above.

DSETs

A DSET is the the data in a Dataset once it has been imported by a Transcript task. A DSET resides in RAM during the Transform phase and, if referenced by a finish statement, is then stored in a database file for long-term use.

A Transcript task may import more than one dataset during execution, in which case multiple DSETs will be resident in RAM at the same time. It is therefore necessary for any subsequent Transcript statement which manipulates the data in a DSET to identify which DSET (or in some cases DSETs) to perform the operation on. This is achieved using a DSET ID which is the combination of a Source tag and an Alias tag.

Source/Alias tags and DSET IDs

After a Dataset has been imported the resulting DSET is assigned a unique identifier such that it can be identified by subsequent statements. This unique identifier is termed the DSET ID and consists of two components, the Source and Alias tags.

The default alias tag is defined automatically and is the filename of the imported Dataset, minus the file extension. For example a Dataset called usage.csv will have an alias of usage.

The Transcript import statement can take one of two forms. Depending on which is used, the Source tag is determined automatically, or specified manually as follows:

Automatic source tagging

By convention Datasets produced by USE are located in sub-directories within the directory <basedir>\collected. The sub-directories are named according to the data source and datadate associated with that data. The naming convention is as follows:

<data_source>\<yyyy>\<MM>\<dd>_filename.csv

where:

• <data_source> is a descriptive name for the external source from which the data was collected

• <yyyy> is the year of the datadate as a 4-digit number

• <MM> is the month of the datadate as a 2 digit number

• <dd> is the day of the month of the datate as a 2 digit number

When importing one of these datasets using automatic source tagging, the source tag will be the name of the directory containing that dataset. Thus, assuming a datadate of 20160801 the following statement:

import usage from Azure

will import the Dataset <basedir>\collected\Azure\2016\08\01_usage.csv, and assign it a Source tag of Azure.

Manual source tagging

When importing a Dataset from a specified path, the Source tag is specified as part of the import statement. For example the statement:

import my_custom_data\mycosts.csv source costs

will import the Dataset <basedir>\my_custom_data\mycosts.csv and assign it a Source tag of costs. Checks are done during the import process to ensure that every imported Dataset has a unique source.alias combination.

Fully Qualified Column names

When a column name is prefixed with a DSET ID in the manner described previously, it is said to be fully qualified. For example the fully qualified column name Azure.usage.MeterName explicitly refers to the column called MeterName in the DSET Azure.usage.

During the execution of a task, or during the generation of a report, a number of files are accessed, both for reading and writing. The user Home Working Directory (referred to throughout this documentation as home_dir or base_dir) is the directory relative to which these files are located.

The home directory should preferably be located on a dedicated volume i.e. D:\exivity\home and it is recommended that it be located on an SSD drive.

RDF stands for Reporting Database File. Exivity uses these to store usage data and configuration information.

Daily RDFs

A daily RDF stores the usage data from which Edify produces report data. Any given RDF contains a single DSET, along with internally managed metadata such as column types and prepared report data.

An RDF is created using the statement in a task. For any given DSET, there can be a single RDF per day, although there may be many RDF files per day in total (one RDF for each DSET).

RDFs are named according to the and ID of the DSET they contain, and have a .rdf extension. For example a DSET with an ID of Azure.usage for the first day of the month will result in an RDF called 01_Azure.usage.rdf.

An RDF containing usage data is located at <home dir>/report/<yyyy>/<mm>/<dd>_<dset>.rdf where:

• <yyyy> is the year of the datadate

• <MM> is the month of the datadate

• <dd> is the day of the datadate

• <dset> is the DSET ID that was used to populate the usage data

The Global RDF

The global RDF (located at<home_dir>/system/global.rdf) contains system-wide configuration data including (but not necessary limited to):

• Service definitions

• Service rate revisions

• Service rate adjustments

• Service categories

• User accounts

• Report definitions (and related metadata such as sychronisation timestamps)

• Metadata about RDF files created by Transcript

• Usergroups

• Security settings

• Account information

• Job Schedules

The p_gp.rdf

The file p_gp.rdf (located in <basedir>/system/report ) is used by the charge engine to store the output of report runs.

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 gigagyte 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

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.

!!! note Daily proration whereby a daily service is prorated based on the hours seen will be implemented in the second half of 2018 and this documentation will be updated at that time to reflect the additional feature

Service definitions

A service definition comprises two categories of information:

1. The service - Metadata describing fixed attributes of the service such as its name, description, group, interval, proration and charge type(s)

2. 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

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

Service attributes

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.

The 'Data Sources' 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:

1. From the menu on the left, select "Data Sources" > 'Extractors'

2. To create a new USE Extractor where you want pull usage or lookup data from, click the 'Add Extractors' button

3. 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.

4. 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'

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 Extractor that you want to change:

1. When you've selected your USE Extractor from the "Data Sources" > 'Extractors' list, you can change the USE Extractor variable values via the 'Variables' tab. Any variable value (except encrypted variables) from the USE Extractor script, can be change via this menu

2. In this example there is a variable called User, which has a value of vcenter_admin. We can change this value simply by changing that value to something else: my_new_user

3. When clicking 'Save' at the bottom right of this screen, you can save your changes to this USE Extractor

4. If you want to make more fundamental changes to this USE Extractor, you may click on the 'Editor' tab just next to the 'Variables' tab.

5. In the 'Editor' screen, you can make more advanced changes to your script. Such as:

   • changing existing API calls

   • changing csv output format

   • provide usernames / password that require encryption

6. In case you want to save your changes, click the 'Save' button at the bottom of the 'Editor' screen. To delete this USE Extractor, 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 Extractors

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

1. After you have selected the USE Extractor that you would like to run, click to the 'Run' tab next to the 'Editor' tab

2. 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

3. 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

4. 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.

5. 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.

6. If your Use Extractor requires additional parameters, you may provide these as well in the 'Schedule with these arguments' text field.

7. 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.

The 'Data Sources' 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:

1. From the menu on the left, select "Data Sources" > 'Transformer'

2. To create a new Transformer to normalise and enrich USE Extractor consumption and lookup data, click the 'Add Transformer' button

3. 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.

4. Provide a meaningful name for your Transformer. In the above example we're creating an Transformer for a consolidated bill of various IT resources. Therefore we call this Transformer : 'IT Services Consumption'

5. When you're done creating your Transformer, click the 'Insert' at the bottom of the screen.

!!! INFO 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:

1. When you've selected your Transformer from the "Data Sources" > 'Transformers' list, you can change the Transformer script in the editor

2. In this example, we're adding a 'services' statement using auto completion, to simplify the creation of services

3. 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:

1. After you have selected the Transformer that you would like to run, click to the 'Run' tab next to the 'Editor' tab

2. 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

3. 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 futher investigations or troubleshooting, consult the "Administration" > "Log Viewer" screen

4. 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

5. 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

6. 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.

!!! warning The as of version 1.6, it is recommend to use the Workflow function instead of the Extractor schedule

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:

1. a certain amount of money (i.e. $ 100)

2. a certain quantity (i.e. 100 GB/hours)

3. 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:

1. From the menu on the left, select 'Catalogue' > 'Adjustments'

2. Then select the Account from the list of accounts for which you want to create an adjustment policy

3. After selecting the account, click 'Add Policy', and provide a meaningful name for your policy in right screen where it says 'Adjustment name'

4. Provide the Start date, by selecting the initial month when this adjustment policy is applied

5. 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.

6. Select which Service or Service Category this policy is applied to. You are able to select multiple using the check boxes that are provided.

7. Select a Type for this adjustment. This can be either a Discount or a Premium

8. Select the Target, meaning: is this Adjustment targeting the total Charge or the total Quantity of the selected service(s)?

9. Select the Difference setting, to indicate an Absolute value (i.e. 100 units, or 100 dollars) or a Relative value (such as 10%)

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.

11. When you're done, click the Add Policy button. Your changes are now applied to all charge related reports.

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 the necessary service(s) via the 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 ).

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 'Catalogue' > '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:

1. 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

2. 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)

3. 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

4. It is possible to configure a COGS rate for this service. This is applied the same way as the Per Unit rate

5. 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

7. 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.

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 or statement.

Obtaining details for a Service

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

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

1. The description or friendly name for this service

3. The time stamp when the service was created

4. The time stamp when the service was updated

5. The DataSet where this service relates to

6. Where to obtain the service name from (in header or in data). The value will be used for the service description (see 1)

7. The source column that has the consumed quantity

8. The Instance column refers to the chargeable instance column value (i.e. VM ID) which is required for automatic Services

9. The interval that defeines the frequency of how often this service is being charged. Meaning: automatic (every occurrence/record/hour), daily or monthly

10. 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

11. 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

12. 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

13. Exivity will in a future release allow the user to change and create services through the web interface. At this time, this feature is disabled.

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:

1. Navigate to the Catalogue > Services 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 Definition.

2. 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 Definition. You can then select the Delete button next to the Edit button, to delete all selected services.

3. If you want to change the configuration of one of the services, you should first select the service which you'd like to change.

4. 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.

5. Ensure to re-Prepare your report in the Report Definitions in case you have made any changes.

Exivity allows you to configure a number of system variables easily, such as:

• custom logo, branding and color schema

• currency and decimal values

• date format

• fiscal year

• custom css

To change these and other settings, follow the instructions provided in this document.

Branding

To apply custom branding to the Glass interface, use the 'Administration' > 'Configuration' menu, and then select the 'Branding' tab:

The following GUI adjustments can be done:

1. Provide your custom product name

2. Change the logo as show at the left top part of the screen. The rectangular logo that says "Exivity" by default

3. Change the square logo at the top left part of the screen. Next to the brand logo that says "Exivity" by default

4. Change the favicon as shown in your browser favorites bar

5. Change the GUI default color using the drop down list.

6. If you want to change other colors, font types and other CSS specific parts of the GUI that are not listed in this menu, you can use the 'Show Advanced' button, to add custom CSS code

7. Click the 'Save' button to apply your changes/

Formatting

To change country specific formatting or decimal rounding, use the 'Administration' > 'Configuration' menu, and select the 'Branding' tab:

The following changes to the formatting can be done:

1. Change the system wide Currency settings

2. Configure comma or dot value for the decimal separator

3. If you want to configure a thousands separator, you can choose between comma, dot and space

4. Rate precision refers to the amount of decimal values shown on reports for the (average) rate value

5. The Report precision can be changed to limit the number of decimal values shown on non-invoice reports

6. The Invoice precision can be changed to limit the number of decimal values shown on invoice cost reports

7. The quantity decimal values can be limited using Quantity precision

8. To apply a system wide date format, choose your preferred date format from the Date format drop down list

Reporting settings

To change reporting settings, such as the address lines and logo on your invoice report, use the 'Administration' > 'Configuration' menu, and select the 'Reporting' tab:

The following changes to the reporting configuration can be done:

1. Use the Address lines text field to provide a custom address which will be shown on the textual invoice cost reports

2. To change the Logo that will be shown on the right hand side of your invoice cost report, use Select File to browse and select your custom logo

3. A custom footer text can be added to your invoice report using the text field next to Extra text

4. To adjust the maximum amount of series shown in the Exivity graphical report line graphs, change the value for Maximum graph series

5. If you have a non-standard fiscal year start, you may change the Reporting start month using the provided drop down list

6. Use the Save button to apply your changes

Mail Server Settings

Exivity is able to send out e-mail messages for certain tasks. This can be used for resetting user account passwords and sending out events such as failing workflows. To enable this functionality it is required to configure an SMTP server. This can be done using the following procedure:

• Navigate to: Administration > System > Environment

• Ensure to fill in your SMTP server details as shown in above image

• Click the Update button to save your changes

• If you now logout and try to login again, you will notice a "Reset Password" link. This can be used by all of your users, as long as a valid e-mail address has been associated to each user account

Advanced

config.json

In order to access advanced configuration for all users which can't currently be modified through the interface, edit the file config.json in the web/glass subdirectory of the Program directory.

The default contents of this file should look like this:

{
  "whiteLabel": false,
  "apiHost": null
}

Configure a default API domain

When logging in, users have the option to change the API domain by clicking the Change domain link:

In order to specify a default for all users, edit the config.json file and add your default domain as the apiHost option:

{
  "whiteLabel": false,
-  "apiHost": null
+  "apiHost": "https://example.com:443"
}

Full white-label

To never show any Exivity related branding in the interface, set the whiteLabel option to true:

{
-  "whiteLabel": false,
+  "whiteLabel": true,
  "apiHost": null
}

When the whiteLabel option is enabled, certain functionality will be disabled in the GUI:

• Manual clearing of caches on the About page.

• Documentation links in the header will be turned off.

The Groups and Users menu's, allow you to configure Role Based Access and Account level permissions. Groups are created to define custom restrictive roles, that can be associated to users that must have access to only certain parts of the Glass interface. Users are created to provide login credentials for other users of the Exivity system. A user can be associated to a single Group, and can have access to all accounts or to a subset of accounts.

Creating Groups

To create a custom group with limited access to the Exivity Glass user interface, follow this procedure:

1. Go to 'Administration' > 'Groups', then click 'Add Group'

2. Provide a meaningful Name for this Group

3. Select one or multiple permissions from the custom list of 'Role Names' below

4. When you're done with your selection, click the 'Save' button to create your group. Now you can create Users and associate them to this group.

   5.

Creating Users

To create a User that will only have access to a certain account or a selection of several accounts, use the following procedure to create a user with special account level permissions:

1. Go to 'Administration' > 'Users', then click 'Add User'

2. Provide a login name in the 'Username' field, a valid 'E-mail' address, and a 'Password' of at least 8 characters

3. From the 'Group' dropdown box, select the 'Group' you want to associate to this users

4. Next, click the 'Account Access' tab, and select the 'Access Type' for this user:

   • Grant access to all accounts - to provide access to any of the available accounts in the system

   • Grant access to only specific accounts - to only provide access to usage and charge data of specific accounts

5. If you've selected Grant access to only specific accounts, then select from the Account Access list, each Account Name where this user should have access for. You may select a top level account, or an account further down in the hierarchy. Account inheritance is applicable, meaning lower level accounts are automatically included when selecting a top level account. If there are multiple Report Definitions, select the corresponding Definition from the drop down list first, before selecting any accounts.

6. When all fields have been filled in, you may create the user by clicking the 'Save' button.

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:

1. Create an Access Key and Secret in your Azure EA portal

2. Configure the Azure EA Blob Extractor

3. Configure your Azure EA Transformer

4. 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

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 EA:

• Pick Azure EA Blob 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:

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.

The Workflows menu allows you to schedule various tasks and execute them at a specific date and time. This allows the execution of different Extractors and Transformers, so that they are tightly chained together.

Creating a Workflow

To create a new Workflow, go to 'Administration' > 'Workflows', then click the green button '+ Workflow':

1. Provide a meaningful name for the Workflow

2. Optionally you can provide a detailed Description

3. The Start date of the Workflow, when it will run the first time

4. The interval: Hourly, Daily or Monthly

5. At what time should the Workflow start

6. Provide an interval value. I.e. provide 2 for every second Hourly / Daily / Monthly, depending on your configured interval

7. Add a new step in your work flow

8. Set the Type of the step using the drop down menu

9. Provide the option that goes with the selected Type. This can be your Extractor, Transformer, Report or other name

10. Depending on the selected Step Type, you can provide an offset date. This value is used during execution of that step. Typically this would be used for a From date offset (i.e. -1 for yesterday)

11. A To date offset can be provided for some step types (i.e. 0 for today)

12. Additional arguments can be send to the step. This is typically used for some Extractors and when executing a custom Command

13. You can delete a step using the red minus button

14. To view historical Workflow results, click the Status tab

Special Workflow Steps

Apart from adding Extractor, Transformer and Report steps, there are two different Workflow Step types:

• Core

• Execute Command

Core

The Core command allows you to run a few predefined API calls, currently these are the following:

• Run garbage collector

  • Cleans up the server getPrices cache table and Redis cache

• Purge cache

  • This will Unprepare any prepared reports. Use with caution

• Refresh budgets

  • Evalualte all configured budgets

• Send heartbeat

  • Send an API heartbeat request. For future use.

Execute command

The Execute Command step enables you to execute an external command, like a script:

As an example: you could run a Powershell script to obtains some data from a special data source that Exivity Extractors are not able or allowed to connect to. This script could be executed the following manner:

powershell.exe "D:\script\special.ps1"((-).addDays(-1)).ToString("""yyyyMMdd""")

The above command calls the Powershell executable to run the special.ps1 script, with a dynamically generated parameter that is evaluated at run time. This particular example always provides yesterdays date in yyyyMMdd format as a parameter to the special.ps1 script. Many other variations and scripting languages are possible. Feel free to experiment.

Introduction

When deploying the Azure CSP 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:

1. Create a Partner Center Web Application

2. Configure Extractors for Azure CSP Usage, Billing & Ratecard

3. Configure Transformers

4. Create your Report

5. 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, , 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.

• Go to the Billing section.

• Open the last month's invoice in pdf format.

• Take note of your billing period

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:

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.

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.

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 before proceeding.

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:

1. Solution Overview

2. Launching the CloudFormation Template

3. Creating the Lambda function and API Gateway

4. Configuring an Extractor

5. Configuring a Transformer

6. 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:

1. The name of your AWS Cost and Usage report.

2. 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

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

• 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

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:

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.

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.

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.

v1.8.1

June 06, 2018

New features

• Minimum commit is now supported in the charge engine When generating a report, the results for any services that have a minimum commit value (and for which the usage does not meet that minimum commit quantity) will be adjusted to reflect that minimum commit value.

• Updates to internal service and rate schema The rate attributes min_commit and threshold are now implemented and the API will return a slightly different schema for the /v1/services endpoint (and related /v1/dump models) - the rate_type attribute is now called charge_type. More information can be found at https://api.exivity.com/#0f8c2ac2-1f56-3c54-3000-210a5e94bd61

• The charge engine now includes information about proration adjustments When applying proration to a monthly service, the charge engine will now include information in the raw report data which shows the amount that the unprorated charged was reduced by. This information will be used by the GUI in a future release.

• Proration is now applied to monthly services where applicable Report results for monthly services that are flagged as being prorated will now reflect a percentage of the monthly charge, based on the number of days in the month that the service is used.

• GUI preferences are now saved for each user For example, selected reports, date ranges and filters are now persisted for each user, so they can be restored after logging out and in again.

• The charge engine can now execute a script passed to it via standard input The charge engine can now execute a reportfile passed to it via standard input. This internal change results in fewer termporary files on disk during normal use.

• Error reporting can now be disabled in configuration

• Transcript can now import usage data from existing RDFs The 'import' statement in Transcript can now retrieve the raw usage data from an existing RDF file.

• Usernames are no longer case sensitive when loggin in

• Transformers now always run with loglevel = warn when triggered in workflows

• Service and service category filters now only show items actually in the visible report

• USE will now trap more HTTP errors When enacting some HTTP operations, if an error such as a timeout or invalid host is encountered, USE will now return an error in the HTTP_STATUS_CODE variable instead of automatically terminating the script.

• Added daily usage information for monthly services in the charge engine When generating a report, the charge engine will now include information about the usage quantity for each day in the charge interval. This information will be used by the GUI in a future release.

• Drilldown functionality is now available from the legend in reports

• Reference account information in ETL Account information can now be imported directly during the data transformation step, such that existing account data can be used to enrich the data being processed.

• Increased HTTP client timeout USE will now wait for a three minutes by default before deciding that the connection has timed out if no data is received after the initial connection to a server has been made.

• Improved the syntax for options to the 'import' statement in Transcript The options supported by the 'import' statement must now be formatted such that there is a single option per line of script. This removes the previous requirement to quote the list of column names when using 'select' and 'ignore', as well as the requirement to quote the expression used by the 'filter' option.

• Added a system variable to return the last day of any given month A new system variable has been implemented which will return the last day in any given calendar month.

• The 'correlate' transform now supports a default DSET for column names The 'correlate' statement always uses the default DSET as the destination for correlated columns but now supports an 'assume' parameter which determines the default DSET within which to locate non-fully-qualified source columns.

• Added a button to the detailed widget in reports to toggle search field

• Added an option to configuration to add a custom Google Analytics property

• The charge engine can now be used to identify unused service definitions

  The charge engine now supports the ability to retrieve a list of services which are not used by any existing reports.

Bug fixes

• Fixed an issue where the depth filter wouldn't reload after preparing a report

• Improved the print/PDF layout of consolidated invoices

• Fixed a bug where the summary in Instance reports would sometimes remain empty

• The charge engine now correctly deletes un-needed RDFs The charge engine now includes a mechanism to 'unload' historical data. This is an internal mechanism which will be used by the GUI in a future release.

• Services for users with limited access to accounts are now filtered

• When creating services, an instancecol parameter is now required _Previously it was possible to create services with no instance_col specified. This would result in missing data in reports if no instance_col was specified. Transcript now requires that an instance_col parameter is provided to the 'service' and 'services' statements.

• Consolidated invoices can now be exported to PDF

• Fixed an issue where in some circumstances the reports wouldn't load

• It is now possible to view budget audit trails

• The 'import' statement in Transcript now correctly imports usage data in all forms of the statement Fixed a bug whereby when using automatic source and alias tagging, the 'import' statement would not permit the importing of usage data from an existing RDF

• Improved readability of text when a light background colour is chosen

• USE will no longer reject some valid expressions In some cases, a valid expression in a script was rejected as having an unbalanced number of brackets. This has now been fixed.

• The charge engine can now delete services associated with DSETs that are unused by any reports Fixed a bug where the charge engine would not correctly delete services if there were no RDF files for the DSET that the service is associated with.

• The reset pins button has been moved to the top of the detailed widget in reports

v1.7.0

May 03, 2018

New features

• Implemented search field in report details table Ability to filter and pin a selection using a search query in the Accounts, Services and Instances report details table

• Quantity adjustments can now be applied to a customer Adjustments can now also be set to affect quantities instead of charges. Both relative and absolute quantity adjustments are supported.

• Ability to show consumed quantity in a report

• Ability in transcript to convert number bases The following is now possible in a transcript: convert colName from dec|hex to dec|hex

• It is now possible in the Invoice cost report to consolidate all child accounts on a single page

• Added option to create workflow step which purges Proximity cache.

• Beta version of budget manager & viewer is now available.

Bug fixes

• When encrypting a variable it could get corrupted

• Transcript could previously crash when running for a large date range

• Workflows status tab did not consistently show historical log files

• Fix for Invoice report error "Depth can't be empty, 0 or greater than 5"

v1.6.2

April 13, 2018

Bug fixes

• Extractor arguments where not used correctly when running USE script interactively from GUI

• Report timeline graph could previously show zero when there's consumption

  v1.6.1

April 13, 2018

New features

• Add profile page where logged in users can change their own e-mail address and password.

Bug fixes

• Fixed issue where scheduling multiple steps could corrupt Workflow WARNING: as of this release it is required to re-create your Workflows from scratch, to avoid potential issues

• Fix loading overlays to improve multitasking in GUI

• Fixed an upgrade bug which caused creating report definitions to be broken

• Ability to specify to and from dates for transformers in workflows.

  v1.6.0

April 8, 2018

Notable new features

• [EXVT-971] - Implement day and month name variables in USE

• [EXVT-973] - Add option to enable.disable client certificate support in USE

• [EXVT-979] - Scheduler is now called workflows

• [EXVT-1021] - When using COLNAME_NOT_EXISTS in a filter, it always evaluates to 'TRUE'

• [EXVT-1024] - Internal Error when applying filters in a 'where' statement if import options are used

• [EXVT-396] - Garbage collector

• [EXVT-744] - Glass config file for default port/host

• [EXVT-749] - Stacked bar chart option in accounts/services report, including optimized legend

• [EXVT-805] - Extend `Run` tab in Transformer with `from` and `to` date

• [EXVT-966] - Added Instance reports

• [EXVT-985] - Make toggle so reports can go fullscreen

• [EXVT-986] - Connect graphs + legend

• [EXVT-988] - All lists in the front-end are now sorted alphabetically

• [EXVT-993] - Ability to pin report items

v1.5.0

March 26, 2018

Notable new features

• [EXVT-370] - Add support for SAML Single Sign-On

• [EXVT-849] - Add ability in transcript aggregate to average the values from a column

• [EXVT-879] - Add ability to base new extractor on templates from GitHub repository

• [EXVT-958] - Enhance the 'hash' statement in USE to support base-64 encoding of the result

• [EXVT-780] - Fixed manually editing the value of an encrypted variable in USE can cause a crash

• [EXVT-924] - Fixed Eterenity hourly schedule does not consider start date

• [EXVT-946] - Fixed OSI_TIME_UTC variable is missing a trailing Z

• [EXVT-947] - Fixed some accounts show slight discrepancies when comparing to Excel calculation

• [EXVT-949] - Fixed radio buttons don't update when changing adjustments in Glass

v1.4.1

March 19, 2018

Notable new features

• [EXVT-940] - Fixed duplicate headings not always eliminated in filtered import in Transcript

• [EXVT-941] - Fixed a Transcript crash on 'move rows' or 'delete' after a 'replace'.

v1.4.0

March 16, 2018

Notable new features

• [EXVT-871] - Added 'include' statement to Transcript

• [EXVT-932] - Added UTC versions of time-related variables in USE

• [EXVT-105] - The API can now render an invoice report as a native PDF document

• [EXVT-691] - Ability to change the service description via the GUI

• [EXVT-779] - Made updating of Extractor variables more robust, and added support for encrypted variables in the GUI

• [EXVT-810] - Added the ability to use wildcard in import statement in USE

• [EXVT-823] - Added a daterange wrapper for Transcript

v1.3.1

February 23, 2018

Notable new features

• [EXVT-889] - Fixed a corner case where USE can stop working when executed from Glass

v1.3.0

February 23, 2018

Notable new features

• [EXVT-720] - Add option to choose custom currency symbol

• [EXVT-741] - Move report selector to sidebar

• [EXVT-847] - Improved syntax highlighting for USE and Transcript in the Glass script editor

• [EXVT-858] - Implement rounding in Transcript

• [EXVT-868] - Add additional checks to global conditions in Transcript

• [EXVT-827] - Implement import filters in Transcript

• [EXVT-881] - Add escaping option to import statement in Transcript

• [EXVT-476] - Added scheduler interface

• [EXVT-798] - Add report depth breadcrumbs to reports

• [EXVT-832] - Extractor log is now shown when running on-demand through GUI

• [EXVT-842] - Fixed an issue which caused small discrepancies when using different reporting definitions

A full changelog is available upon request.

v1.2.0

February 09, 2018

Notable new features

• [EXVT-102] - Ability to extract data from databases using ODBC connection

• [EXVT-693] - Scheduler endpoints in API

• [EXVT-802] - Ability to schedule the preparation of report definitions through the GUI

• [EXVT-774] - Enhanced conditional execution in Transcript with support for regex matching

A full changelog is available upon request.

v1.1.1

February 03, 2018

Notable new features

• [EXVT-818] - Fix for Cannot read property 'relationships' of undefined error when logging in as a user with limited account permissions.

A full changelog is available upon request.

v1.1.0

February 02, 2018

Notable new features

• [EXVT-253] - Syntax highlighting for USE

• [EXVT-255] - Add support for XML data extraction in USE

• [EXVT-600] - Enable parallel processing in Eternity

• [EXVT-681] - Create USE script for reading AWS S3 bucket

• [EXVT-717] - Extractor and Transformer execution must show last 25 lines of corresponding log file

• [EXVT-739] - Perform cross-browser test and add warning in unsupported browsers.

• [EXVT-770] - Improve orbit performance when syncing large amounts of records

• [EXVT-777] - Select single days in datepicker

• [EXVT-783] - Support in Eternity for hourly and monthly schedules

A full changelog is available upon request.

v1.0.0

January 12, 2018

Initial release.

🥂

Upgrading

Upgrading to the latest release is a straight-forward process. More information.

When upgrading to new major version (e.g. from version 1.x.x to version 2.x.x), also take note of these manual upgrade steps:

• Upgrading to version 2

Changelog

v2.3.1

January 23, 2019

New features

• The 'export' statement will no longer generate an error if asked to process an empty DSET while option mode = strict is set

• Implemented user-definable timeout setting when retrieving data from HTTP sources

  When retrieving data from HTTP sources, the number of seconds to wait for a server response before timing out can be defined using set http_timeout.

v2.3.0

January 17, 2019

New features

• Added editable labels for report levels

• Added the ability to mass delete services

• Invoice report is now called Summary report

• The 'finish' statement in Transcript can be made to cause the task to fail if the DSET it's given is empty

  Previously, when creating an RDF, the finish statement would perform no action if the DSET to create the RDF from was empty. This is still the case if 'option mode = permissive' is in force, but if 'option mode = strict' then the finish statement will now generate an error. The error will cause the task to fail for the current day, and if 'option continue' is not enabled, the task will be terminated, else the task will move onto the next date in the range of days being processed.

• When importing multiple files using the pattern option to import, any invalid files will be skipped When multiple files matching a pattern are imported, any of those files that are malformed or otherwise non-importable will be skipped.

• Added the ability to exit a subscript invoked via #include in a transform script The 'return' statement, when used in a Transform script, will now cause script execution to resume from the statement following the #include statement in a parent script that referenced the script containing the 'return' statement.

• Increased the performance of the correlate statement Correlation should now be significantly faster than it was previously

• Increased the performance of the aggregate statement

  Aggregation should now be significantly faster than it was previously

Bug fixes

• Fixed a bug where users couldn't update their own details (including their password)

• Fixed a bug where only 10 datasets were displayed when creating a new report

• Fixed a bug where the mail sender name was not persisted in the configuration

• Fixed an issue when importing CSV files containing quotes

  When importing a CSV file with two successive quote characters at the end of a field, Transcript would reject the file as invalid. This has now been fixed.

• Fixed an issue where deleting data let to GUI crashes on occasion When deleting data (RDFs) associated with a report, it could be that if one or more days had previously been overwritten, a stale database entry would cause issues after the RDFs were deleted. This has now been fixed.

• Fixed a bug whereby using terminate within the body of an if statement in a Transform script could cause an error Invoking terminate in an if block when running a transform script against a range of dates could cause the error The maximum number of nested blocks (32) is already in use. This has now been fixed.

v2.2.1

December 13, 2018

Bug fixes

• A bug was fixed which could lead to an error in the invoice report when using a rate with a minimum commit set

• Fixed an issue with minimum commit It was possible that when applying minimum commit to a service, that other services would be affected by that minimum commit. This has now been fixed.

• Fixed an issue when retrieving NULL fields from an ODBC query When using ODBC to collect data, the presence of NULL values in the results could cause USE to crash. This has been fixed.

v2.2.0

November 30, 2018

New features

• Added the ability to view instance level details on the invoice reports.

• Added the ability to customize the report exports (CSV format only) field delimiter and decimal separator. These settings are system-wide and available to administrators by navigating to Administration > Configuration > Formatting.

• Added the ability for users to reset their own passwords. This requires the email address of users to be set and a working server configuration for sending emails. This can be configured in Administration > System > Environment.

• Logfiles generated from workflow tasks now include a timestamp. This prevents logfiles from consecutive runs of the same task from being overwritten.

• Workflow status now automatically refreshes after a manual run.

• Added a new Environment tab in Administration > System. In this tab, information about the system the Exivity instance is running on can be filled out. In the future this will be expanded to include more configuration options.

• Invoice reports now include minimum commit uplifts as separate entries.

• Carriage-returns and line-feeds in data extracted using ODBC are now replaced with spaces. When extracting data with USE, the presence of newlines in the data could cause corrupt CSV output. Carriage Return and Line Feed characters in data extracted from ODBC are therefore now replaced with spaces.

• Enhanced expression support in the Extractor component. Conditional expressions have been enhanced in the Extractor component such that more complex conditions can be evaluated and additional operations can be performed. Additionally, it is now possible to set a variable value using an expression.

• Services can now be manually deleted using the GUI.

Bug fixes

• The datasets selector visible when creating a new report definition is now alphabetically sorted.

• Fixed a bug which caused the contents of extractor editor to not update after updating variables. The contents of the extractor script itself was always saved after updating variables, only those changes were not visible in the editor.

• Fixed a bug which caused the account depth selector to reset after performing an upgrade.

• Fixed a bug which could cause the interface to become unresponsive after preparing a report.

• Fixed an issue when running Transform scripts for days with 25 hours in them. When running a Transform script with a data-date representing a day where the clocks were adjusted such that the day had 25 hours in it, the script would be executed a second time automatically once the first had completed. This could lead to unexpected errors and log entries on occasion, and has now been fixed.

• When writing to CSV files in USE, embedded CR/LF characters are converted to spaces. USE will now automatically strip out embedded carriage-return and line-feed characters when writing data to CSV files. Each unique occurrence of one or more sequential CR/LF characters will be replaced with a single space.

• Fixed a bug whereby the body of an 'if' statement in the Transformer could terminate prematurely. In some cases, using an 'import' statement with an 'options' block within the body of an 'if' statement could cause statements following the 'import' to be skipped. This has now been fixed.

v2.1.5

November 22, 2018

Bug fixes

• Updated the documentation links in the header to point to our new documentation site.

• Fixed grouping behaviour in the details table of the accounts report. In some cases, accounts could appear grouped under the wrong parent account in the 'Detailed' table in the accounts report.

v2.1.4

October 31, 2018

Bug fixes

• Fixed an issue with incorrect quantities sometimes showing on reports. Occasionally, when running a report for a range of dates, the quantities on one or more services differed from the quantity for that service shown when a report was run for a different date range (or just the day in question). This issue has now been fixed.

v2.1.3

October 26, 2018

New features

• The USE 'basename' statement can now write its results to a new variable. Previously, the 'basename' statement would always modify the value of the variable whose name was supplied as the argument. It can now also accept a literal string and create a new, or update an existing, variable to hold the result.

• Archives in GZIP format can now be decompressed using USE. USE now supports the 'gunzip' statement which can be used to inflate GZIP'd data. Details of how to use this statement may be found at https://docs.exivity.com/diving-deeper/extract/language/gunzip

• FIxed an issue whereby when running a Transform script the Audit database would be locked for the duration of the task. Transcript now only opens the Audit database when it needs to, reducing the likelihood of encountering errors pertaining to a locked audit database in the logfile.

• A new system variable is now available containing the number of days in the current month. The existing dataMonth variable, which contains the yyyyMM of the current month is now supplemented with a new variable called dataMonthDays which contains the number of days in that month.

• Changed default service type to 'automatic' in the 'services' statement in Transcript. When creating services, if no 'type' parameter is provided then the default service type will now be be set to 'automatic'.

Bug fixes

• Fixed an issue whereby when creating a service, the audit indicated that the service creation failed. When a service definition is successfully created, Transcript will now correctly audit that event as opposed to indicating that the attempt failed.

• Fixed an issue whereby over-writing services could result in database errors in the logfile. Sometimes when overwriting services, a constraint error would be logged in the logfile and the service would not have any rate associated with it. This has been fixed.

v2.1.2

October 18, 2018

Bug fixes

• Fixed an issue that could cause database corruption.

  Fixed an issue that could cause database corruption due to the Aeon database being held open for long periods of time.

v2.1.1

October 10, 2018

New features

• Added a live preview feature when working with transforms. A new feature has been added which can display a live preview of the transformer output. Note: this feature is currently in beta and will be further updated in the next release.

• The code editor has been updated. The code editor for Extractor and Transformer scripts has been updated (it now uses the open source Monaco editor - https://microsoft.github.io/monaco-editor/) resulting in a significant improvement over our previous editor. This greatly enhances the user experience when editing scripts in the GUI. Note: This change also lays the foundation for more advanced features going forwards.

• Charges for monthly services now take quantity into consideration as well as price. If two or more days in a month have the same highest price then the one with the highest quantity will be reported. Previously, the first seen was reported which could lead to discrepancies between the reported quantity and price on the report.

• When running reports blank instance values are now displayed as a hyphen. When running reports against data with blank instance values in the usage data, the instance value will now be represented as a hyphen, which improves the aesthetics of the report.

• Added hardware information to Transcript log-files. Log-files created by Transcript now contain information about the CPU and RAM at the top of the log.

• Increased auditing information in Transcript. Events relating to service, rate and RDF changes are now audited

Bug fixes

• Removed COGS option for users without rights to view COGS information. In the services and instances report, users with no access to view COGS will no longer be able to select the COGS type in the details table. Note: This bug never allowed users without appropriate access rights to view the actual COGS data.

• Fixed a bug where the list of datasets on the report definition page was only showing the first 10 results. This could result in an inability to create new reports using datasets that were not included in those results

• A link to the instances report has been added to the search feature in the header.

• The service interval column in the instances report now contains data. Previously this column was always blank

• Fixed a bug where searching for units within a services reports leads to a GUI crash.

• Fixed an issue whereby very rarely a charge would not be included in reports. On very rare occasions, information in a record in the prepared report caches was not included in the output when a report was run. This has now been fixed.

• Fixed an issue that could cause Aeon database corruption. Fixed an issue that could cause database corruption (and workflows to fail) due to the Aeon database being held open for long periods of time.

• Fixed an issue whereby re-using an existing named buffer in USE for ODBC purposes could lead to unexpected results. Fixed an issue in USE whereby if an existing named buffer was re-used to store data retrieved from ODBC then a new buffer could have been created with the same name as the existing buffer, and attempts to reference it would return the old data.

• Fixed an issue when executing ODBC queries that return no data. Using the ODBC capability to execute a query that returns no data will no longer cause an extractor to return an error.

v2.0.6

September 05, 2018

New features

• Upgraded the underlying API framework For more information, please refer to the Laravel release notes.

Bug fixes

• Fixed an issue whereby 'append' could crash if one or other DSET was empty When executing the 'append' statement in a transformation script, if one or other of the DSETs involved in the operation was empty (having no data rows) then a crash could occur. This has now been fixed.

• Fixed an issue where an expression that evaluated as FALSE could show the wrong line number in a log message The DEBUG level logfile entry indicating an expression is true or false would contain a reference to the wrong line number if the expression evaluated to false. This has now been fixed.

• Fixed an issue whereby some comparisons would evaluate incorrectly in expressions Fixed and issue whereby in some cases where a value was quoted in an expression, the quotes would be considered part of the value itself.

• Fixed a condition where reports were not showing for non-admins

• Quantity metric is available again for the timeline chart on the services and instances reports

• Reports in the navigation menu dropdown are now alphabetically ordered

v2.0.5

August 28, 2018

New features

• Ability to filter data in report using search query: The search bar in Accounts, Services and Instances reports now supports the use of operators (for example >and <) to filter your results based on column values or strings.

• Add avg_unit_based_rate to report/run API endpoint Added the average per unit rate field to the report/run API endpoint and a placeholder for the average per interval rate which will be implemented later.

Bug fixes

• Fixed an issue where deleting services could lead to adjustments not displaying correctly

• Rate column in report details tables now use the configured rate precision setting

• Fixed an issue whereby scheduled tasks that output more than 4kb of data to the console could suspend execution and do nothing until they timed out

v2.0.4

August 22, 2018

New features

• Transcript can now normalise scientific decimal numbers to standard format: When processing data that contains numbers in scientific format (such as 2.1E-5) the normalise statement can now be used to convert these to standard decimal notation (0.000021 in the above case) using the form normalise columncolNameas standardwhere colNameis the column containing the values to convert. Any values already in decimal will be unchanged, except that any trailing zeros will be removed from them. Non-numeric values will be converted to 0.

• Support group and group_col as service parameters in Transcript: In the serviceand servicesstatements in Transcript, the parameters to define the service category are category and category_colThese parameters now have aliases of groupand group_col respectively, for those who prefer to use that terminology.

Bug fixes

• The replace statement in Transcript will no longer behave unexpectedly when given an empty string as the target to replace: When using replace to update substrings within the values in a column, if the target string (the text to replace) is empty then Transcript will generate a meaningful log entry explaining that it cannot be used to replace empty strings, and will no longer overwrite non-blank column values with multiple copies of the replacement text.

• The export statement in Transcript now supports backslashes as path delimiters: When specifying a relative path for the exportstatement, Transcript will automatically create any directories that do not exist in that path. Previously there was a bug whereby the auto-creation of those directories would only work if UNIX-style forward slashes were used as delimiters in the path. This has now been fixed and Windows or UNIX style delimiters may be used when specifying an export path.

• Fixed a bug in the scheduler that could cause schedules to fail: In some cases schedules could fail for no obvious reason. This has now been fixed.

v2.0.3

August 17, 2018

New features

• USE scripts can now be forced to terminate with an error result Previously, the 'terminate' statement could be used to cancel script execution, but its use would always indicate that the script ran successfully. This may not be appropriate in all cases (for example if an error is detected by the script itself but ultimately cannot be resolved satisfactorily). The 'terminate' statement will still cause a script to exit with a success result by default, but may now be invoked as 'terminate with error' such that an error status is returned instead.

• Added more service attributes as optional columns in the reports details table. The following extra service attributes can now be enabled as columns in the report details table: interval, charge type, cogs type and proration.

• Support 'group' and 'groupcol' as service parameters in Transcript In the 'service' and 'services' statements in Transcript, the parameters to define the service category are 'category' and 'category_col'. These parameters now have aliases of 'group' and 'group_col' respectively, for those who prefer to use that terminology.

• Reduced the chance of a 'database is locked' warning when preparing reports When preparing reports, on occasion it is possible for a warning to appear in the logfile pertaining to the global database being locked. When this warning happened, it could cause some days in the reporting period to remain unprepared. A known specific cause of this issue has been fixed, significantly reducing the likelihood of it happening.

Bug fixes

• Fixed an issue where an ODBC connection could cause a crash in USE When executing an ODBC-based collection in USE, under certain circumstances an incorrect direct connection string could cause a crash. This has been fixed. Additionally, when an ODBC error occurs the error written to the logfile contains more detail than in previous releases.

• The order of workflow steps in the status tab now corresponds to the order of workflow steps in the configuration tab.

• An issue has been fixed where old user preferences could conflict by updates in the GUI, leading to errors when loading the service and instance reports.

• An issue has been fixed where certain characters in a workflow status could lead to errors in the API. Sometimes, when running a scheduled task, the output written to the database contains non-printable characters. The API now re-encodes those characters, which means the GUI will now correctly show the status for those workflows.

• When selecting a reporting period that spans multiple months, the charts will now only show a single label for each month.

• Fixed a USE crash bug with certain combinations of conditional expressions Fixed an issue whereby if an expression with more than 2 parameters was followed later in the script by an expression with fewer parameters than the first, a crash would occur.

• Fixed issue where an extractor could crash when using a parslet after formatting some JSON A bug has been fixed whereby if the 'json format' statement was used to prettify some JSON in a named buffer, use of a parslet to extract data from the JSON could cause a crash.

• Fixed an issue where sometimes an XML parslet would cause an 'out of memory' error in USE When using an XML parslet, it was possible that an 'out of memory' error would be returned in the logfile and the script would fail, even on small input files. This has now been fixed.

v2.0.2

August 03, 2018

Bug fixes

• The 'export' statement in Transcript now supports backslashes as path delimiters

  When specifying a relative path for the 'export' statement, Transcript will automatically create any directories that do not exist in that path. Previously there was a bug whereby the auto-creation of those directories would only work if UNIX-style forward slashes were used as delimiters in the path. This has now been fixed and Windows or UNIX style delimiters may be used when specifying an export path.

• The 'replace' statement in Transcript will no longer behave unexpectedly when given an empty string as the target to replace

  When using 'replace' to update substrings within the values in a column, if the target string (the characters to replace) is empty then Transcript will generate a meaningful log entry explaining that it cannot be used to replace empty strings, and will no longer overwrite non-blank column values with multiple copies of the replacement text.

v2.0.1

July 25, 2018

New features

• Increased default timeout when retrieving data from HTTP servers

  Currently a USE script will fail if more than 3 minutes elapse without response when downloading data from an HTTP server. This has been increased to 5 minutes to cater for slow APIs.

v2.0.0

July 19, 2018

New features

• Transcript can now normalise scientific decimal numbers to standard format When processing data that contains numbers in scientific format (such as 2.1E-5) the 'normalise' statement can now be used to convert these to standard decimal notation (0.000021 in the above case) using the form 'normalise column colName as standard' where 'colName' is the column containing the values to convert. Any values already in decimal will be unchanged, except that any trailing zeros will be removed from them. Non-numeric values will be converted to 0.

• When accessing the GUI via http visitors will be redirected to https automatically

• Progress indicator in the report/run endpoint can be disabled To disable, set the progress parameter to 0. More information at our API documentation.

• Filter selectors show which items are present in the current report The service category selector in the services and instances report, and service selector in the instances report will show items not available in the current report grayed out.

• On-demand workflow execution Workflows can now be executed on demand. Also the schedule for a Workflow can be disabled.

• Single workflows can now have multiple schedules

• Workflows can now be scheduled in a specific timezone

• Added the average rate column to the reports details table

• Added the ability to show various totals in reports summary widget Summary widget now has the option to show all totals (previous behaviour) or only the totals for the current search results, or for the current pinned items.

• Added report shortcuts to the dashboard

• COGS, fixed COGS and fixed prices are now evaluated per instance when preparing reports Previously, if a service was created that used any of fixed_price_col, cogs_col or fixed_cogs_col to indicate that the rate in question should be obtained from the usage data for any given day, then the charge engine would use a single value from the specified column(s) and apply that to all instances of the services for the day. Now, each row of usage is individually consulted when preparing reports such that the specific value on that row is used (as is already the case when using 'rate_col' for pass-through rates)

• When extracting XML or JSON values, parse errors no longer cause the USE script to terminate Previously, when using a static parslet to extract XML or JSON values from the contents of a named buffer, if the buffer contained invalid JSON or XML then the USE script failed with an error in the log saying that the contents of the buffer could not be parsed. Now, if a named buffer contains data that is not valid JSON or XML, any attempt to extract a value from it using a static parslet will be expanded to the value EXIVITY_INVALID_XML or EXIVITY_INVALID_JSON.

• Improved performance and lowered memory requirements when running a report Previously, in some circumstances running a report could take longer than expected and consume large amounts of memory in the process. The performance and memory use of the report engine have both been improved.

• Reduced memory and increased performance when preparing reports Previously it was possible for some installations to use large amounts of memory and exhibit unreasonably slow performance when preparing reports. Preparing reports is not intended to be a realtime feature and will always incur some time overhead, but this time should now be significantly reduced in many cases, and the memory required to complete the process will be much less.

• New output format for the /report/run endpoint in the API Due to changes to the charge engine, the output format of the /report/run endpoint in the API has changed. An up-to-date overview of the attributes returned by this endpoint can be found at our API documentation.

• Free formatted ODBC connect strings are now supported in USE This exposes all ODBC driver options to the user, and avoid the requirement of creating manually DSN at the operating system level.

• The 'split' statement now supports discarding unwanted result columns When using the 'split' statement it is now possible to discard all but a selected range of the resulting new columns.

Bug fixes

• Fixed a Transcript crash when deleting a DSET Transcript will no longer crash in certain circumstances when deleting a DSET using the 'delete dset' statement.

• The Transcript 'export' statement now creates a path automatically When exporting data from Transcript, if a relative path is specified as part of the export filename, Transcript will automatically create the path if it does not exist. The path will be created relative to /exported

• Changed the behaviour of some columns in the report tables The optional _per unit charges and per interval charges columns on the report pages represent a fraction of the total charge and as such should be considered a subtotal rather than a rate._

• Allow users to see anonymous roll-up accounts even if they have no access When a user only has access to some children of a parent account, reports will now show the combined usage of those accounts grouped as an unknown account in the reports.

• Fixed a rare bug where incorrect character encoding in the data source could lead to reports not loading

• Currency symbol is no longer shown for quantity graphs

• An issue has been fixed which could lead to empty reports when there actually was report data In some cases, selecting certain combination of filters could lead to reports showing No data while there actually was report data for the current set of filters. This behaviour was observed mainly on the instances report page.

• Usernames are now allowed to contain special characters As a side effect of changing usernames to be case-insensitive, using special characters was no longer permitted since v1.8.1. This restriction is now removed.

• Changed the behaviour of clearing the charge engine caches Clearing the charge engine (Edify) caches unprepares all reports. The button on the About page now reflects this.

• It is now possible to use decimal values for adjustment amounts Previously this was only possible through the API. The GUI has been updated to also support this.

• Changing the date in the invoice report no longer resets the account selection Previously, when changing the date range on the invoice report screen, the current account selection (dropdown inside the invoice page) would automatically select the first account in the list. This has now been fixed to remember the selection when changing the date.

• Exivity now works correctly when installed in a directory containing spaces

• Transcript variables were not properly expanded when using in an import filter

• Export of consolidated invoice now contains data for all accounts Previously, selecting the CSV or Excel export of a consolidated invoice would only export data for the first account on the invoice.

• Fixed crash bug in the 'services' statement When creating services, Transcript will no longer crash if a blank interval or model value is encountered while building the service definitions.

Older release notes can be found here.

Report Caches

Version 2 introduces a breaking change to historical cached data from installations before version 2.0.0. Therefore, when you're upgrading an Exivity version 1 to version 2, all Reports will be automatically Unprepared during upgrade. After completing the upgrade, you will have to browse to your Report Definitions and Prepare each of them for the appropriate date period:

Changes to Transcript