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
