Pivot Chart
  • 05 Dec 2024
  • 9 Minutes to read
  • Dark
    Light
  • PDF

Pivot Chart

  • Dark
    Light
  • PDF

Article summary

The Pivot chart provides flexible data segmentation and presentation to support complex data analysis. Use the Pivot chart to segment asset fields from different points of view by measuring the asset field values on a desired asset field segmentation and create a visualization with multiple chart representation options such as bar charts, line charts, pie charts, and more.

Configure the Pivot chart by selecting options in the various configuration sections. The available options in each section reflect the configured settings in previous sections.

Work through the following sections to configure a Pivot chart:

  1. General
  2. Visualization
  3. Data
  4. Dimensions
  5. Metrics
  6. Calculation
  7. Presentation

For clarity, the following sections demonstrate the creation of a simple Pivot chart, SaaS Application Activity.

PivotChart - SaaSAppsChart.png

1. General

Use General settings to define the name of the chart, add an optional description, and set the type of chart widget.

  • Chart title - Enter a descriptive title for the chart.
  • Description - Enter an optional description about what the chart shows, the data and/or query it uses, etc.
  • Widget - Select Pivot Chart.

In this example, the chart title is SaaS Application Activity. There is an appropriate description and Pivot Chart is selected as the widget.

PivotChart - General.png

2. Visualization

The Pivot chart allows you to select from a range of chart types.

Select the chart type that best represents the data you want to see:

  • Bar chart - A standard bar chart.
  • Pie chart - A standard pie chart.
  • Stacked bar chart - Displays bars divided into segments to represent different asset groups or other segmentation.
  • Line chart - A standard line chart.
  • Matrix chart - A standard Matrix table.
Note:
The selected chart type determines the options available in the other configuration sections.

In the example above, Stacked is selected as the chart visualization and it segments the data by a particular field. A total of all found assets appears at the end of the bar, while each stacked section of the bar represents a segment of data.

The following sections will describe how to select the various data, metrics, and calculations that will determine what data to display and how.

VisualizationIcons

3. Data

Use the Data settings to select the query that determines the data that is displayed on the chart.

PivotChart - Data2.png

  • Under Module, select an asset module and a query. You can also click + Add Query at the bottom of the list to create a new query. See Creating Queries with the Query Wizard for more about creating queries.

  • Under Schema, select whether to use current or historical data in the chart:

    • Latest data - Use data from the latest fetch. Aggregates data by the asset Date field.
    • Data over time (snapshots) - Use data from historical snapshots.
  • Show results for previous date - Only available when Latest data is selected. Toggle on to choose a specific fixed date or a date relative to the current date. See Viewing Query Results from a Historical Date.

For this example, SaaS Applications is selected as the module and the query is left empty to include all SaaS applications. This will return data about all SaaS applications. In addition, under Schema, Latest data is selected to use data from the most recent fetch.

4. Dimensions

Select the data segmentation to be represented on the axis. Data from the query in the Data section will be segmented according to the field selected here.

Under Axis, select an adapter and field.

PivotChart - Dimension.png

  • Click the filter icon FilterIcon-BlackOutline.png to filter the data returned by the selected query. When a filter is applied, a red dot appears on the filter icon. To clear the filter, click the icon, and then Clear and Save.

  • Click the edit icon PencilEditIcon.png to assign a custom name to the axis. This is useful when you want a more representative name than the field name itself. When a custom name is applied, a red dot appears on the pencil icon. To clear the custom name, click the icon, and then Clear and Save.
    For Date fields, this functionality also allows for editing how the date is aggregated and displayed. For more information see Editing Date Fields below.

  • Click the add icon Plus-button-sm to add another axis dimension. If the selected chart type doesn't support multiple dimensions, this will be disabled.

NOTE

You can select up to 1 dimension column and 3 dimension rows. When you select a dimension column, you can select up to 2 dimension rows.

For this example, the field Name is selected. This means that data will be segmented by SaaS application name and and the axis label adjacent to each bar will be the name of the corresponding SaaS application name. The chart below shows axis labels for the SaaS applications: Zoom, ServiceNow, Okta, and Azure.

PivotChart - DataExampleChartAxisLabels.png

The values represented by the bars and that appear to the right of them are determined by the next two settings, Metrics and Calculation.

Editing Date Fields

You can edit the way date fields are aggregated and displayed. In addition to the option to edit the field's display name (available for all fields), you can also select an aggregation and format.

To edit a date field

  1. When a field that displays a date is selected, click the edit icon PencilEditIcon.png.

  2. (Optional) In the Field Display Name field, enter a new name to display for the field.

  3. Select how you want this date aggregated.

    • Hour - The hour of the event. For example, 12:00 PM.
    • Hour in a day - The one hour timeframe that the event occurred in. For example, 12:00 - 12:59 PM.
    • Day - The date of the event. For example, Jan 1, 2024.
    • Day in a week - The day of the week that the event occurred in. For example, Monday.
    • Week - The week that the event occurred in. For example, Jan 1-7, 2024.
    • Month - The month that the event occurred in. For example, Jan 2024.
    • Quarter - The quarter that the event occurred in. For example, Q1 2024.
    • Year - The quarter that the event occurred in. For example, 2024.
      DateFieldAggregation
  4. Select the format in which you want the date to be displayed.

  5. Click Apply.
    EditDateField

5. Metrics

Select the values within the Dimensions selected above that you want to count or total in the chart.

  • Select an adapter and a field. Some chart types allow you to select multiple measures.

MeasuresImage

  • Select a calculation function for the measure. For more information, see Calculation.

  • Specify a color for the measure to be displayed in.
    This functionality is supported in Bar, Stacked Bar, and Matrix chart visualizations and the option is only displayed when the Set Threshold Color toggle is set to Off.

  • Click the filter icon FilterIcon-BlackOutline.png to filter the data returned by the selected query. When a filter is applied, a red dot appears on the filter icon. To clear the filter, click the icon, and then Clear and Save.

  • Click the edit icon PencilEditIcon.png to assign a custom name to the measure. This is useful when you want a more representative name than the field name itself. When a custom name is applied, a red dot appears on the pencil icon. To clear the custom name, click the icon, and then Clear and Save.

  • Click Plus-button-sm to add another measure. If the selected chart type doesn't support multiple measures, this will be disabled.

For this example, the screen below shows five different user statuses selected as the measures in this example. These will return the number of users per status per SaaS application as selected in the Dimensions section above. The chart will display the users per status for each application. Each bar in the chart represents a SaaS application and will be segmented to display the number of users per user account status.

PivotChart - Metrics.png

NOTE

When using the Matrix visualization, you can select up to 10 measurements.

Segment a Complex Field

You can use the Pivot Chart to display a data from a complex field, segmented by another field within the same complex field. This allows for more granular analysis and flexibility in how you display your data.
CVEbySeverity

For example, you can display a device's vulnerable software by CVE severity and count the CVE IDs associated with each severity level. This way the segmentation is performed on the Complex Table level.

CVEbySeverity_Setup

6. Calculation

Select the calculation function you want. The available functions depends on the field type. You can apply a different function to each of the Measures in the Metrics section.

For this example, the Sum function is selected for each measure. This will calculate the total number of users per SaaS application, segmented by user status.

PivotChart - Calculation.png

You can mouse over a chart bar to see the segmentation displayed in a tooltip with the total number of users of each status.

PivotChart - DataExampleChartHover.png

The following table describes the calculation functions available for each field type.

FunctionBooleanNumericTextNotes
Sum*Total sum of values from fields
Average*Calculates the average
Count*Number of assets
Count True*Number of returned True values
Count False*Number of returned False values
  • Compare results - Display a comparison between the latest data and data from a previous date. See Comparing Today's Query Results to a Previous Date.

  • Add percentage - When enabled, calculates and shows the percentage of the segments compared to one of these values:

    • From Selected Query - Calculates the percentage of the total of the field selected in the module field.
    • From Custom Input - Enter a custom value. The segment's percentage is calculated as a fraction of the entered value.

Counting Adapter Connections vs Counting Unique Assets

When using the Count function you can either count adapter connections or unique assets.

To count the number of adapter connections select In general, the count function on shows the number of adapter connections for the selected asset field.

For example, when you filter for a specific asset name and use the Asset Name field with the count function, the Pivot chart shows the number of adapter connections with that asset name.

AssetName_Count

To show the number of unique assets, even though a given asset may repeat across adapter connections, you can select the Asset field with the Count function. In the same example, when we replace the Asset Name field with the Asset field, the number displayed will represent the number of unique assets with that asset name.
AssetCount

7. Presentation

Use the Presentation options to configure how the data in the chart is presented.
PivotChart%20-%20PresentationOptions

Select a default sort method for the bars or slices or lines:

  • Sort by value or Sort by name - Sort by the values or by the name of the value.
  • Ascending or Descending - Sort in ascending or descending order.

Additional Presentation settings:

  • Set threshold colors - Toggle on to set threshold colors. See Setting Asset Count Threshold Colors.When the Matrix visualization is selected, you can choose the following options for a color threshold:
    • Static - Set the threshold for displaying the fields in the selected colors.
      StaticThresholdColor

    • Dynamic - Automatically distribute the color intensity from the lowest to highest values in the table.
      DynamicColorThreshold

Heatmap

  • Show title - Toggle on to show the title of the chart from the Chart title field.
  • Show totals - Toggle on to show totals.

Timeline Visualization

When using the Timeline visualization, set the following:

  • In Aggregate by, select an aggregation time period: Hour, Day, Week, Month Quarter, or Year. Query results for weekly, monthly, quarterly, and yearly time periods are aggregated based on the average values for the selected time period. Each data point for weekly aggregation is the average of all daily values since the last weekly period. And each data point for the monthly aggregation is the average of all daily values since the last monthly period. You can choose to exclude date ranges from the displayed range. The legend reflects the time period selected.

    Note:
    On the daily aggregation chart, you can click through to the asset list.



Was this article helpful?