Overview

During an incident investigation, you might need to run complex queries, such as combining attributes from multiple log sources or transforming log data, to analyze your logs. Use Log Workspaces to run queries to:

• Correlate multiple data sources
• Aggregate multiple levels of data
• Join data across multiple log sources and other datasets
• Extract data or add a calculated field at query time
• Add visualizations for your transformed datasets

Create a workspace and add a data source

You can create a workspace from the Workspaces page or from the Log Explorer.

On the Log Workspaces page:

1. Click New Workspace.
2. Click the Data source tile.
3. Enter a query. The reserved attributes of the filtered logs are added as columns.

In the Log Explorer:

1. Enter a query.
2. Click More, next to Download as CSV, and select Open in Workspace.
3. The workspace adds the log query to a data source cell. By default, the columns in Log Explorer are added to the data source cell.

Analyze, transform, and visualize your logs

You can add the following cells to:

• Include additional data sources such as reference tables
• Use SQL to join data
• Transform, correlate, and visualize the data

Cells that depend on other cells are automatically updated when one of the cells it depends on is changed.

At the bottom of your workspace, click any of the cell tiles to add it to your workspace. After adding a cell, you can click the dataset on the left side of your workspace page to go directly to that cell.

Data source cell

You can add a logs query or a reference table as a data source.

1. Click on the Data source tile.
   • To add a reference table:
     1. Select Reference table in the Data source dropdown.
     2. Select the reference table you want to use.
   • To add a logs data source:
     1. Enter a query. The reserved attributes of the filtered logs are added as columns.
     2. Click datasource_x at the top of the cell to rename the data source.
     3. Click Columns to see the columns available. Click as for a column to add an alias.
     4. To add additional columns to the dataset:
        a. Click on a log.
        b. Click the cog next to the facet you want to add as a column.
        c. Select Add…to…dataset.
2. Click the download icon to export the dataset as a CSV.

Analysis cell

1. Click the Analysis tile to add a cell and use SQL to query the data from any of the data sources. You can use natural language or SQL to query your data . An example using natural language: select only timestamp, customer id, transaction id from the transaction logs.
2. If you are using SQL, click Run to run the SQL commands.
3. Click the download icon to export the dataset as a CSV.

Visualization cell

Add the Visualization cell to display your data as a:

• Table
• Top list
• Timeseries
• Treemap
• Pie chart
• Scatterplot

1. Click the Visualization tile.
2. Select the data source you want to visualize in the Source dataset dropdown menu.
3. Select your visualization method in the Visualize as dropdown menu.
4. Enter a filter if you want to filter to a subset of the data. For example, status:error. If you are using an analysis cell as your data source, you can also filter the data in SQL first.
5. If you want to group your data, click Add Aggregation and select the information you want to group by.
6. Click the download button to export the data as a CSV.

Transformation cell

Click the Transformation tile to add a cell for filtering, aggregating, and extracting data.

1. Click the Transformation tile.
2. Select the data source you want to transform in the Source dataset dropdown menu.
3. Click the plus icon to add a Filter, Parse, or Aggregate function.
   • For Filter, add a filter query for the dataset.
   • For Parse, enter grok syntax to extract data into a separate column. In the from dropdown menu, select the column the data is getting extracted from. See the column extraction example.
   • For Aggregate, select what you want to group the data by in the dropdown menus.
   • For Limit, enter the number of rows of the dataset you want to display.
4. Click the download icon to export the dataset into a CSV.

Column extraction example

The following is an example dataset:

Use the following grok syntax to extract the customer ID from the message and add it to a new column called customer_id:

Submitted order for customer %{notSpace:customer_id}`

This is the resulting dataset in the transformation cell after the extraction:

Text cell

Click the Text cell to add a markdown cell so you can add information and notes.

An example workspace

This example workspace has:

• Three data sources:

  • transaction_start_logs
  • transaction_execution_logs
  • trading_platform_users

• Three derived datasets, which are the results of data that has been transformed from filtering, grouping, or querying using SQL:

  • parsed_executed_logs
  • transaction_record
  • transaction_record_with_names

• One treemap visualization.

This diagram shows the different transformation and analysis cells the data sources go through.

Example walkthrough

The example starts off with two logs data sources:

• transaction_start_logs
• transaction_execution_logs

The next cell in the workspace is the transform cell parsed_executed_logs. It uses the following grok parsing syntax to extract the transaction ID from the message column of the transaction_execution_logs dataset and adds the transaction ID to a new column called transaction_id.

transaction %{notSpace:transaction_id}

An example of the resulting parsed_executed_logs dataset:

The analysis cell transaction_record uses the following SQL command to select specific columns from the transaction_start_logs dataset and the transaction_execution_logs, renames the status INFO to OK, and then joins the two datasets.

SELECT
    start_logs.timestamp,
    start_logs.customer_id,
    start_logs.transaction_id,
    start_logs.dollar_value,
    CASE
        WHEN executed_logs.status = 'INFO' THEN 'OK'
        ELSE executed_logs.status
    END AS status
FROM
    transaction_start_logs AS start_logs
JOIN
    transaction_execution_logs AS executed_logs
ON
    start_logs.transaction_id = executed_logs.transaction_id;

An example of the resulting transaction_record dataset:

Then the reference table trading_platform_users is added as a data source:

The analysis cell transaction_record_with_names runs the following SQL command to take the customer name and account status from trading_platform_users, appending it as columns, and then joins it with the transaction_records dataset:

SELECT tr.timestamp, tr.customer_id, tpu.customer_name, tpu.account_status, tr.transaction_id, tr.dollar_value, tr.status
FROM transaction_record AS tr
LEFT JOIN trading_platform_users AS tpu ON tr.customer_id = tpu.customer_id;

An example of the resulting transaction_record_with_names dataset:

Finally, a treemap visualization cell is created with the transaction_record_with_names dataset filtered for status:error logs and grouped by dollar_value, account_status, and customer_name.