xyseries
This topic walks through how to use the xyseries command.
Description
Converts results into a tabular format that is suitable for graphing. This command is the inverse of the untable command.
Syntax
xyseries [grouped=<bool>] <x-field> <y-name-field> <y-data-field>... [sep=<string>] [format=<string>]
Required arguments
- <x-field>
- Syntax: <field>
- Description: The name of the field to use for the x-axis label. The values of this field appear as labels for the data series plotted on the x-axis.
- <y-name-field>
- Syntax: <field>
- Description: The field that contains the values to use as labels for the data series.
- <y-data-field>
- Syntax: <field> [,<field>] ...
- Description: One or more fields that contain the data to chart. When specifying multiple fields, separate the field names with commas.
Optional arguments
- format
- Syntax: format=<string>
- Description: Used to construct output field names when multiple data series are used in conjunction with a split-by-field and separate the <y-name-field> and the <y-data-field>.
format
takes precedence oversep
and lets you specify a parameterized expression with the stats aggregator and function ($AGG$) and the value of the split-by-field ($VALUE$).
- grouped
- Syntax: grouped= true | false
- Description: If true, indicates that the input is sorted by the value of the <x-field> and multifile input is allowed.
- Default:
false
- sep
- Syntax: sep=<string>
- Description: Used to construct output field names when multiple data series are used in conjunctions with a split-by field. This is equivalent to setting
format
to$AGG$<sep>$VALUE$
.
Usage
The xyseries
command is a distributable streaming command, unless grouped=true
is specified and then
the xyseries
command is a transforming command. See Command types.
Alias
The alias for the xyseries
command is maketable
.
Results with duplicate field values
When you use the xyseries
command to converts results into a tabular format, 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. For an example, see the Extended example for the untable command.
Example
Let's walk through an example to learn how to reformat search results with the xyseries command.
Write a search
This example uses the sample data from the Search Tutorial but should work with any format of Apache web access log. 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. |
Run this search in the search and reporting app:
sourcetype=access_* status=200 action=purchase | top categoryId
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.
The search results look 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 |
Identify your fields in the xyseries command syntax
In this example:
- <x-field> = categoryId
- <y-name-field> = count
- <y-data-field> = percent
Reformat search results with xyseries
When you apply the xyseries
command, the categoryId
serves as the <x-field> in your search results. The results of the calculation count
become the columns, <y-name-field>, in your search results. The <y-data-field>, percent
, corresponds to the values in your search results.
Run this search in the search and reporting app:
sourcetype=access_* status=200 action=purchase | top categoryId | xyseries categoryId count percent
The search results look like this:
categoryId | 138 | 245 | 246 | 348 | 367 | 493 | 806 |
---|---|---|---|---|---|---|---|
SPORTS | 5.221339 | ||||||
ACCESSORIES | 13.166856 | ||||||
ARCADE | 18.653046 | ||||||
SHOOTER | 9.269769 | ||||||
SIMULATION | 9.307605 | ||||||
STRATEGY | 30.495649 | ||||||
TEE | 13.885736 |
Extended example
Let's walk through an example to learn how to add optional arguments to the xyseries command.
Write a search
To add the optional arguments of the xyseries command, you need to write a search that includes a split-by-field command for multiple aggregates. Use the sep
and format
arguments to modify the output field names in your search results.
Run this search in the search and reporting app:
sourcetype=access_combined_wcookie | stats count(host) count(productId) by clientip, referer_domain
This search sorts referrer domain, count(host) and count(productId) by clientIp.
Run this search in the search and reporting app:
sourcetype=access_combined_wcookie | stats count(host) count(productId) by clientip, referer_domain | xyseries clientip referer_domain count(host), count(productId)
In this example:
- <x-field> = clientip
- <y-name-field> = referrer domain
- <y-data-field> = host, productId
The xyseries command needs two aggregates, in this example they are: count(host) count(productId). The first few search results look like this:
Add optional argument: sep
Add a string to the sep argument to change the default character that separates the <y-name-field> host,and the <y-data-field> productId. The format argument adds the <y-name-field> and separates the field name and field value by the default ":"
Run this search in the search and reporting app:
sourcetype=access_combined_wcookie | stats count(host) count(productId) by clientip, referer_domain | xyseries clientip referer_domain count(host), count(productId) sep="-"
The first few search results look like this:
Add optional argument: format
The format argument adds the <y-name-field> and separates the field name and field value by the default ":" For example, the default for this example looks like count(host):referrer_domain
When you specify a string to separate the <y-name-field> and <y-data-field> with the format argument, it overrides any assignment from the sep argument. In the following example, the sep argument assigns the "-" character to separate the <y-name-field> and <y-data-field> fields. The format argument assigns a "+" and this assignment takes precedence over sep. In this case $VAL$ and $AGG$ represent both the <y-name-field> and <y-data-field>. As seen in the search results, the <y-name-field>, host, and <y-data-field>, productId can correspond to either $VAL$ or $AGG$.
Run this search in the search and reporting app:
sourcetype=access_combined_wcookie | stats count(host) count(productId) by clientip, referer_domain | xyseries clientip referer_domain count(host), count(productId) sep="-" format="$AGG$ + $VAL$ TEST"
The first few search results look like this:
Add optional argument: grouped
The grouped argument determines whether the xyseries
command runs as a distributable streaming command, or a transforming command. The default state grouped=FALSE for the xyseries
command runs as a streaming command.
See also
- Commands
- untable
xpath | 3rd party custom commands |
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
Feedback submitted, thanks!