Customize NRQL data

Tip

This lesson is part of a course that teaches you how to build a New Relic application from the ground up. If you haven't already, check out the Overview.

Each lesson in the course builds upon the last, so make sure you've completed the last lesson, Add NrqlQuery components to your nerdlet, before starting this one.

In this series, you're creating a New Relic application, or NR1 app for short, that shows A/B test data in a variety of charts. Most of the charts need to show data that comes from a demo service you spun up at the beginning of this series.

In the last lesson, you supplied real data, which comes from New Relic's database, to the following charts:

Newsletter subscriptions per version
Version A - Page views
Version B - Page views
Total subscriptions per version

Because you have experience querying subscription and page view data, you may feel ready to supply data to even more charts:

Version A - Page views vs. subscriptions
Version B - Page views vs. subscriptions

However, these charts are different than the charts you've supplied data to because they compare data from two different sources: subscription events and pageView events.

In this lesson, you learn how to format data from multiple sources to exist in the same chart.

Experiment with data in the Data explorer

From your New Relic homepage's top navigation menu, go to to the Data explorer.

Here, you can experiment with subscription event and pageView event queries to make sure you fetch the right data in your app.

Select subscription from the Custom events menu.

This queries NRDB for subscription event totals per minute over the last 30 minutes and shows that data in a chart.

Click Dimensions to see a list of the attributes associated with these subscription events.

You can filter and group subscription events using these dimensions. For Version A - Page views vs. subscriptions and Version B - Page views vs. subscriptions, you want to filter your subscription totals by page_version.

Click the NRQL query to navigate to the Query builder.

Here, you can view and manually edit the query to fetch the data you need.

Add a WHERE clause to filter subscription totals by page_version:

SELECT count(*) FROM subscription WHERE page_version = 'a' SINCE 30 MINUTES AGO TIMESERIES

Remove the TIMESERIES clause to fetch a count:

SELECT count(*) FROM subscription WHERE page_version = 'a' SINCE 30 MINUTES AGO

Modify the SINCE clause to see totals over the past week:

SELECT count(*) FROM subscription WHERE page_version = 'a' SINCE 7 DAYS AGO

Click Run and see the data visualized in a billboard chart instead of a line chart.

Version A - Page views vs. subscriptions and Version B - Page views vs. subscriptions need a combined total of four data values:

All-time subscription totals for version A
All-time page view totals for version A
All-time subscription totals for version B
All-time page view totals for version B
Experiment with the Query builder to discover the four queries which pull the right data. In the end, you'll come up with the following four queries:
```
SELECT count(*) FROM subscription WHERE page_version = 'a' SINCE 7 DAYS AGO
SELECT count(*) FROM pageView WHERE page_version = 'a' SINCE 7 DAYS AGO
SELECT count(*) FROM subscription WHERE page_version = 'b' SINCE 7 DAYS AGO
SELECT count(*) FROM pageView WHERE page_version = 'b' SINCE 7 DAYS AGO
```
technical detail
Unlike other structured query languages, NRQL doesn't provide a mechanism for joining data across sources. This is why you have to perform two queries to get subscription event totals and pageView event totals.
Until now, you've provided every chart with a single query. Here, you have to provide two queries per chart. In the remaining steps, you'll learn how to customize NrqlQuery results to merge data from multiple sources.

Merge NRQL from multiple sources

Change to the customize-nrql-data/ab-test directory of the coursework repository:

bash

$cd nru-programmability-course/customize-nrql-data/ab-test

This directory contains the code that we expect your application to have at this point in the course. By navigating to the correct directory at the start of each lesson, you leave your custom code behind, thereby protecting yourself from carrying incorrect code from one lesson to the next.

In your Nerdlet's totals.js file, implement constructor() and the React lifecycle method componentDidMount() in VersionTotals:

import React from 'react';
import {
    HeadingText,
    NrqlQuery,
    TableChart,
} from 'nr1';

export default class VersionTotals extends React.Component {
    constructor() {
        super(...arguments);

        this.state = {
            tableData: {
                metadata: {
                    id: `totals-${this.props.version}`,
                    name: `Version ${this.props.version}`,
                    columns: ['name', 'count'],
                },
                data: [
                    {
                        name: 'Subscriptions',
                        count: 0
                    },
                    {
                        name: 'Page views',
                        count: 0
                    },
                ],
            }
        }
    }

    componentDidMount() {
        NrqlQuery.query({
            accountIds: this.props.accountIds,
            query: `SELECT count(*) FROM subscription WHERE page_version = '${this.props.version}' SINCE 7 DAYS AGO`,
            formatType: NrqlQuery.FORMAT_TYPE.RAW
        }).then(({ data }) => {
            if (data.raw) {
                let tableData = {...this.state.tableData}
                tableData.data[0].count = data.raw.results[0].count
                this.setState({tableData})
            }
        })

        NrqlQuery.query({
            accountIds: this.props.accountIds,
            query: `SELECT count(*) FROM pageView WHERE page_version = '${this.props.version}' SINCE 7 DAYS AGO`,
            formatType: NrqlQuery.FORMAT_TYPE.RAW
        }).then(({ data }) => {
            if (data.raw) {
                let tableData = {...this.state.tableData}
                tableData.data[1].count = data.raw.results[0].count
                this.setState({tableData})
            }

        })
    }

    render() {
        return <div>
            <HeadingText className="chartHeader">
                Version {this.props.version.toUpperCase()} - Page views vs. subscriptions
            </HeadingText>
            <TableChart data={[this.state.tableData]} fullWidth />
        </div>
    }
}

Important

Make sure you replace <YOUR NEW RELIC ACCOUNT ID> with your actual New Relic account ID.

Here, you initialize your state in the component's constructor. The state has a single value, tableData, which holds series data and metadata for the TableChart. In componentDidMount(), you query the data that populates the data in this state.

componentDidMount() is a React lifecycle method that is called when a component is mounted in the component tree. You use this method, instead of the constructor, to query data because your logic, which requests data from New Relic, introduces side effects and sets state values, neither of which you should do in the constructor.

In componentDidMount(), you use NrqlQuery in a new way. First, you specified the formatType to NrqlQuery.FORMAT_TYPE.RAW because you're going to modify the data instead of supplying it directly to a chart Before, you used its JSX form in the render() method of a chart. Second, you called its query() method and handled the results in a then() callback, which logs them to the console.

In your Nerdlet's index.js file, create a constant called ACCOUNT_ID and pass it to VersionTotals:

import React from 'react';
import { ChartGroup, Grid, GridItem } from 'nr1';
import EndTestSection from './end-test';
import NewsletterSignups from './newsletter-signups';
import PastTests from './past-tests';
import TotalCancellations from './total-cancellations';
import TotalSubscriptions from './total-subscriptions';
import VersionDescription from './description';
import VersionPageViews from './page-views';
import VersionTotals from './totals';

const ACCOUNT_ID = 123456  // <YOUR-ACCOUNT-ID>
const VERSION_A_DESCRIPTION = 'The newsletter signup message says, "Sign up for our newsletter"'
const VERSION_B_DESCRIPTION = 'The newsletter signup message says, "Sign up for our newsletter and get a free shirt!"'

export default class AbTestNerdletNerdlet extends React.Component {
    render() {
        return <div>
            <Grid className="wrapper">
                <GridItem columnSpan={6}>
                    <VersionDescription
                        description={VERSION_A_DESCRIPTION}
                        version="A"
                    />
                </GridItem>
                <GridItem columnSpan={6}>
                    <VersionDescription
                        description={VERSION_B_DESCRIPTION}
                        version="B"
                    />
                </GridItem>
                <GridItem columnSpan={12}><hr /></GridItem>
                <GridItem columnSpan={12}><NewsletterSignups /></GridItem>
                <GridItem columnSpan={6}><TotalSubscriptions /></GridItem>
                <GridItem columnSpan={6}><TotalCancellations /></GridItem>
                <GridItem columnSpan={6}>
                    <VersionTotals version='a' accountId={ACCOUNT_ID} />
                </GridItem>
                <GridItem columnSpan={6}>
                    <VersionTotals version='b' accountId={ACCOUNT_ID} />
                </GridItem>
                <ChartGroup>
                    <GridItem columnSpan={6}>
                        <VersionPageViews version='a' />
                    </GridItem>
                    <GridItem columnSpan={6}>
                        <VersionPageViews version='b' />
                    </GridItem>
                </ChartGroup>
                <GridItem columnSpan={12}><EndTestSection /></GridItem>
                <GridItem columnSpan={12}><PastTests /></GridItem>
            </Grid>
        </div>
    }
}

Important

Make sure you replace <YOUR NEW RELIC ACCOUNT ID> with your actual New Relic account ID.

Navigate to the root of your Nerdpack at nru-programmability-course/customize-nrql-data/ab-test.

Generate a new UUID for your Nerdpack:

bash

$nr1 nerdpack:uuid -gf

Because you cloned the coursework repository that contained an existing Nerdpack, you need to generate your own unique identifier. This UUID maps your Nerdpack to your New Relic account.

Serve your application locally:

bash

$nr1 nerdpack:serve

Navigate to your app in New Relic.

To view your logs, which are useful for uncovering errors in your application, turn on developer tools in your favorite browser and navigate to the Console:

FireFox instructions
Safari instructions
Chrome instructions
With your app on-screen, notice the NRQL data in the console.
This console log tells you that you can find the data you're looking for, namely the subscription or pageView count, at data.raw.results[0].count. The next step is to store that count in state.tableData.
Tip
If something doesn't work, use these debug tools to try to identify the problem.
Make sure you:
- Copied the code correctly from the lesson
- Generated a new UUID
- Replaced all instances of <YOUR NEW RELIC ACCOUNT ID> in your project with your actual New Relic account ID

In totals.js, store the query data in state:

import React from 'react';
import {
    HeadingText,
    NrqlQuery,
    TableChart,
} from 'nr1';

export default class VersionTotals extends React.Component {
    constructor() {
        super(...arguments);

        this.state = {
            tableData: {
                metadata: {
                    id: `totals-${this.props.version}`,
                    name: `Version ${this.props.version}`,
                    columns: ['name', 'count'],
                },
                data: [
                    {
                        name: 'Subscriptions',
                        count: 0
                    },
                    {
                        name: 'Page views',
                        count: 0
                    },
                ],
            }
        }
    }

    componentDidMount() {
        NrqlQuery.query({
            accountIds: this.props.accountIds,
            query: `SELECT count(*) FROM subscription WHERE page_version = '${this.props.version}' SINCE 7 DAYS AGO`,
            formatType: NrqlQuery.FORMAT_TYPE.RAW
        }).then(({ data }) => {
            if (data.raw) {
                let tableData = {...this.state.tableData}
                tableData.data[0].count = data.raw.results[0].count
                this.setState({tableData})
            }
        })

        NrqlQuery.query({
            accountIds: this.props.accountIds,
            query: `SELECT count(*) FROM pageView WHERE page_version = '${this.props.version}' SINCE 7 DAYS AGO`,
            formatType: NrqlQuery.FORMAT_TYPE.RAW
        }).then(({ data }) => {
            if (data.raw) {
                let tableData = {...this.state.tableData}
                tableData.data[1].count = data.raw.results[0].count
                this.setState({tableData})
            }

        })
    }

    render() {
        return <div>
            <HeadingText className="chartHeader">
                Version {this.props.version.toUpperCase()} - Page views vs. subscriptions
            </HeadingText>
            <TableChart data={[this.state.tableData]} fullWidth />
        </div>
    }
}

Pay close attention to the data you're accessing. The array indices are particularly important to get right. The subscription data should go to state.tableData.data[0].count because in tableData, it is the first element in the data array:

data: [
    {
        name: 'Subscriptions',
        count: 0
    },
    {
        name: 'Page views',
        count: 0
    },
],

By similar logic, page view data should go to state.tableData.data[1].count.

With your Nerdpack served locally, view your application to see your charts serving real data.

Tip

If something doesn't work, use your browser's debug tools to try to identify the problem.

Make sure you:

Copied the code correctly from the lesson
Generated a new UUID
Replaced all instances of <YOUR NEW RELIC ACCOUNT ID> in your project with your actual New Relic account ID

In this lesson, you learned how to use NrqlQuery.query() to fetch data from multiple sources and stitch them together into data your chart can use. Notice that there are still two charts in your NR1 application that use mock data:

Total unsubscriptions per version
Past tests

Unfortunately, your demo application doesn't create custom New Relic events when a user unsubscribes from your newsletter or you end an A/B test. In the next lesson, you'll learn how to use NerdGraph and NerdStorage to populate Past tests.

Course

This lesson is part of a course that teaches you how to build a New Relic application from the ground up. Continue on to the next lesson: Access NerdStorage from your nerdlet.

Tip

Experiment with data in the Data explorer .css-21sua1{background:none;border:none;width:0;padding:0;}

technical detail

Merge NRQL from multiple sources

Important

Important

Tip

Tip

Course

Experiment with data in the Data explorer