union command: Overview, syntax, and usage

The SPL2 union command merges the results from two or more datasets into one larger dataset. One of the datasets can be the incoming search results that are then piped into the union command and merged with a second dataset.

Use these links to quickly navigate to the main sections in this topic:

• How the SPL2 union command works
• Syntax
• Usage
• See also

How the SPL2 union command works

You can use the SPL2 union command to merge datasets that have identical or different field names. The following sections illustrate how to use the union command in both of these situations.

Datasets with identical field names

Consider the following two datasets:

products-amer

productID	product_name	supplierID	supplier_name	categoryID
BS-AG-G09	Benign Space Debris	A51G-USA	Area 51 Games	ARCADE
SF-BVS-G01	Grand Theft Scooter	IP-PAN	Isthmus Pastimes	ARCADE

products-apac

productID	product_name	supplierID	supplier_name	categoryID
DC-SG-G02	Dream Crusher	PMG-KOR	Play More Games	STRATEGY
PZ-SG-G05	Puppies vs. Zombies	TF-JAP	Tiger Fun	STRATEGY
SC-MG-G10	SIM Cubicle	PMG-KOR	Play More Games	SIMULATION

You can use the SPL2 union command to bring these dataset together. For example:

$products = union products-amer, products-apac

The results look something like this:

productID	product_name	supplierID	supplier_name	categoryID
BS-AG-G09	Benign Space Debris	A51G-USA	Area 51 Games	ARCADE
DC-SG-G02	Dream Crusher	PMG-KOR	Play More Games	STRATEGY
PZ-SG-G05	Puppies vs. Zombies	TF-JAP	Tiger Fun	STRATEGY
SC-MG-G10	SIM Cubicle	PMG-KOR	Play More Games	SIMULATION
SF-BVS-G01	Grand Theft Scooter	IP-PAN	Isthmus Pastimes	ARCADE

Datasets with different field names

Consider the following events from two datasets:

products-apac

productID	product_name	supplierID	supplier_name	categoryID
DC-SG-G02	Dream Crusher	PMG-KOR	Play More Games	STRATEGY

suppliers_apac

supplierId	supplier_name	contact_name	email	address
PMG-KOR	Play More Games	Vanya Patel	vanya@sample.com	234 Sejong-daero ... Seoul South Korea

Notice that both events have a field called supplier_name and fields for the supplier ID, but with different capitalization: supplierID and supplierId.

You can use the union command to bring these dataset together. For example:

$products = union products-apac, suppliers_apac

When the datasets are unioned, the fields from both datasets added to the output. The NULL value is added to fields that were not in the original event.

The results look something like this:

address	categoryID	contact_name	email	productID	product_name	supplierID	supplierId	supplier_name
NULL	STRATEGY	NULL	NULL	DC-SG-G02	Dream Crusher	PMG-KOR	NULL	Play More Games
234 Sejong-daero ... Seoul South Korea	NULL	Vanya Patel	vanya@sample.com	NULL	NULL	NULL	PMG-KOR	Play More Games

Both events have a field called supplier_name that appears in the output with the same value. However, because the supplier ID fields have different capitalization, both fields appear in the output, even though the fields have the same value.

Syntax

The required syntax is in bold.

union

<dataset> ["," <dataset>...]

Required arguments

dataset

Syntax: [ <dataset-kind>"."]<dataset-name>

Description: The dataset that you want to perform the union on. Because dataset names must be unique, you only need to specify the dataset kind for built-in datasets that include the kind. The dataset can be the incoming set of search results, a dataset that has been defined in the Metadata Catalog, or a literal dataset that you type in. To perform a union with the incoming search results, you only need to specify one dataset. See the Usage section.

Usage

The union command is a generating command. Generating commands fetch information from the datasets, without any transformations.

You can use the union command at the beginning of your search to combine two datasets or later in your search where you can combine the incoming search results with a dataset.

Specifying a dataset

You can declare, or specify, a dataset several different ways. Here are some examples:

Type of declaration	Description	Example
Dataset references	Specifying an existing dataset. The datasets in this example are indexes.	...| union main, customers, purchases
Transient	Specifying a SPL subsearch as the dataset. Subsearches are enclosed in square brackets.	...| union [search main | stats count() by host ], [from customers | stats count() by host]
Fluent	The search results that are piped into the union command are referred to as a fluent dataset. This type of declaration has a union command that contains one or more subsearches.	... <some search criteria> | union [<subsearch1>], [<subsearch2>]
Literal	Using literal values that you type in as subsearches. Each subsearch is a dataset. This example shows three separate literal dataset declarations.	from [{state:"Washington", population:39557045}] | union [{state:"California", population:753591}, {state:"Oregon", population:4190713}]
Mixed	Specifying a mixture of the types of declarations. This example begins with a fluent, contains a dataset reference <ds1>, includes a subsearch comprised of SPL syntax <subsearch1>, and then a subsearch comprised of literal values.	... | <union ds1, [ <subsearch1> ], [ { "state": "Washington", "population": 39557045 } ]

Semantics

If all of the datasets that are unioned together are streamable time-series, the union command attempts to interleave the data from all datasets into one globally sorted list of events or metrics. The list is based on the _time field in descending order. Otherwise, the union command returns all the rows from the first dataset, followed by all the rows from the second dataset, and so on.

Interleaving results

When two datasets are retrieved from disk in time descending order, which is the default sort order, the union command interleaves the results. The interleave is based on the _time field. For example, suppose you have the following datasets:

dataset_A

_time	Host	Bytes
4	mailsrv1	2412
1	dns15	231

dataset_B

_time	Host	Bytes
3	router1	23
2	dns12	22o

Both datasets are descending order by _time. When | union dataset_A, dataset_B is run, the following dataset is the result.

_time	Host	Bytes
4	mailsrv1	2412
3	router1	23
2	dns12	22o
1	dns15	231

See also

union command

union command: Examples