Splunk® Enterprise

Search Reference

Acrobat logo Download manual as PDF


Acrobat logo Download topic as PDF

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 as hostA* 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 or json_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, or json_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, the foreach command defaults to the mode for multiple fields, which is the multifield 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 the multifield 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 the fieldstr option must match the <<FIELD>> template value. For example, if you change the default <<FIELD>> template value to MYFIELD, then you must also change the value of fieldstr option to MYFIELD.
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 the matchstr option must match the <<MATCHSTR>> template value. For example, if you change the default <<MATCHSTR>> template value to ID, then you must also change the value of matchstr to ID.


To avoid unpredictable results in searches, do not use the matchstr option with any matchseg* 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 the matchseg1 option must match the <<MATCHSEG1>> template value. For example, if you change the default <<MATCHSEG1>> template value to PHONE, then you must also change the value of matchseg1 to PHONE.


To avoid unpredictable results in searches, do not use the matchseg1 option with the matchstr 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 to PRICE, then you must also change the value of <<matchseg2>> to PRICE.


To avoid unpredictable results in searches, do not use the matchseg2 option together with the matchstr option.

matchseg3 matchseg3=<string> A customizable string that replaces the <<MATCHSEG3>> template value with the segment of the field name that matches the second 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 to ADDRESS, then you must also change the value of <<matchseg3>> to ADDRESS.


To avoid unpredictable results in searches, do not use the matchseg3 option together with the matchstr option.

Use the following option to iterate over multivalue fields or JSON arrays using the the multivalue mode or the json_array mode. This option is available only with the multivalue mode or the json_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 the itemstr option must match the <<ITEM>> template value. For example, if you change the default <<ITEM>> template value to ADDRESS, then you must also change the value of <<itemstr>> to ADDRESS.
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>> to MYFIELD, then the value of the fieldstr option is also MYFIELD. 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 is test8, 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 the json_array mode.
Template value Description
<<ITEM>> A customizable string replacement for each element in a multivalue field or JSON array. Only a single eval statement is permitted in the search pipeline when using this template value with the multivalue mode or json_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 the json_array mode.

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 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
2022-4-20 15:55:50 5 10

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 multivalue 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 fields total, test1, test2, and test3 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 the test field to calculate the new total. The final total after all of the test 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 called MYFIELDis test1ab2=5.
  • The field value is 5.
  • The <<MATCHSEG1>> template value called SEG1 is 1.
  • The <<MATCHSEG2>>template value called SEG2 is 2.

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
3

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
David

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
David

["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
30

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
David

[ ] 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
David

["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
David

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

Commands
eval, map
Related information
Evaluate and manipulate fields with multiple values
JSON functions
Last modified on 20 July, 2022
PREVIOUS
folderize
  NEXT
format

This documentation applies to the following versions of Splunk® Enterprise: 9.0.0


Was this documentation topic helpful?


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