Splunk® Enterprise

Search Reference

Splunk Enterprise version 7.2 is no longer supported as of April 30, 2021. See the Splunk Software Support Policy for details. For information about upgrading to a supported version, see How to upgrade Splunk Enterprise.

untable

Description

Converts results from a tabular format to a format similar to stats output. This command is the inverse of the xyseries command.

Syntax

untable <x-field> <y-name-field> <y-data-field>

Required arguments

<x-field>
Syntax: <field>
Description: The field to use for the x-axis labels or row names. This is the first field in the output.
<y-name-field>
Syntax: <field>
Description: A name for the field to contain the labels for the data series. All of the field names, other than <x-field>, are used as the values for the <y-name-field> field. You can specify any name for this field.
<y-data-field>
Syntax: <field>
Description: A name for the field to contain the data to chart. All of the values from the fields, other than <x-field>, are used as the values for the <y-data-field> field. You can specify any name for this field.

Usage

The untable command is a distributable streaming command. See Command types.

Results with duplicate field values

When you untable a set of results and then use the xyseries command to combine the results, results that contain duplicate values are removed.

You can use the streamstats command create unique record numbers and use those numbers to retain all results. See Extended examples.

Basic example

This example uses the sample data from the Search Tutorial. To try this example on your own Splunk instance, you must download the sample data and follow the instructions to get the tutorial data into Splunk. Use the time range All time when you run the search.

To show how to use the untable command, we need results that appear in a table format. Run this search.

sourcetype=access_* status=200 action=purchase | top categoryId

The results appear on the Statistics tab and look something like this:

categoryId count percent
STRATEGY 806 30.495649
ARCADE 493 18.653046
TEE 367 13.885736
ACCESSORIES 348 13.166856
SIMULATION 246 9.307605
SHOOTER 245 9.269769
SPORTS 138 5.221339

The top command automatically adds the count and percent fields to the results.

For each categoryId, there are two values, the count and the percent. When you untable these results, there will be three columns in the output:

  • The first column lists the category IDs
  • The second column lists the type of calculation: count or percent
  • The third column lists the values for each calculation

When you use the untable command to convert the tabular results, you must specify the categoryId field first. You can use any field name you want for the type of calculation and the values. For example:

sourcetype=access_* status=200 action=purchase | top categoryId | untable categoryId calculation value

The results appear on the Statistics tab and look something like this:

categoryId calculation value
STRATEGY count 806
STRATEGY percent 30.495649
ARCADE count 493
ARCADE percent 18.653046
TEE count 367
TEE percent 13.885736
ACCESSORIES count 348
ACCESSORIES percent 13.166856
SIMULATION count 246
SIMULATION percent 9.307605

Extended example

The untable command does exactly what the name says, it converts tabular information into individual rows of results. Suppose you have this search:

...| table _time EventCode Message

The search produces these results:

_time EventCode Message
date-time1 4136 Too late now
date_time2 1234 I dont know
date_time3 3456 Too busy, ask again later
date_time4 1256 Everything is happening at once
date_time4 1257 And right now, as well

Notice that this set of events has duplicate values in the _time field for date_time4. We will come back to that in a moment.

Use the untable command to remove the tabular format.

...| untable _time FieldName FieldValue

Here are the results from the untable command:

_time FieldName FieldValue
date-time1 EventCode 4136
date-time1 Message Too late now
date_time2 EventCode 1234
date-time2 Message I dont know
date_time3 EventCode 3456
date-time3 Message Too busy, ask again later
date_time4 EventCode 1256
date-time4 Message Everything is happening at once
date_time4 EventCode 1257
date-time4 Message And right now, as well

Events with duplicate timestamps

Remember that the original set of events in this example had duplicates for date_time4. If you want to process the events in some way and then put the events back together, you can avoid eliminating the duplicate events by using the streamstats command.

Use the streamstats command to give each event a unique record number and use that unique number as the key field for the untable and xyseries commands.

For example, you can add the streamstats command to your original search.

...| table _time EventCode Message | streamstats count as recno

The search produces these results:

_time EventCode Message recno
date-time1 4136 Too late now 1
date_time2 1234 I dont know 2
date_time3 3456 Too busy, ask again later 3
date_time4 1256 Everything is happening at once 4
date_time4 1257 And right now, as well 5

You can then add the untable command to your search, using recno as the <x-field>:

...| table _time EventCode Message | streamstats count as recno | untable recno FieldName FieldValue

The search produces these results:

recno FieldName FieldValue
1 EventCode 4136
1 Message Too late now
2 EventCode 1234
2 Message I dont know
3 EventCode 3456
3 Message Too busy, ask again later
4 EventCode 1256
4 Message Everything is happening at once
4 EventCode 1257
4 Message And right now, as well

These events can be put back together by using the xyseries command, again using the recno field as the <x-field>. For example:

...| xyseries recno FieldName FieldValue

The search produces these results:

recno EventCode Message
1 4136 Too late now
2 1234 I dont know
3 3456 Too busy, ask again later
4 1256 Everything is happening at once
5 1257 And right now, as well

Restoring the timestamps

In addition to using the streamstats command to generate a record number, you can use the rename command to restore the timestamp information after the xyseries command. For example:

...| table _time EventCode Message | streamstats count as recno | rename _time as time | untable recno FieldName FieldValue | xyseries recno FieldName FieldValue | rename time as _time

(Thanks to Splunk users DalJeanis and BigCosta for their help with this example.)

See also

xyseries

Last modified on 06 October, 2022
uniq   where

This documentation applies to the following versions of Splunk® Enterprise: 7.0.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.0.5, 7.0.6, 7.0.7, 7.0.8, 7.0.9, 7.0.10, 7.0.11, 7.0.13, 7.1.0, 7.1.1, 7.1.2, 7.1.3, 7.1.4, 7.1.5, 7.1.6, 7.1.7, 7.1.8, 7.1.9, 7.1.10, 7.2.0, 7.2.1, 7.2.2, 7.2.3, 7.2.4, 7.2.5, 7.2.6, 7.2.7, 7.2.8, 7.2.9, 7.2.10, 7.3.0, 7.3.1, 7.3.2, 7.3.3, 7.3.4, 7.3.5, 7.3.6, 7.3.7, 7.3.8, 7.3.9, 8.0.0, 8.0.1, 8.0.2, 8.0.3, 8.0.4, 8.0.5, 8.0.6, 8.0.7, 8.0.8, 8.0.9, 8.0.10, 8.1.1, 8.1.2, 8.1.3, 8.1.4, 8.1.5, 8.1.6, 8.1.7, 8.1.8, 8.1.9, 8.1.13, 8.1.14, 8.2.0, 8.2.1, 8.2.2, 8.2.3, 8.2.4, 8.2.5, 8.2.6, 8.2.7, 8.2.8, 8.2.9, 8.2.10, 8.2.11, 8.2.12, 9.0.0, 9.0.1, 9.0.2, 9.0.3, 9.0.4, 9.0.5, 9.0.6, 9.0.7, 9.0.8, 9.0.9, 9.0.10, 9.1.0, 9.1.1, 9.1.2, 9.1.3, 9.1.4, 9.1.5, 9.1.6, 9.2.0, 9.2.1, 9.2.2, 9.2.3, 9.3.0, 9.3.1, 8.1.0, 8.1.10, 8.1.11, 8.1.12


Was this topic useful?







You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters