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

The interface allows you to do the following:

• Develop extractors

• Create transformer (ETL) tasks

• Configure report definitions

• Run graphical usage & costs reports

• Run textual usage & costs reports

• Manage users & roles

• General configuration

• White labeling

More features are added on a regular basis.

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

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

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

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

As part of version 2, outdated syntax in your Transformers will be automatically converted to comply with version 2 Transcript syntax. Your version 1 Transformers will be backed up during upgrade into the default backup folder %EXIVITY_HOME_PATH%/system/backup. To manually upgrade any of your version 1 Transformers, you may use the script provided in %EXIVITY_PROGRAM_PATH%/update-transcript-v2.0.0.bat (this script will upgrade any Transformer currently available in the default Transformer folder).

Replacement of Exivity Eternity Service

the service that was previously responsible for scheduling and execution of workflows ("Exivity Eternity Service" which ran eternity.exe) has been replaced by the Aeon component and runs as the "Exivity Scheduling Service". In case the "Exivity Eternity Service" was running under a non-system account, ensure to update the newly registered "Exivity Eternity Service" with the corresponding user service credentials.

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+

• Chromium 66+

• Microsoft Edge 41+ (EdgeHTML 16+)

• Opera 50+

• Mozilla Firefox 65+ (support added in Exivity v2.10.0)

We don't support Apple Safari at the moment due to missing features in this browser.

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 for processing extracted data. Using it you can merge consumption metrics, contract details, customer information, custom metadata, service definitions or any other imported information to produce an enriched and/or normalised result.

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

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

Report

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

Integrate

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

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

Detailed information about the REST API can be found at

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

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

Filters and Reporting Depth

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

Grouping and display of Instances

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

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

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

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

Date Selection

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

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

Report

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

Drill Down, Services & Report Depth

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

1. Moving your mouse over one of the accounts will reveal a toolbar as shown above. For each account, you have the option to click on the Drilldown control in that toolbar. This will do the following:

   1. Descends one level deeper into the report

   2. Updates your view of the data to reflect that deeper level

   3. Sets the 'Parent' filter to the account you selected to drill down on

2. As well as the ability to drill down you can also view the Services associated with an account on any level of your report. Note that this will change your view from the Accounts report to the report.

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

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

Filters and Reporting Depth

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

• Category - to only view certain Service Categories

• Account - to limit your view to all services belonging to a certain account

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

Introduction

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

USE script

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

An introductory overview of the scripting language:

A reference guide for the USE scripting language:

How to parse XML and JSON data

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

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.

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

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

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

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

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

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

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

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

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

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

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

Connecting to your Exivity instance

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

• https://<Your_Public_IP>:8001

The default admin username is admin with password exivity.

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

Next steps

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

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 'Services' > 'Rates' screen and click on the service name for which you want to change the global rate value:

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

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

6. To save your changes, which will also initiate a re-preparation of the applicable Report. click the Save Revision button (see to learn more about report preparation)

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.

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

To install Exivity, you'll need the following:

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 meeting one or more of the above requirements, please get in contact with our support department.

Interactive installation

To install Exivity interactively, execute the provided setup executable. Then follow the instructions 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 and it is recommended to use a local SSD volume which is not the boot drive.

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

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

• Choose an admin username and password

• As the installer completes you will be offered the option to start the Exivity services

Silent Installation

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

<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 straightforward and may be done by installing the new version over the top of the old.

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 as standard with an untrusted self-signed SSL certificate. It is therefore highly recommended to replace the default certificate with an official one, signed by your Certificate Authority. To install a signed certificate, follow this procedure:

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

• 'Common Name (e.g. server FQDN or YOUR name) []'

1. The generated CSR file should be sent 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, to the directory %EXIVITY_PROGRAM_PATH%\server\nginx\conf. This should overwrite the existing key and crt files

2. Restart the Exivity Web Service Windows service to activate your signed certificate.

Configuring a separate web server portal

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

To achieve this, first install Exivity on 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;
        proxy_set_header Host $host;
        proxy_set_header X-Forwarded-Host $host;
        proxy_set_header X-Forwarded-Port 443;
    }

    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 name of the User Portal.

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

Using an Internet proxy when extracting data

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

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

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

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

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

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

Increase limit of filesize upload in the API

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

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

Introduction

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

1. Create Exivity vCenter user

2. Configure an Extractor

3. Configure a Transformer

4. Create your Report

Exivity supports Out of the Box integration with vCenter version 6.5 and higher. For version before 6.5, please contact [email protected].

Create Exivity vCenter user

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

• In your vCenter Configuration Manager go to Configuration > Local Users and Groups > Users.

• Create a new User, insert username and password.

• Take note of the username and password, they will be used later on to configure Exivity

• Click on Create.

• Under Users and Groups, click Add, select the previously created user.

• On the Select Users and Groups dialog, click Add, and then click OK.

• Add a Reader role in the dropdown.

• Finally click on OK.

Configure an Extractor

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. To create a new Extractor based off the vCenter template follow these steps:

• Provide a name for the Extractor in the Name field above

• Pick vCenter 6.5 (VM Inventory REST API) template from the list

• Click the Create button.

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

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

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

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

Configure a Transformer

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

Create a Report

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

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

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.

Introduction

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

1. Create an Exivity Enterprise Application in your Azure AD for authentication

2. Configure a rate card lookup file

3. Configure an Extractor

4. Configure a Transformer

5. Create your Report

Creating an Enterprise Application

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

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

Create a rate card

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

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

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

Configure Extractor

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

• Pick Azure_Stack_Extractor_(App+Secret) from the list

• Provide a name for the Extractor in the name field

• Click the Create button.

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

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

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

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

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

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

The Extractor requires two parameters in yyyyMMdd format:

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

• to_date should be the date immediately following from_date

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

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

Configure Transformer

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

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.

Pre-requisites

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

Please note that we also need the following requisites:

• S3 bucket where the CUR reports are placed

• The AWS region of the S3 bucket

• The path where the reports are placed inside the S3 bucket

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

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

Obtaining the API Credentials

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

• Go to IAM - Policies and click on Create policy.

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

• In the next screen, type the name of your bucket followed by /* in Bucket name (example: billing-bucket/*) and click on Add.

• Click on Review Policy.

• Give a Name and a Description to the policy and finally click on Create policy.

Now, we will attach the policy to a user:

• Go to IAM – Users – Add user.

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

• Click on Attach existing Policies directly and filter by the name of the policy you just created.

• Select the policy and click on Next:Tags.

• Add a Key-Value pair for the Tags and click on Next:Review.

• Click on Create User.

• In the next screen click on Download .csv we will use this credentials to populate our Exivity Extractor in the next section.

• Finally click on Close.

Configure Extractor

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

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

• Bucket: Type the name of your S3 CUR bucket.

• AWS region: Name of your S3 CUR bucket region code you can find them here (column Region).

• Access key: You can find it in the csv that you have downloaded in the previous section.

• Secret key: You can find it in the csv that you have downloaded in the previous section.

• Cur report: Type the name of your CUR report.

• Bucket Directory: Type the path that you got in the previous section (if there is more than one folder separate them with /).

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

The Extractor requires one parameters in yyyMMdd format:

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

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

Configure Transformer

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

Create Report

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

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.

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

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

Creating Extractors

To create a new USE Extractor, follow these steps:

1. From the menu on the left, select "Data pipelines" > '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 Extractors that you want to change:

1. After you select your USE Extractor, you can change its variable values at the 'Variables' tab.

2. At the "Editor" tab you can make more advanced changes or delete the original USE-script. Such as:

   • changing existing API calls

   • changing csv output format

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.

Introduction

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

1. Create Exivity vCloud user (vCloud < 9.1)

2. Create Exivity vCloud user (vCloud >= 9.1)

3. Configure an Extractor

4. Configure a Transformer

5. Create your Report

Create Exivity vCloud user (vCloud < 9.1)

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

• Click the Administration tab and click Users in the left panel.

• Click New, fill the required details.

• Note the username and password.

• Finally, click OK.

Create Exivity vCloud user (vCloud >= 9.1)

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

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

• From the main menu, select Administration.

• In the left panel, under Access Control, click Roles

• Click New.

• Enter a name and, optionally, a description for the new role.

• Select the rights that you want to associate with the role. Exivity needs all the View Rights in the different tabs and also Perform Administrator Queries under the General tab.

• Click Save.

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

• On the VMware Cloud Service toolbar, click the VMware Cloud Services icon and select Identity & Access Management.

• Click Add Users.

• On the Active Users tab, fill the details of the user you want to add to the system organization.

• In the Role in organization text box, assign the role previously created.

Configure an Extractor

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

• Pick vCloud_Extractor_AdminVM from the list

• Provide a name for the Extractor in the name field

• Click the Create button.

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

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

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

Configure a Transformer

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

Create a Report

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

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

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

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

Create a budget

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

Global options

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

• Interval: determine whether the budget is applied Monthly, Quarterly or Yearly

• Apply to: a configured budget is by default applied to the total Charge of the configured Interval. It is however also possible to create a budget which is applied to the Cost of Cogs (COGS) instead

Revisions

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

• Revision start date: the start Month, Quarter or Year for this budget revision

• Filter by: typically a budget is applied to a single or multiple Accounts. It is however possible to add additional filtering on Service or Service Category. By applying Service based filtering, it is possible to limit the scope for a configured budget

Accounts

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

• Account selection: it is required to select an Account from any level in your Report Definition. It is the possible to set a budget value (i.e. $100000) in the grey box next to the Account.

  • The same applies to any Child Accounts for which you may set/overwrite a budget. You may add a Child Account to the list, by click the green button left of the account name

    • NOTE: a child account may also be excluded from a budget, by clicking the Exclude checkbox right of the name of the Account

• Remainder: using the Remainder drop-down it is possible to control the distribution of the budget towards child accounts. The two options to pick by default are:

  • even: each child account will get an even amount of budget. Example :Consider a top level account 'ACME Corp' with a monthly budget set of $100.000. When 'ACME Corp' has 10 child 'Business Unit' accounts , each of these 'Business Units' will get an even amount of the budget: In this case this will be $10.000.

  • shared: when the distribution of the remainer is set to shared, the consumption of child accounts is ignored. As long as the total spendings of all child accounts does not go beyond the configured budget. Example: Consider a top level account 'ACME Corp' with a monthly budget set of $100.000. When 'ACME Corp' has 10 child 'Business Unit' accounts , each of these 'Business Units' combined should not use more then $100.000.

  • none: the option to not distribute any remainders is only applicable when overriding the budget percentage for each child account. This means it is required set a distinct budget percentage manually for each child account.

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

Changing a budget

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

Changing an existing budget revision

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

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

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

Adding a new budget revision

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

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 of a Service

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

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

1. The description or friendly name for this service

2. The unique key value of this service (see )

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

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 Services > Overview menu and click the white Edit button at the top of service list. The system will warn you that any changes made to existing service, may require you to re-Prepare the currently selected Report , found at Data pipelines > Reports.

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 . 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 case you have made any changes.

Creating a Service

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

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

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

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

Apply Metadata to Services

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

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

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

Syntax

clear http_headers

Details

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

Example

The discard statement is used to delete a named .

Syntax

discard{buffer_name}

Details

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

Example

The unzip statement is used to unzip the data in a .

Syntax

unzip{buffer_name}

Details

The unzip statement will extract a single file from a zip archive stored in a named buffer. In order for this to succeed, the buffer must have been previously populated using the statement, and the data within the buffer must be a valid ZIP file.

A warning will be logged, the buffer left intact and the script will continue to execute if any of the following conditions arise:

• The buffer is empty or does not contain a valid ZIP archive

• The ZIP archive is damaged or otherwise corrupted

• More than 1 file is present within the archive

After the unzip statement completes, the buffer will contain the unzipped data (the original ZIP archive is discarded during this process).

The filename of the unpacked file is also discarded, as the resulting data is stored in the buffer and can subsequently be saved using an explicit filename as shown in the example below.

Example

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

Syntax

escape quotes invarName|{bufferName}[usingescape_char]

Details

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

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

Example

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

The following script:

will produce the following output:

Overview

The lowercase statement is used to modify the name of, and/or the values in, a column such that any upper case characters are replaced with their lower case equivalent.

Syntax

lowercase heading|values [and heading|values] in columnColName1 [... ColNameN]

Details

The heading and values keywords determine the scope of the processing to be applied. The columns named in ColName1 ... ColNameN may or may not be fully qualified. If a column name is not fully qualified then the default DSET will be assumed.

Only upper case characters in a column name or value are converted. Numbers, punctuation and other symbols are ignored. Any blank values in a column are ignored.

Examples

Overview

The finish statement creates a (RDF) from a DSET. The RDF can subsequently be used by the reporting engine.

Syntax

finish[dset.id]

Details

The finish statement is used to create an RDF from a DSET. Only a single DSET can be used to create an RDF, but multiple finish statements may be used within the same task file. If there is no dset.id parameter then the default DSET will be used.

The RDF created by finish will be saved as <BaseDir>\system\report\<yyyy>\<MM>\<dd>_source.alias.rdf

where:

• <yyyy> is the 4-digit year

• <MM> is the 2-digit month

• <dd> is the 2-digit day

• source.alias are the tags which form the DSET ID

Any existing RDF with the same name will be overwritten.

Examples

Create a Reporting Database file for the default DSET: finish

Create a Reporting Database file for the DSET Azure.usage finish Azure.usage

When developing your , it is possible to view intermediate results by utilizing the preview functionality in the Transformer editor. To use the Transformer Preview, place your cursor at a line number in your script uptill where the Transformer should execute. In the example below, the cursor is placed at line numer 27:

Make sure to select a Preview Date for which data is available in the system. Then execute the preview by clicking the Update and Preview button.

By default, the preview will load the first 1000 records for each DSET being imported. In case you want to increase / decrease this amount, you can do so by adjusting the value in the Output Limit field:

Additionally, the preview will by default return the contents of the Default . In case you prefer to preview a static DSET, you can control this by selecting a custom DSET from the drop down selector:

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

The return statement is used to exit a subroutine at an arbitrary point and return to the calling location

Syntax

return

Details

A subroutine will automatically return to the location it was called from when the end of its body is reached. However it may be desirable to explicitly exit the subroutine at some other point in which case the return statement is used.

Example

#
# Download two files into named buffers
# using a subroutine to do so
#
gosub getfile(data1, "http://intranet/datadump1.json")
gosub getfile(data2, "http://someotherserver/anotherfile.xml")

# (Script to do something with the data goes here)

#
# Argument 1: the name of the buffer to store the data
# Argument 2: the URL of the file to download
#
subroutine getfile {
    if (${SUBARG.COUNT} != 2) {
        print "Error: This subroutine requires two arguments
        return
    } 

    buffer ${SUBARG_1} = http GET "${SUBARG_2}"
    # There is an implicit 'return' here
}

The print statement is used to display text to standard output while a USE script is executing.

Syntax

print [-n]word|{buffer_name} [... word|{buffer_name]

Details

The print statement enables user-defined output to be generated during the execution of a USE script. When retrieving data from external sources it may take some time for a lengthy series of operations to complete, so one use of the print statement is to provide periodic status updates during this time.

The print statement will process as many arguments as it is given, but at least one argument is required. If the first argument is -n then no newline will be output after the last argument has been echoed to standard output, else a newline is output after the last argument.

Arguments that are normal words will be sent to standard output followed by a space. Arguments referencing a named buffer will result in the contents of the buffer being displayed.

Note that print will stop output of data from a named buffer as soon as a NUL (ASCII value 0) character is encountered

Example

var server = "https://my_json_server.com"
print Obtaining token from server
buffer response = http GET ${server}/generatetoken        
print Token received:
print {response}

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

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

The exit_loop statement will terminate the current loop.

Syntax

exit_loop

Details

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

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

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

The gunzip statement is used to inflate a GZIP file

Syntax

gunzip filename as filename

gunzip {bufferName} as filename

Details

The gunzip statement can be used to extract the contents of a GZIP archive containing a single file. The GZIP archive may be a file on disk or may be the contents of a named buffer.

It is not possible to inflate GZIP data directly in memory, but the same effect can be achieved by extracting GZIP data in a named buffer to disk, and then loading the extracted data back into the named buffer as shown in the example below.

Example

# Download an archive and extract it into a named buffer
buffer archivedata = http GET http://server/archived.csv.gz
gunzip {archivedata} as system/extracted/extracted.csv
buffer archivedata = FILE system/extracted/extracted.csv

# Download an archive and extract it to disk, automatically deriving the
# output filename from the input filename based on the .gz extension
var save_path = system/extracted
var archivefile = extracted.csv.gz
set http save_file ${save_path}/${archivefile}

match csv_name "(.*)\.gz$" ${filename}
if (${csv_name.STATUS} != MATCH) {
    print WARNING: Downloaded file does not end in .gz and will not be extracted
} else {
    gunzip "${save_path}/${archivefile}" as "${save_path}/${csv_name.RESULT}"
    print Extracted file: "${save_path}/${csv_name.RESULT}"
}

Overview

The capitalise (the spelling capitalize is also supported) statement is used to modify the name of a column and/or the values in a column such that the first character is a capital letter and the remaining characters are lower case.

Syntax

capitalise values|heading [and values|heading] in column|columnsColName1 [... ColNameN]

Details

The heading and values keywords refer to the name of an existing column and the values in each row for that column respectively.

Only the first character in the column name or value is converted to upper case. If this character is not a letter, then the statement will have no effect on it. For example applying the statement:

capitalise heading in column _vmname

will have no effect on the column name as the first character is an underscore. However, applying the same statement to a column called _VMName would result in a new name of _vmname as after attempting to make the first character a capital (which in the case of the underscore has no effect), the remaining characters are converted to lower case.

Any number of column names may be specified, and any of these may or may not be fully qualified. When applied to values in a column, blank values are ignored.

Examples

capitalise heading in column servicegroup

capitalize heading and values in columns servicegroup Azure.usage.resourcepool chargeinterval

capitalise values in column Region

The terminate statement will exit the USE script immediately.

Syntax

terminate [with error]

Details

Normally a USE script will finish execution when an error is encountered or when the end of the script file is reached, whichever comes first.

When the terminate statement is encountered, the script will finish at that point. No statements after the terminate statement will be executed.

By default, the script will exit with a success status, however it may be useful to exit deliberately when an error such as an invalid or unexpected response from an HTTP session is detected. Adding the keywords with error to the statement will cause it to exit with an error status.

Example

set http_savefile = extracted/serverdata.txt
buffer serverdata = http GET "https://server.com/uri"
if (${HTTP_STATUS_CODE} != 200) {
    print Got HTTP status ${HTTP_STATUS_CODE}, expected a status of 200
    print The server response was:
    print {serverdata} 
    terminate with error
} else {
    print Received data from server successfully
}

Overview

The default statement is used to explicitly define the default DSET to use when specifying a column name as an argument in subsequent statements.

Syntax

default dsetsource.alias

Details

Given that multiple DSETS can be loaded at once, it is necessary to specify which DSET to operate on when performing actions such as creating a new column with create. A column name in a Transcript statement is assumed as belonging to the default DSET unless it is a fully qualified column name.

If there is no default statement in the Transcript, then the first CSV file imported via the import statement will automatically be designated as the default DSET.

The default statement can be used multiple times throughout a Transcript, in which case the default DSET will be whichever was specified by the last default statement executed.

Lastly, when executing a finish statement, unless otherwise specified, the default DSET will be used to populate the reporting database created as a result.

Examples

Set custom.datafile as the default DSET:

default dset custom.datafile

The gosub keyword is used to run a named subroutine

Syntax

gosub subroutineName([argument1, ... argumentN])

gosub subroutineName (argument1,
      argument2,
      argument3,
      )

Details

The subroutineName provided to the gosub statement must be that of a subroutine defined elsewhere in the script using the subroutine statement.

If any argument contains white-space or a comma then it must be quoted:

gosub getfile("directory with spaces/filename.txt")

The opening bracket after subroutineName may or may not be preceded with a space:

gosub getfile ("filename.txt")

To call a subroutine with no parameters, use empty brackets:

gosub dosomething()

Example

Please refer to the example in the documentation for the subroutine statement

Pre-requisites

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

Setting up BigQuery

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

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

1. Go to the service page.

2. Select the project that will contain your dataset in the project drop down.

3. Click CREATE DATASET.

   1. Enter a Dataset ID

   2. Select Data location (region)

   3. Set Default table expiration to Never

   4. Set Encryption to Google-managed key

4. Finally, click Create dataset

5. Note the Project, BigQueryProject and Table, these parameters will be used by Exivity on a later stage.

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

1. Sign in to the , select Organization and main Billing Account.

2. Go to the Billling Export tab.

3. Click Edit Settings to enable the export.

   1. Select the project where you previously created the BigQuery dataset.

   2. Select the specific dataset from the Billing export dataset list.

   3. Click Save.

Creating Exivity Service Account

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

Configuring the Extractor

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

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

• Hostname: Default endpoint for Google BigQuery with version

• Private: Provide the RSA private key PEM format

• Email: Mail address / service account associated with the private key, obtained in the Creating Exivity Service Account section.

• Project: Main GCP Project name.

• BigQuery project: GCP BigQuery Billing Project, obtained in the Setting up BigQuery section.

• BigQuery table: GCP BigQuery Billing Table, obtained in the Setting up BigQuery section.

Finally, click on Update.

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

The Extractor requires two parameters in yyyMMdd format:

• from_date is the date for which you wish to start collecting consumption data.

• to_date is the end date for which you wish to collect consumption data.

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

Configure a Transformer

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

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

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

Create your Report

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

Select the column(s) by which you would like to break down the costs (you can start with only project_name as a test). 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.

The pause statement is used to suspend execution of a USE script for a specified time.

Syntax

pausedelaytime

Details

The delaytime parameter is the number of milliseconds to wait before continuing. A value of 0 is allowed, in which case no delay will occur.

The pause statement may be useful in cases where an external data source imposes some form of rate limiting on the number of queries that can be serviced in a given time-frame, or to slow down execution at critical points when debugging a long or complex script.

Example

Overview

The append statement is used to append one to the end of another.

Syntax

appendsource_dset.idtodestination_dset.id

Details

If the source DSET has any column names not present in the destination DSET then additional columns are automatically created in the destination DSET. These additional columns will contain blank values by default.

If one or more column names are present in both DSETs then the columns copied from the source DSET may be re-ordered into the same order as that used by the destination DSET.

At the end of the operation, the destination DSET will contain all the data from both DSETs, and the source DSET is unchanged.

Example

Given the following DSETs:

DSET ID: example.data

DSET ID: example2.data

The statement append example2.data to example.data will result in the following destination DSET (example.data):

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

Syntax

encode base16|base64varName|{buffer_name}

Details

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

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

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

Example

The following script ...

... produces the following output:

While executing a USE script, various messages are written to a logfile. The loglevel option determines the amount of detail recorded in that logfile.

Syntax

loglevelloglevel

Details

The table below shows the valid values for the loglevel argument. Either the numeric level or the label can be specified. If the label is used then it must be specified in CAPITAL LETTERS.

The log levels are cumulative, in that higher log-level values include lower level messages. For example a level of INFO will cause FATAL, ERROR, WARN and INFO level messages to be written to the log.

The loglevel statement takes immediate effect and may be used multiple times within a USE script in order to increase or decrease the logging level at any time.

The json statement is used to format JSON in a .

Syntax

json format{buffername}

Details

In many cases an API or other external source will return JSON in a densely packed format which is not easy for the human eye to read. The json statement is used to re-format JSON data that has been previously loaded into a named buffer (via the statement) into a form that is friendlier to human eyes.

Example

Given the following single packed line of JSON in a named buffer called myJSON:

The following USE script fragment:

will result in the following output:

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

Syntax

basenamevarName

basenamestringasvarName

Details

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

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

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

Examples

Example 1

The following script ...

... will produce the following output:

Example 2

The following script ...

... will produce the following output:

The if statement is used to conditionally execute one or more statements. In conjunction with an optional else statement it can cause one or other of two blocks of statements to be executed depending on whether an expression is true or false.

Syntax

if(expression){

} [else {

}]

Details

If the condition evaluates to true, then the first block of statements is executed, and the second block (if present) is skipped over. If the condition evaluates to false then the first block of statements is skipped and the second block (if present) is executed.

The opening { character at the start of each block may be placed on a line of its own if preferred but the closing } must be on a line of its own.

Multiple conditions can be used in a single expression and combined with the boolean operators && or || (for AND and OR respectively) so long as each condition is enclosed in braces. For example:

Example

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

will produce the following output:

The save statement is used to write the contents of a to disk.

Syntax

save{buffer_name}asfilename

Details

The save statement will write the contents of a to filename. As well as providing a means of direct-to-disk downloading this can be useful for retrieving server responses and capturing them for later examination, whether it be for analysis, debugging or audit purposes.

If the destination file already exists then it will be overwritten.

If the filename argument contains a path component, then any directories not present in the path will be created. If creation of the path destination file is not successful then an error will be logged and the USE script will fail.

The save statement is similar in effect to the option supported by , in that data from a server is written to disk. There is one important distinction however:

• When has been used to specify a file to save, the next request will stream data to the file as it is received from the server

• When a statement is used to capture the server response, and a subsequent save statement is used to write it to disk, all the buffered data will be written to the file immediately

Example

Overview

The convert statement is used to convert values in a column from base-10 to base-16 or vice-versa.

Syntax

convertcolNameto decimal|hex from decimal|hex

Details

When converting values in a column, the following considerations apply:

• Values in the column are replaced with the converted values

• The colName argument must reference an existing column, and may optionally be fully qualified (else the column is assumed to be in the default DSET)

• If any values in the column are not valid numbers, they will be treated as 0

• Blank values are ignored

• The convert statement may be used in the body of a where statement

• If a value in colName contains a partially correct value such as 123xyz then it will be treated as a number up to the first invalid character, in this case resulting in a value of 123.

• The hex digits in the original value can be either upper or lower case

• The hex digits from A-F will be rendered in upper case in the converted output

• The convert statement only supports integer values (floating points will be treated as floored to the nearest integer)

Example

Overview

The copy statement is used to copy rows from one DSET to another

Syntax

copy rows todset.id

move rows todset.id

Details

Both copy and move must be used within the body of a statement. Only rows that match the expression will be copied (or moved).

• The DSET from which rows will be copied or moved is automatically determined from the expression used by the where statement.

• The DSET to which rows will be copied or moved is determined by the dset.id parameter

The destination DSET may or may not exist. If it does not exist then it will be created. If it does exist then the following logic is applied:

• If the destination DSET has more columns than the source DSET then the new rows in the destination DSET will have blank values in the rightmost columns

• If the destination DSET has fewer columns than the source DSET then the destination DSET will be extended with enough new columns to accomodate the new rows. In this case, existing rows in the destination DSET will have blank values in the rightmost columns

If the destination DSET is extended to accomodate the source rows then the new (rightmost) columns will have the same names as the equivalent columns in the source DSET. In the event that this would cause a naming conflict with existing columns in the destination DSET, one or more new columns in the destination DSET will heve a suffix added to their name to ensure uniqueness. This suffix takes the form _N where N is a number starting at 2.

To illustrate this, if the source DSET has columns called subscription,name,address,hostname and the destination DSET has a single column called name then the resulting extended destination DSET would have columns called name,subscription,name_2,address,hostname.

Example

Overview

The calculate statement is used to perform arithmetic operations using literal and column values.

Syntax

calculate columnResultColas source operation source

where source is either of columncolName or valueliteral_value

and operation is one of the characters + - * / % for addition, subtraction, multiplication, division and modulo respectively.

Examples: calculate column ResultCol as column Amount * value 1.2 calculate column Net as column total - column cogs calculate column constant_7 as value 3.5 + value 3.5

Details

The ResultCol parameter is the name of the column that will hold the results. This column may or may not exist (if necessary it will be created automatically).

Both of the two source parameters can specify a literal value, or the name of a column containing the value to use when performing the calculation.

• A literal value is specified using valueN where N is the literal number required

• A column name is specified using columncolName where ColName is the name of the column containing the values required

Additional notes:

• Any blank or non-numeric values in a source column will be treated as 0

• An attempt to divide by zero will result in 0

• When performing a modulo operation, the two source values are rounded to the nearest integer first

• If the result column already exists then if is set to no, only blank cells in the result column will be updated.

Examples

• Add 1.5 to the values in the Rate column:

calculate column Rate as column Rate + value 1.5

• Multiply the values in the Rate column by those in the Quantity column

• Store the result in a new column called Charge

calculate column Charge as column Rate * column Quantity

Overview

The export statement is used to snapshot the data in a DSET and write it to disk as a Dataset.

Syntax

exportsource.aliasasfilename

Details

The exported file will be created under <base_dir>/exported. The filename parameter may include a path as well as the filename to export, so long as it does not contain the substring ".." and is not an absolute path. If the path contains one or more directories that do not exist then they will be created.

Exporting usage

The source.alias argument is the DSET ID to export (see for more information on DSET IDs).

The export statement can be used at any point during this process to save a CSV (conforming to Dataset format) which snapshots the data in the DSET at that moment in time. This can be used for a number of purposes including:

• Examining the state of data part-way through processing for debugging purposes

• Creating custom exports for subsequent import into 3rd part systems

• Producing output CSV files (which may potentially contain merged data from multiple sources) for subsequent processing with another Transcript

Examples

The following Transcript will import a quoted CSV file, add a ProcessedFlag column to it with a value of 1 in each row, and save it back out again without quotes:

Overview

The set statement is used to write a specified value into all the cells in any given column, or to copy values from one column to another.

Syntax

setColNametoValue

setColNameasSrcColName

setColName=Expression

Details

If the keyword word to is used then column ColName will be updated using the constant value specified in Value.

If the word as is used then the values in the column SrcColName will be copied into column ColName.

If = is used then the expression is evaluated and the column values are set to the result. For a complete list of functions that can be used in an expression please refer to the in the article about the statement

The ColName argument must identify an existing column. The modified column will be in the default DSET unless it is a column name.

If the overwrite is disabled, then only cells with blank values will be updated with the new value.

Examples

Set the column called Flag in the default DSET to a value of 0:

Set the column called Flag to be the value in the column status:

Set the column called DiskTier to the 4th character in the existing value of that column

set DiskTier = @SUBSTR([DiskTier],4,1)

Set the column called Flag in the DSET custom.dataset to a value of Pending:

Set the column Flag to free if the rate column contains a value of 0:

Fill any blank values in the column username in the DSET Azure.usage to Unknown :

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

The loop statement executes one or more statements multiple times.

Syntax

looplabel [count][timeouttimelimit]{

 `# Statements`

}

Details

The loop statement will loop indefinitely unless one of three exit conditions cause it to stop. These are as follows:

1. The number of loops specified by the count parameter are completed

2. At least as many milliseconds as are specified by the timelimit parameter elapse

3. An exit_loop statement explicitly exits the loop

In all three cases when the loop exits, execution of the script will continue from the first statement after the closing } marking the end of the loop.

In the event that both count and timelimit parameters are specified, the loop will exit as soon as one or other of the limits have been reached, whichever comes first.

Both the count and timeout parameters are optional. If omitted then the default for both of them will be infinite.

The loop statement will automatically create and update a variable called loop_label.COUNT which can be referenced to determine how many times the loop has executed (as shown in the example below). This variable is not deleted when the loop exits which means that it is possible to know how many times any given loop executed, even after the loop has exited.

Example

loop example 10 {
    This is loop number ${example.COUNT}
}

The loop shown above will result in the following output:

This is loop number 1
This is loop number 2
This is loop number 3
This is loop number 4
This is loop number 5
This is loop number 6
This is loop number 7
This is loop number 8
This is loop number 9
This is loop number 10

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

Syntax

encrypt varname = value_to_be_encrypted

Details

Encrypting one or more variables

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

• All text (including trailing white-space) from the word following the = character up to the end of the line is encrypted

• The encrypted value is base64 encoded

• The original variable value in the USE script is substituted with the result

• The encrypt keyword for that variable is changed to encrypted

• The USE script is overwritten on disk in this new form

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

Using encrypted variables

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

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

Example

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

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

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

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

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

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

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

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

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

As described in the Transcript Documentation, you are free to use your editor of choice to create and modify Transformers. However, the GUI also comes with a built-in Transformers-editor.

Creating Transformers

To create a new Transformer for Transcript, follow these steps:

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

2. To create a new Transformer to normalise and enrich USE Extractor consumption and lookup data, click the 'Create 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. When we create a Transformer for a consolidated bill of various IT resources we would, for example, name it: 'IT Services Consumption'.

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

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 Pipelines" > '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 further investigations or troubleshooting, consult the "Log Viewer" found under the administration dropdown menu top right of the 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.

Overview

The include statement is used to combine Transcript task files together

Syntax

includetaskfile

Details

The include statement is used to combine two or more task files into a single script prior to commencement of execution.

Before executing a task file, Transcript analyses the script and processes all the include statements. Wherever an include statement is encountered, the specified taskfile is read from disk and inserted into the script in place of the include statement itself.

Command placement

As with most statements, include must occupy a single line of its own in the script.

Additional notes

The taskfile argument is treated as relative to <basedir>/system/config/transcript/.

Multiple files may be included in a task file and any included file may include other files but a check is done to ensure that no infinite include loops are generated.

Example

Given the following two files in /system/config/transcript/ ...

# FILE 1: myscript.trs
option loglevel = DEBUGX

# import usage data from csp
import "system\extracted\AzureCSP\${dataDate}_csp_usage.csv" source CSP alias Usage
default dset csp.usage
rename column resource_id to meter_id

include import_customers.trs

option overwrite = no
set resource_subcategory to Generic
create column interval value individually
create mergedcolumn service_name separator " - " from resource_name resource_subcategory region
# etc ...

# FILE 2: import_customers.trs
import "system\extracted\AzureCSP\${dataDate}_csp_customers.csv" source CSP alias Customers
rename column CSP.Customers.ID to customer_id
# END FILE 2

Prior to execution the pre-processor will combine these into the single memory-resident Transcript task:

# FILE 1: myscript.trs
option loglevel = DEBUGX

# import usage data from csp
import "system\extracted\AzureCSP\${dataDate}_csp_usage.csv" source CSP alias Usage
default dset csp.usage
rename column resource_id to meter_id

# FILE 2: import_customers.trs
import "system\extracted\AzureCSP\${dataDate}_csp_customers.csv" source CSP alias Customers
rename column CSP.Customers.ID to customer_id
# END FILE 2

option overwrite = no
set resource_subcategory to Generic
create column interval value individually
create mergedcolumn service_name separator " - " from resource_name resource_subcategory region
# etc ...

Overview

The rename statement is used to change the name of an existing column in a DSET, or to change the source and/or alias of a DSET

Syntax

rename columnOldNametoNewName

rename dsetOldSource.OldAliastoNewSource.NewAlias

Details

Renaming a column

When renaming a column, OldName may be a fully qualified column name. If it is not fully qualified then the column OldName in the default DSET will be renamed.

The NewName argument must not be fully qualified.

Renaming a DSET

When renaming a DSET, NewSource.NewAlias must contain exactly one dot and must not be the name of an existing DSET. The rename takes immediate effect, thus any subsequent reference to the renamed DSET in the transcript task must use its new name.

Any pending service definitions referencing the renamed DSET will automatically be updated with the new name.

Examples

Renaming columns:

# Rename the column 'UnitCost' in the default DSET to 'Cost'
rename column UnitCost to Cost

# Rename the column 'UnitCost' in the DSET 'custom.prices' to 'CustomCost'
rename column custom.prices.UnitCost to CustomCost

Renaming a DSET:

# Usage file has yyyyMMdd in filename
import "system/extracted/AzureJuly/${dataDate}.ccr" source AzureJuly

# The resulting DSET will have an ID of 'AzureJuly.yyyyMMdd'
# where yyyyMMdd is the current dataDate

#
# Any additional processing would be done here
#

# Remove the yyyyMMddfrom the DSET ID
rename dset AzureJuly.${dataDate} to Azure.July

# Create 'Azure.July.rdf'
finish

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

Syntax

generate_jwt keykey component1 [... componentN]asresult

Details

The generate_jwt statement performs the following actions:

• encodes all components as Base64URL

• concatenates all components using a dot separator (.)

• hashes the concatenated result using SHA256

• signs the hash with a provided PEM-encoded key using the RSA algorithm

• encodes the resulting signature as Base64URL

• builds JWT by concatenating the two results using a dot separator (.)

• stores the final result in th variable specified by the result parameter

Example

To acquire a Google Cloud OAuth 2.0 access token:

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

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

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

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

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

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

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

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

Syntax

get_last_day_ofyyyyMMasvarName

Details

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

The statement will take leap years into account.

Example

Overview

The normalise statement is used to update the values in a numerical column such that they are all positive, negative or inverted.

Syntax

normalise columncolNameas positive

normalise columncolNameas negative

normalise columncolNameas invert

normalise columncolNameas standard

Details

The normalise statement processes each value in the column called colName and applies the following logic based on the last argument shown above as follows:

In order to be considered a number, a value in the colName column must start with any of the characters +, -, . or 0 to 9 and may contain a single . character which is interpreted as a decimal point.

Additionally:

• Any numerical value in colName which starts with a +, . or decimal character is considered positive

• Any numerical value in colName which starts with a - character is considered negative

• When using standard the resulting conventional number will be accurate up to 14 decimal places

The normalise statement ignores the setting, as its sole purpose is to modify existing values.

Example

Overview

The replace statement is used to search for, remove, and optionally replace with new values, substrings of the values in a column.

Syntax

replacesubstringincolName[withreplacement]

Details

The replace statement will remove or replace all occurrences of substring in the values of a column. The parameters are as follows:

The colName argument may or may not be , but must reference an existing column.

If the optional replacement string is provided then any occurrences of substring will be substituted with the specified value.

If the replacement string is not provided then all occurrences of substring will be removed from the values in colName.

Example

Given the following sample data:

The following script ...

... will produce the following output data.

Overview

The delete statement is used to delete one or more columns or rows from one or more DSETs.

Syntax

delete columns [except]ColName1 [... ColNameN]

delete blankcolumns

delete rows

delete dsetdset_id

Details

Deleting columns

The list of column names following the initial delete columns statement may be used in one of two ways:

1. The columns listed will be deleted

2. All columns except those listed will be deleted

Which method to use is determined by the presence (or otherwise) of the except keyword:

It is not possible to delete a column from a DSET which only contains a single column. When deleting a column, the memory used to index it, along with the memory used to store the contents of its cells is released. This may be useful in situations where memory is limited.

Deleting unwanted columns early in a Transcript task will also increase the the performance of many subsequent operations performed by the task.

The second keyword may be column instead of columns. Either way, the statement behaves identically.

Deleting blank columns

The delete blankcolumns statement will delete columns that only contain blank values from the default DSET.

Deleting rows

In order to delete rows from a DSET a local filter must be in effect. A local filter is created using the statement.

When used within the body of a statement, delete rows will delete all rows in the DSET associated with the local filter where the condition is true. For more information on conditions, please refer to the article.

Deleting a DSET

The delete dset statement can be used to remove a DSET from memory. This may be useful in cases where a DSET is no longer required, for example after it has been used to enrich another DSET via the statement.

Examples

Delete the column temp from the default DSET and the column Interim from the azure.Usage DSET: delete columns temp azure.Usage.Interim

Delete any columns in the default DSET that have no values in any rows: delete blankcolumns

Delete all rows where the VMID is 1234

Delete the DSET azure.rates delete dset azure.rates

The hash statement is used to generate a base-16 or base-64 encoded hash of data stored in a variable or named buffer.

Syntax

hash sha256 [HMAC [b16|b64]key] target|{target}asresult[b16|b64]

hash md5target|{target}asresult[b16|b64]

Details

The hash statement uses the contents of target as its input and places the final result into result. The SHA256 and MD5 hash algorithms are supported.

If target is surrounded with curly braces like {this} then it is taken to be the name of a memory buffer and the contents of the buffer will be used as input. Otherwise, it is treated as the name of the variable, the value of which will be hashed.

By default the resulting hash is base-16 encoded and the result placed into the variable specified by the result argument.

If the optional HMACkey arguments are provided when the hash type is sha256 then the secret in key will be used to generate an HMAC-SHA-256 result. The optional b64 or b16 argument following the HMAC option indicates that key is base-64 or base-16 encoded. By default, a clear-text key is assumed.