Analyzing Query Data - Creating Data Analytics Reports

The Data Analytics page enables you to load query data into a configurable pivot table within Axonius. Use this feature to customize the way the data is aggregated and calculated, and save the resulting report for further analysis, or share it with others within an Axonius report. See Report Configuration Page for more about including pivot table data in an Axonius report.

Verify that your system meets the minimum suggested requirements.

The benefits of the Data Analytics page include the following:

• Simplified Data Analysis - The pivot table enables you to quickly and easily view query results in a simple, organized table. This makes it easier to analyze and understand the data.

• Customizable Aggregations - Customize the aggregation of query data by choosing which columns to use as rows, columns, and data fields. You can also specify the calculation types, such as sums, averages, or counts with calculated fields.

• Quick and Easy Filtering - You can filter the pivot value to make it easier to isolate subsets of data based on specific criteria. This can help you to focus on the data that is most relevant to you.

• Interactive - Rearrange, filter, and update the data to quickly explore different scenarios and uncover insights that might be hidden within the data.

• Descriptive Statistics - Use calculated fields to calculate descriptive statistics, such as sums, averages, and standard deviations to gain a deeper understanding of the data and identify patterns and trends.

The Data Analytics pivot table is a powerful tool that helps you to make more informed asset management and cyber risk decisions.

To access the Data Analytics page, click on the left navigation panel.

When the Data Analysis page is displayed, no data appears. Load query data to start working with the pivot table.

Permissions for Data Analytics

To use Data Analytics tables and to add them to Reports, your assigned role must have the following permissions:

• To load a query - Permission to view that asset type. For example, to create a table with SaaS application repository asset data, you need the permission View saas applications repository under the SaaS Applications Repository section.

• To save a Data Analytics report - The Save data analytics permission in the Global Actions permissions.

• To load an existing Data Analytics report - Permission to view that asset type.

• To add a Data Analytics report to a system report - The Add report permission under the Reports permission group as well as permission to view that asset type.

Loading Query Data into the Table

Load query data into the table to start working with it.

To load query data into the table

• Do one of the following:

  • To create a new report, from the Module drop-down list select an asset module. Then, from the Select Query drop-down list select a query. Note that you cannot select private queries.
    

    The query is run, the results are stored in cache and displayed in the grid. The query data is aggregated by adapter.

  • To open a saved report, click Load.

    

    From the list of displayed reports, select the report you want. The data is loaded into the table and presented exactly as it was saved, including column selection and filters.

    

Depending on the field configuration, table rows may be expandable.

Saving Query Data

You can save the table with the data and your configurations.

To save a query data report

• Do one of the following:

  • To save a new report, click Save As.

    

    Give the report a name and click Save. The data is saved as a CSV file.

  • To save changes to an existing report, click Save.

    

    Changes are saved immediately.

To view the most recent query results

The date and time data was last updated is displayed above the toolbar to the right.

• Click to rerun the selected query and display the most recent data. Field, formatting and filtering selections remain unchanged.

To remove all data from the table

• Click Clear. The module and query selections and all data are cleared from the table.
  

Splitting Data by Field Values or Asset Entities

You can select how to split the data in the table.

To split the data

1. Select an asset module and a query.
2. To the right of the query list, click .
3. To split the data by asset entities, select Split by asset entities.
4. To split by field values, click in the field list and select the adapter fields you want to split into single-value rows. You can select as many as needed. This will split each multi-value row into one row for each value. The limit is 500K rows, total.
5. Click Save.

Configuring Pivot Table Fields

In the Fields dialog, you can define the row and column fields in the pivot table.

On the left is a list of all available fields from the saved query. If there are more than 50 fields, a search bar appears at the top of the list of fields.

Values are typically placed in columns but can also be dragged to rows. Each value has an aggregation function applied to it. Depending on the data source type, numeric fields support different aggregation functions. String fields can also be selected for values.

By default, numeric and string fields are aggregated using the sum and count functions, respectively. To change the aggregation function, click the sigma icon and choose a new aggregation.

To configure the pivot table fields

1. Click Fields in the pivot table toolbar to open the Fields dialog. You can also click the diagonal arrow in the upper-right corner of the table. The Fields dialog can be resized.

2. Drag and drop fields to the Report filters, Rows, Columns, or Values lists.

   Fields can also be dragged between the lists or inside them to arrange the order. Each list can have as many fields as you want. You can also include any level of a hierarchy, as well as all its sub-levels, in rows or columns.

3. To remove a value or field from one of the lists, drag it back to the Fields list on the left.

4. Click APPLY.
   

You can also add a calculated value to the table. See Adding a Calculated Value.

Adding a Calculated Value

Use calculated fields to add custom calculations to the table.

To add a calculated value

1. Open the Field List and click Add calculated value.
2. In the empty text box, enter a value name.
3. Next to the fields you want to use, click the sigma icon to select the type of value to use.
4. Drag fields from the list of fields to the formula box. Click an arithmetic operator to add it. You can also manually enter field names, operators and values with the keyboard.
5. Select Calculate individual values to calculate the formula using raw values.

6. When you are finished configuring the calculated value, click APPLY. The new value is added to the Values list in the Fields dialog.
   

7. In the Fields dialog, click APPLY.

All calculated values are grouped under the Calculated values folder in the All fields list.

To edit a calculated value

• Click the pencil icon next to the value name either in the Fields list or in the Values list. Make the changes you want and click APPLY.

To remove a calculated value

• Click the trash can icon at the top of the Calculated value dialog.
  

Setting Layout Options

You can change the layout of the pivot table to make it easier to read.

To change the layout of the pivot table

1. From the toolbar, click Options.
2. Select the options you want and click APPLY.

Formatting Cells

You can format cells to make it easier to locate the values you want.

To format cells

1. Select the cells you want to format.
2. Hover over the Format icon in the toolbar and click Format cells.
3. From the CHOOSE VALUE list, select the value whose cell you want to format.
4. Select the options you want and click APPLY.

Using Conditional Formatting

Conditional formatting can be used to identify extreme values or to highlight a particular value in the table.

To apply conditional formatting

1. Select the cells you want to format.
2. Hover over the Format icon in the toolbar and click Conditional formatting.
3. To add the first condition, click the + at the top or + Add condition.
4. To add more conditions, click the + at the top. You can add as many conditions as you want.
5. To delete a condition, click the X to the right of the condition.

Filtering Fields

Apply a filter to a field to refine the data displayed in the table. The types of filters available depend on the field.

The following types of filters are available:

• Category filters - Filter by subcategory.
• Value filters - Filter based on value.
• Conditional filters - Filter based on a condition.

Applying Category Filters

Fields that have subcategories can be filtered by including or excluding the categories.

To apply a category filter

1. To the right of a field, click the gear icon.
   
2. Select the categories you want in the table. Click AZ or ZA to sort the list.
3. Click APPLY.

Applying Value Filters

You can filter the data by field value.

For example, Count of Aggregate Connections is the field to filter, Top is the condition, and 10 is the condition value.

See Conditional and Value Filter Operators for a list of operators available for each field type.

To apply a value filter

1. To the right of a field, click the gear icon.
   

2. In Filter by, click VALUES.
   

3. From left to right, select the field, the condition and the value.

4. Click APPLY.

Applying Conditional Filters

You can filter field members based on a condition. For example, you can create a filter to display dates of the previous quarter or assets whose name contains a specific text string.

You can apply a conditional filter to the following field types:

• Number
• String
• Date
• Time

See Conditional and Value Filter Operators for a list of operators available for each field type.

To apply a conditional filter

1. To the right of a field, click the gear icon.
   

2. In Filter by, click the type of field to which the conditional filter applies.

   • Labels – for string and number fields.
   • Dates – for date fields.
   • Time – for time fields.
     

3. Select the condition and enter the values required by that condition. Some conditions require more than one value.

   

4. Click APPLY.

Sorting the Pivot Table

You can sort the pivot table to reorder the data.

To sort the table

• Hover over a column header and click the up or down arrow.
  

Exporting Query Data to CSV

When you have created the table you want, use Export to CSV to share the data with external users.

To export data analytics table to CSV:

1. Use the Data Analytics functions to create the data table you want.
2. From the toolbar, click Export to CSV. The data is immediately downloaded to your local Downloads folder as pivot.csv.

System Requirements for Data Analytics

The following requirements must be met to use the Data Analytics page:

• A web browser. It is recommended that you use the most up-to-date version available for the best experience. The minimum browser requirements are listed below:
  • Chrome 12+
  • Firefox 15+
  • Microsoft Edge
  • Opera 15+
  • Safari 6.1+
  • iOS Safari 5.1.1+
  • JavaScript must be enabled.
• The minimal recommended size for the pivot table component is 400×300px.
• Internet Explorer 11 up to version 2.9.38