foreach
Description
Use this command to run a subsearch that includes a template to iterate over the following elements:
- Each field in a wildcard field list
- Each value in a single multivalue field
- A single field representing a JSON array
Syntax
The required syntax is in bold.
- foreach
- mode=(multifield | multivalue | json_array)
- <wildcard-field-list> | <field>
- [<mode-options>]
- <subsearch>
- [<subsearch-options>]
Required arguments
- wildcard-field-list
- Syntax: <string> ...
- Description: A space-delimited wildcard field name or list of wildcard field names that is used to iterate over one or more fields in a search. You can use the asterisk ( * ) as a wildcard to specify a list of fields with similar names. For example, if you want to specify all fields that start with "value", you can use a wildcard field such as
value*
. You can also specify a list of wildcard fields, such ashostA* hostB* hostC*
.
- You can use this argument only with the
multifield
mode.
- field
- Syntax: <string>
- Description: A field name that is used when iterating over elements in a multivalue field or JSON array, using the
multivalue
mode orjson_array
mode in a search.
- subsearch
- Syntax: [ subsearch ]
- Description: A subsearch that includes a template for replacing the values of the specified fields, which depend on whether you are using
multifield
mode,multivalue
mode, orjson_array
mode.
- For each field that is matched, the templated subsearch replaces values as follows:
Option Default template value Replacement Mode fieldstr <<FIELD>> The whole field name. multifield matchstr <<MATCHSTR>> The part of the field name that matches the wildcard values in the wildcard field. multifield matchseg1 <<MATCHSEG1>> The part of the field name that matches the first wildcard. multifield matchseg2 <<MATCHSEG2>> The part of the field name that matches the second wildcard. multifield matchseg3 <<MATCHSEG3>> The part of the field name that matches the third wildcard. multifield itemstr <<ITEM>> Matches each element in a multivalue field or JSON array. multivalue or json_array
Optional arguments
- mode
- Syntax: mode=<mode-name>
- Description: Tells the
foreach
command to iterate over multiple fields, a multivalue field, or a JSON array. If a mode is not specified, theforeach
command defaults to the mode for multiple fields, which is themultifield
mode.
- You can specify one of the following modes for the
foreach
command:
Argument Syntax Description multifield mode=multifield Iterates over a single supplied field name or a list of multiple field names, which can include wildcard characters. This is the default if the mode
is not specified.multivalue mode=multivalue Iterates over a single supplied multivalue field. Use this mode with multivalue fields. json_array mode=json_array Iterates over a single supplied JSON array. Use this mode with JSON functions.
- mode options
- The mode options depend on the mode you use.
- Use the following options to iterate over a field or list of fields using the
multifield
mode. These options are available only with themultifield
mode.
Option Syntax Description fieldstr fieldstr=<string> A customizable string that replaces the <<FIELD>>
template value with the name that you specify. The value of thefieldstr
option must match the<<FIELD>>
template value. For example, if you change the default<<FIELD>>
template value toMYFIELD
, then you must also change the value offieldstr
option toMYFIELD
.matchstr matchstr=<string> A customizable string that replaces the <<MATCHSTR>>
template value with the segment of the field name that matches the wildcard(s) in each field in the list. The value of thematchstr
option must match the<<MATCHSTR>>
template value. For example, if you change the default<<MATCHSTR>>
template value toID
, then you must also change the value ofmatchstr
toID
.
To avoid unpredictable results in searches, do not use thematchstr
option with anymatchseg*
options.matchseg1 matchseg1=<string> A customizable string that replaces the <<MATCHSEG1>>
template value with the segment of the field name that matches the first wildcard in each field in the list. The value of thematchseg1
option must match the<<MATCHSEG1>>
template value. For example, if you change the default<<MATCHSEG1>>
template value toPHONE
, then you must also change the value ofmatchseg1
toPHONE
.
To avoid unpredictable results in searches, do not use thematchseg1
option with thematchstr
option.matchseg2 matchseg2=<string> A customizable string that replaces the <<MATCHSEG2>>
template value with the segment of the field name that matches the second wildcard in each field in the list. The value of the<<matchseg2>>
option must match the<<MATCHSEG2>>
template value. For example, if you change the default<<MATCHSEG2>>
template value toPRICE
, then you must also change the value of<<matchseg2>>
toPRICE
.
To avoid unpredictable results in searches, do not use thematchseg2
option together with thematchstr
option.matchseg3 matchseg3=<string> A customizable string that replaces the <<MATCHSEG3>>
template value with the segment of the field name that matches the third wildcard in each field in the list. The value of the<<matchseg3>>
option must match the<<MATCHSEG3>>
template value. For example, if you change the default<<MATCHSEG3>>
template value toADDRESS
, then you must also change the value of<<matchseg3>>
toADDRESS
.
To avoid unpredictable results in searches, do not use thematchseg3
option together with thematchstr
option.
- Use the following option to iterate over multivalue fields or JSON arrays using the the
multivalue
mode or thejson_array
mode. This option is available only with themultivalue
mode or thejson_array
mode.
Option Syntax Description itemstr itemstr=<string> Replaces the <<ITEM>>
template value with each element in a multivalue field or JSON array. The value of theitemstr
option must match the<<ITEM>>
template value. For example, if you change the default<<ITEM>>
template value to<<ITERATOR>>
, then you must also change the value of<<itemstr>>
to<<ITERATOR>>
.
For example, consider the following search:| foreach mode=multivalue numbers [eval result = result + <<ITEM>>]
That search is equivalent to this search:
| foreach mode=multivalue itemstr=<<ITERATOR>> numbers [eval result = result + <<ITERATOR>>]
- subsearch options
- Syntax: <option-name>
- Description:
- The subsearch options depend on the mode you use.
- You can use the following optional template values in a subsearch when iterating over one or more fields. These template values are available only with the
multifield
mode.
Template value Description <<FIELD>> A customizable string replacement for each field name in the field list. Each time you run a subsearch, this value is used to replace the whole field name in the fieldstr
option for each field you specify. For example, if you change the value of<<FIELD>>
toMYFIELD
, then the value of thefieldstr
option is alsoMYFIELD
. Your search might look like this:...|foreach test* fieldstr=MYFIELD [eval total=total + MYFIELD]
.<<MATCHSTR>> A customizable string replacement that represents wildcards in each matching field name in the list. For example, if the wildcard field that is being matched is test*
and the field name istest8
, then the value of<<MATCHSTR>>
is 8.
To avoid unpredictable results in searches, do not use the<<MATCHSTR>>
template value together with any<<MATCHSEG*>>
template values.<<MATCHSEG1>> A customizable string replacement for the segment of the field name that matches the first segment before the first wildcard in each matching field name in the list.
To avoid unpredictable results in searches, do not use the<<MATCHSEG1>>
template value with the<<MATCHSTR>>
template value.<<MATCHSEG2>> A customizable string replacement for the segment of the field name that matches the second segment before the second wildcard in each matching field name in the list.
To avoid unpredictable results in searches, do not use the<<MATCHSEG2>>
template value with the <<<MATCHSTR>>
template value.<<MATCHSEG3>> A customizable string replacement for the segment of the field name that matches the third segment before the third wildcard in each matching field name in the list.
To avoid unpredictable results in searches, do not use the<<MATCHSEG3>>
template value with the<<MATCHSTR>>
template value.
- You can use the following optional template value in a subsearch to iterate over elements in a multivalue field or JSON array. This template value is available only with the
multivalue
mode or thejson_array
mode.
Template value Description <<ITEM>> A customizable string that replaces the <<ITEM>> template value with some other string that is substituted with the contents of the multivalued field or JSON array being iterated over. Only a single eval
statement is permitted in the search pipeline when using this template value with themultivalue
mode orjson_array
mode.
Usage
The foreach
command is a streaming command.
You can use the foreach
command in the following ways:
- To obtain results across multiple fields in each result row. This is useful, for example, when you need to calculate the average or sum of a value across multiple columns in each row. If you want to iterate over one or more matching fields, use the
multifield
mode.
- To iterate over multiple values within a single row's field in multivalue fields or JSON arrays. This is useful, for example, when you need to concatenate strings or calculate the average or sum of a set of numbers in a single field across multiple columns in each row in a multivalue field or JSON array. If you want to iterate over a multivalue field, use the
multivalue
mode. If you want to iterate over a JSON array node, use thejson_array
mode.
The default <<ITEM>>
template value should be used only when the mode is multivalue
or json_array
.
Iterating over multiple matching fields containing nonalphanumeric characters
If the field names contain characters other than alphanumeric characters, such as dashes, underscores, or periods, enclose the <<FIELD>>
template value in single quotation marks in the right side of the eval
command portion of the search to avoid unpredictable results. For example, the following search uses the default foreach
multifield mode and adds the values from all of the fields that match myfield_*
.
| makeresults
| eval myfield_1 = 5, myfield_2 = 10
| foreach myfield_*
[eval <<FIELD>> = '<<FIELD>>' + <<MATCHSTR>>]
The search results look something like this:
_time | myfield_1 | myfield_2 |
---|---|---|
2023-3-14 15:55:50 | 6 | 12 |
The <<FIELD>>
template value in the foreach
subsearch is just a string replacement of the field named myfield_*
. The eval expression does not recognize field names with nonalphanumeric characters unless the field names are surrounded by single quotation marks. For the eval expression to work, the <<FIELD>>
template value must be surrounded by single quotation marks.
Support for multiple eval statements
If you need to include multiple eval
statements with the foreach
command, use the default multifield mode. Multiple eval
statements are not supported in foreach
searches that use multivalue mode or JSON array mode. As a result, your searches on multivalue fields or JSON arrays must contain only a single eval
statement in the pipeline. However, your eval
statement can include as many assignments as you want.
For example, the following multivalue search with multiple eval
assignments completes successfully because there is only one eval
statement, which means there aren't any piped commands following the eval
command.
| makeresults
| eval mv=mvappend("5", "15"), total = 0, count = 0
| foreach mode=multivalue mv
[eval total = total + <<ITEM>>, count = count + 1]
The search results look something like this.
_time | count | mv | total |
---|---|---|---|
2022-03-29 19:52:38 | 2 | 5 15 |
20 |
Wildcards are not supported in multivalue fields or JSON arrays
Unlike multifield mode, the modes for multivalue fields and JSON arrays don't support wildcards in search expressions. Instead, these modes treat a wildcard as part of the field name. For example, the following search includes a field called mv*
, which looks like a wildcarded field.
| makeresults
| eval mv1=mvappend("1", "2"), mv2=mvappend("3", "4"), mv*=mvappend("100", "300"), total = 0
| foreach mode=multivalue mv*
[eval total = total + <<ITEM>>]
However, multivalue and JSON array modes don't recognize the wildcard and add up all of the fields containing mv
. As a result, the search results for multivalue mode look something like this:
_time | mv* | mv1 | mv2 | total |
---|---|---|---|---|
2022-4-20 15:55:50 | 100
300 |
1
2 |
3
4 |
400 |
Elements of the same type in multivalue fields or JSON arrays
Elements in a subsearch and the eval
expression must be of the same type as either strings or numbers. For example, the following search correctly adds up the three numbers in the JSON array because all of the elements are numbers.
| foreach json_array(1, 2, 3)
[eval total = total + <<ITEM>>]
However, the following search results in an error because adding a number to a string isn't allowed.
| foreach json_array(1, 2, "hello")
[eval total = total + <<ITEM>>]
Examples
1. Generate a total for each row in search results
Suppose you have events that contain the following data:
categoryId | www1 | www2 |
---|---|---|
ACCESSORIES | 1000 | 500 |
SIMULATION | 3000 | 750 |
ARCADE | 800 | |
STRATEGY | 400 | 200 |
Use the foreach
command with the default multifield
mode to iterate over each field that starts with www
and generate a total for each row in the search results.
...| eval total=0
| foreach www*
[eval total=total + <<FIELD>>]
The results look like this:
categoryId | www1 | www2 | total |
---|---|---|---|
ACCESSORIES | 1000 | 500 | 1500 |
SIMULATION | 3000 | 750 | 3750 |
ARCADE | 800 | 800 | |
STRATEGY | 400 | 200 | 600 |
2. Add the values from all fields that start with similar names
The following search adds the values from all of the fields that start with similar names and match the wildcard field test*
. It uses the foreach
command with the default multifield
mode
| makeresults
| eval total=0, test1=1, test2=2, test3=3
| foreach test*
[eval total=total + <<FIELD>>]
The results of the search look something like this.
_time | test1 | test2 | test3 | total |
---|---|---|---|---|
2022-4-20 15:55:50 | 1 | 2 | 3 | 6 |
- This search creates one result using the
makeresults
command. - The search then uses the
eval
command to create the fieldstotal
,test1
,test2
, andtest3
with corresponding values. - The
foreach
command is used to perform the subsearch for every field that starts with "test". Each time the subsearch is run, the previous total is added to the value of thetest
field to calculate the new total. The final total after all of thetest
fields are processed is 6.
The following table shows how the subsearch iterates over each test
field. The table shows the beginning value of the total
field each time the subsearch is run and the calculated total based on the value for the test
field.
Subsearch iteration | test field | total field start value | test field value | calculation of total field |
---|---|---|---|---|
1 | test1 | 0 | 1 | 0+1=1 |
2 | test2 | 1 | 2 | 1+2=3 |
3 | test3 | 3 | 3 | 3+3=6 |
3. Iterate over fields using the eval and foreach commands
The eval
command and foreach
command can be used in similar ways. For example, this search uses the eval
command:
| makeresults
| eval name="name" | eval price="price" | eval category="category"
It is equivalent to this search that uses the foreach
command with the default multifield
mode:
| makeresults
| foreach name price category
[eval <<FIELD>> = "<<FIELD>>"]
The results of both searches look something like this:
_time | category | name | price |
---|---|---|---|
2022-4-20 15:55:50 | category | name | price |
4. Monitor license usage
Use the foreach
command to monitor license usage.
First run the following search on the license master to return the daily license usage per source type in bytes:
index=_internal source=*license_usage.log type!="*Summary" earliest=-30d
| timechart span=1d sum(b) AS daily_bytes by st
The search results for one user across several days looks something like this:
_time | csv | universal_data_json |
---|---|---|
2022-04-03 | 8308923 | 36069628 |
2022-04-04 | 7290647 | 48851560 |
2022-04-05 | 7676935 | 12542231 |
2022-04-06 | 3016517 | 17521059 |
You can also use the foreach
command with the default multifield
mode to calculate the daily license usage in gigabytes for each field:
index=_internal source=*license_usage.log type!="*Summary" earliest=-30d
| timechart span=1d sum(b) AS daily_bytes by st
| foreach * [eval <<FIELD>>='<<FIELD>>'/1024/1024/1024]
This time the search results look something like this:
_time | csv | universal_data_json |
---|---|---|
2022-04-03 | 0.2335968237849277853 | 0.6309081468478106 |
2022-04-04 | 0.9703636813411461080 | 0.4818287762321547 |
2022-04-05 | 0.3825212419915378210 | 0.9126501722671725 |
2022-04-06 | 0.0028093503788113594 | 0.0163177577778697 |
5. Use the MATCHSTR template value
In this example, the <<FIELD>>
template value is a placeholder for test
, and the <<MATCHSTR>>
template value represents the wildcarded value that follows test
in each field name in the eval
expression. This example uses the default multifield
mode.
| makeresults
| eval test1 = 5, test2 = 10
| foreach test* [eval <<FIELD>> = <<FIELD>> + <<MATCHSTR>>]
The results look something like this:
_time | test1 | test2 |
---|---|---|
2022-03-28 15:43:39 | 6 | 12 |
The value of each field is added to the value that replaces the wildcard in the field name. For example, for the test1
field, 5 + 1 = 6.
6. Use the MATCHSEG1 and MATCHSEG2 template values
This example uses the default multifield
mode. The matchseg1
and matchseg2
options are used to add each field value to the two values represented by the wildcard in the corresponding <<MATCHSEG1>>
and <<MATCHSEG2>>
template values.
| makeresults
| eval test1ab2=5, test2ab3=10
| foreach test*ab* fieldstr=MYFIELD matchseg1=SEG1 matchseg2=SEG2
[eval MYFIELD = MYFIELD + SEG1 + SEG2]
Let's take a closer look at the syntax for the test1ab2=5
eval expression:
- The wildcard field is
test*ab*
. - The
<<FIELD>>
template value calledMYFIELD
istest1ab2=5
. - The field value is 5.
- The
<<MATCHSEG1>>
template value calledSEG1
is 1. - The
<<MATCHSEG2>>
template value calledSEG2
is 2.
- The wildcard field is
The results of the search look something like this:
_time | test1ab2 | test2ab3 |
---|---|---|
2022-03-28 17:10:56 | 8 | 15 |
The value of the test1ab2
field in the search results is 8 because 5 + 1 + 2 = 8.
7. Add values in a multivalue field
In this example using the multivalue
mode, <<ITEM>>
is a placeholder for each number in the multivalue field, which is added to the total.
| makeresults
| eval mvfield=mvappend("1", "2", "3"), total=0
| foreach mode=multivalue mvfield
[eval total = total + <<ITEM>>]
| table mvfield, total
The results of the search look something like this.
_time | mvfield | total |
---|---|---|
2022-4-20 15:55:50 | 1 2 |
6 |
The previous search produces similar results as the following eval
search, which also displays the total, but without listing each of the values that make up the total.
| makeresults
| eval total = 0
| eval total = total + 1
| eval total = total + 2
| eval total = total + 3
The search results look like this:
_time | total |
---|---|
2022-4-20 15:55:50 | 6 |
8. Categorize employees by manager using multivalue fields
You can create lists of employee names and organize them by manager using the eval
command or the foreach
command. This is an example of a search on employees and their manager using the eval
command:
| makeresults
| eval manager="Rutherford", employees=mvappend("Alex", "Claudia", "David")
| fields - _time
The results of the eval
search look something like this.
employees | manager |
---|---|
Alex Claudia |
Rutherford |
To create multivalue fields of employee names and organize them by manager, you can run a similar search using multivalue fields with the foreach
command:
| makeresults
| eval manager="Rutherford", employees=mvappend("Alex", "Claudia", "David"), employees_array=json_array()
| foreach mode=multivalue employees
[eval employees_array=json_append(employees_array, "", <<ITEM>>)]
| fields - _time
The search results this time look like this:
employees | employees_array | manager |
---|---|---|
Alex Claudia |
["Alex", "Claudia", "David"] | Rutherford |
9. Calculate grade averages using multivalue fields
To find the average of a set of student grades using the multivalue
mode, you could run this search:
| makeresults
| eval teacher="James", student_grades=mvappend("50", "100", "30"), sum = 0, count = 0
| foreach mode=multivalue student_grades
[eval sum = sum + <<ITEM>>, count = count + 1]
| eval average = sum / count
The search results look something like this:
_time | average | count | student_grades | sum | teacher |
---|---|---|---|---|---|
2022-03-21 16:02:30 | 60 | 3 | 50 100 |
180 | James |
10. Add values in a JSON array
If you want to do something simple like add up each element in a JSON array, you could run a search like this:
| makeresults
| eval jsonfield=json_array(1, 2, 3), total=0
| foreach mode=json_array jsonfield
[eval total = total + <<ITEM>>]
| table jsonfield, total
The search results look like this:
jsonfield | total |
---|---|
[1, 2, 3] | 6 |
11. Add values to a JSON array
Now let's take the names of the employees in a multivalue field and append them to a JSON array. In this search, the employees_array
is empty.
| makeresults
| eval manager="Rutherford", employees=mvappend("Alex", "Claudia", "David"), employees_array=json_array()
| fields - _time
The search results look like this:
employees | employees_array | manager |
---|---|---|
Alex Claudia |
[ ] | Rutherford |
To copy all the values from the multivalue field into json_array()
, use foreach
to iterate over the employees
values and append each of the employee names to the array, like this search:
| makeresults
| eval manager="Rutherford", employees=mvappend("Alex", "Claudia", "David"), employees_array=json_array()
| foreach mode=multivalue employees
[eval employees_array=json_append(employees_array, "", <<ITEM>>)]
| fields - _time
Now the search results look like this:
employees | employees_array | manager |
---|---|---|
Alex Claudia |
["Alex", "Claudia", "David"] | Rutherford |
The foreach
command just copied over each of the employees' names to the JSON array.
12. Extracting values from a JSON array
What if you want to extract values for given key names from a JSON array and do something with them? For example, the following search extracts a list of employee IDs from a JSON array of employees and puts them in a new field called ID_array
that you can use for other operations.
| makeresults
| eval manager="Rutherford", employees=mvappend("Alex", "Claudia", "David"), ID_array=json_array(), IDs=json_object("Alex", 4125, "Claudia", 2538, "David", 3957)
| foreach mode=multivalue employees
[eval ID_array=json_append(ID_array, "", json_extract(IDs, <<ITEM>>))]
| fields - _time
The results of this search look something like this:
ID_array | IDs | employees | manager |
---|---|---|---|
[4125,2538,3957] | {"Alex":4125,"Claudia":2538,"David":3957} | Alex Claudia |
Rutherford |
13. Multiplying elements in a JSON array
You can use the foreach
command to multiply numbers and append to a JSON array in searches like this:
| makeresults
| eval price=json_array(1,2,3,4), double_price=json_array()
| foreach mode=json_array price
[eval double_price = json_append(double_price, "", <<ITEM>> * 2)]
The results look something like this:
_time | double_price | price |
---|---|---|
2022-03-21 16:24:49 | [2,4,6,8] | [1,2,3,4] |
This search doubles each value in the array in price
and then adds the values to a new array called double_price
.
14. Calculating weights
To find the weights of values in a JSON array called grades
, you could run a search like this:
| makeresults
| eval grades=json_array(1,2,3,4), weight=json_array()
| eval sum = 0
| foreach mode=json_array grades
[eval sum = sum + <<ITEM>>]
| foreach mode=json_array grades
[eval weight = json_append(weight, "", <<ITEM>> / sum)]
The search results look something like this:
_time | grades | sum | weight |
---|---|---|---|
2022-03-31 12:58:16 | [1,2,3,4] | 10 | [0.1,0.2,0.3,0.4] |
See also
- Related information
- Evaluate and manipulate fields with multiple values
- JSON functions
folderize | format |
This documentation applies to the following versions of Splunk® Enterprise: 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
Feedback submitted, thanks!