Creating and Using Worksheets#

Explore the details of your data in a Worksheet to filter to rows of interest, extract new fields, create a visualization, and more. Since the underlying data doesn’t change, you can try multiple scenarios as you shape and transform your data.

Worksheets Landing Page

Figure 1 - Worksheets Landing Page

When you click Worksheets in the Navigation bar, the landing page displays the following information:

  • My Worksheets - Worksheets that you created on the Observe instance.

  • app - The app used to create the Worksheet.

  • date created - the date you created the Worksheet.

  • creator - the user that created the Worksheet.

  • modified by - the user who modified the Worksheet.

  • date modified - the modification date of the Worksheet.

You can also see Worksheets created by users with access to the instance.

Worksheet Basics#

This example uses data from a Kubernetes Ingress Logs dataset. The Ingress Logs dataset tracks remote IP address connections, the connection method, such as POST or GET, parameters, protocols, and other pertinent information. You can perform basic searches, but using the spreadsheet-like Worksheet gives you many more options, from searching and filtering to creating additional Event Streams and Resource Sets.

Ingress Logs Worksheet

Figure 2 - Ingress Logs Worksheet

Clicking on the Down arrow at the top of a column displays options for working with data in a single column. The type of options depends on the data type in the column.

Column menu options

Figure 3 - Column Menu Options

  • Filter - filter data by a single parameter.

  • Remove empty cells - remove empty cells from the column.

  • Create as visualization - create a visual representation of data in a column.

  • Create summary - add a summary of the data in a column.

  • Extract from string - extract parameters depending on the type of string.

  • Sort A -> Z (descending order)

  • Sort Z -> A (ascending order)

  • Hide column - hide the column from view.

  • Convert -

    • int - integer

    • float - floating-point type

    • time - timestamp, date, time, interval

    • JSON - JavaScript Object Notation type

  • Add parameters

    • Existing - add existing parameters to the column data.

    • Create new - create a new parameter.

  • Add to resource

    • Datasets - add the column data to an existing Dataset.

    • Add to new - add the column data to a new Dataset

  • Link to other dataset

    • Datasets - link the column data to a listed Dataset.

    • View more - list more Datasets.

Some columns allow you to extract JSON from the data. Use this to create additional columns of data. In this worksheet, selecting Extract from JSON on the parameter column displays a list of fields that you can select and create columns. You can click Preview to see what your extraction displays as columns. Click Cancel to exit the Extract from JSON option.

Filtering and Visualizing Data#

Analyzing Kubernetes Ingress logs can help you gain insights into your application’s traffic and performance, troubleshoot issues, and detect and investigate security incidents.

You want to review the request times for your Kubernetes collector endpoints. You can perform this task in the Worksheet by first filtering the path column to use only the /v1/kubernetes/logs data.

Column menu for the path column

Figure 4 - Column menu for path

To filter by Kubernetes paths, select Filter from the path column menu to open the Filter dialog. Select /v1/observations/kubernetes/logs.

Filtered path by Kubernetes Logs

Figure 5 - Filtered path by Kubernetes Logs

Note that the corresponding OPAL appears in the Script field.

filter path = "/v1/observations/kubernetes/logs"

Visualizing a Single Column of Data#

Now that the Worksheet contains only data from the Kubernetes logs, you can select a column and visualize data into one of the following types:

  • Time Series - data over a selected time period.

  • Single Stat - a single data point.

  • Value Over Time - a single value over a selected time period.

  • Pie Chart

  • Bar Chart

Select the request_time column, and then the column menu. Click Remove empty cells to be sure you visualize cells with data. Click the column menu again, and then click Create as visualization.

Upstream response time menu

Figure 6 - Selecting Create Visualization

The column data now displays as a Time Series. While reviewing the information, you notice a spike in the request time around 8:00.

Time Series with a large spike in request time

Figure 7 - Time Series with a large spike in request time

Using the Expression builder, you can identify the source of the spike. In the Input Source field, select request_time and add a value >100ms. Leave Plot as over time, using function at Count Values of request_time, and from the by menu, select Endpoint.

Running Expression Builder in a Worksheet

Figure 8 - Running Expression Builder in a Worksheet

Expression Builder using OPAL

filter request_time > 100ms
timechart options(empty_bins:true), request_time_count_b39ogeh7:count(request_time), group_by(upstream_ip, upstream_port, upstream_protocol, clusterUid)

After running Expression Builder, the Visualization displays Collector 9 as the connector with the spike in request time.

Collector 9 with a spike in request time

Figure 9 - Collector 9 with spike in request time

Using OPAL

filter path = "/v1/observations/kubernetes/logs"
filter not is_null(request_time)
filter request_time > 100ms
timechart options(empty_bins:true), request_time_count_w6rkeouu:count(request_time), group_by(upstream_ip, upstream_port, upstream_protocol, clusterUid)

From the visualization, you can see that Collector 9 experienced a spike in request time.

On the Visualization pane, click the name of the collector, Collector 9, and display details about Collector 9 in the right panel.

Collector 9 Detailed Information

Figure 10 - Collector 9 Detailed Information

To view the Dashboard for Collector 9, click Open from the Details panel.

Collector 9 Dashboard

Figure 11 - Collector 9 Dashboard

You can see in the statusErrors panel that errors occurred on Collector 9. To view the errors, click the statusErrors panel to display the More icon. Click the More icon and select Open in worksheet.

Collector 9 Worksheet

Figure 12 - Collector 9 Worksheet

Add a name for the Worksheet, such as Collector Status Errors, and click Save.

From the metricName column menu, click Filter and then statusErrors. This filters the column data to display only the errors that occurred on Collector 9.

Observe app in Okta

Figure 13 - Collector 9 statusErrors

Sharing a Worksheet in Observe#

Share a Worksheet, Dataset, Dashboard, or other objects with another Observe member using the Share icon next to Favorites.

Share a link with another Observe user

Figure 14 - Share a Link with another Observe Member

Select from one of three Save options to share your object, such as Default, or with filters and time:

  • Default - defaults to the current version of the Datasheet or Dashboard.

  • Filter and relative time - displays current filters with data in the relative timeframe in the Select Time Frame menu.

  • Filters and current time - displays the latest version based on the current time.

Click Copy Link to copy the link to your clipboard and then provide the link to the Observe member.

Your copied link looks similar to the following link:

https://yourinstance.observeinc.com/workspace/12345678/dataset/event/CloudTrail-Events-123456789?

To learn more about the types of visualizations supported in Observe, see the Visualization Types Reference.