Comparing every elements in a list with a table

[oleg90]

Hello!

Could you advise, please, how can I compare results of 2 searches, which returns results in a different format?

First search:

...

<first part of the search>

...

| eval output3 = json_extract(output1, "data.affected_items{}.id")
| table output3

The result of this search looks like that:

["112","114","267","456"] (ony one row)

Second search:

...

<first part of the search>

...

| table id

The result of this search looks like that:

id (header)

111 (first row)

112 (second row)

255 (third row)

etc.

The number of elements in results of the first and the second searches is different.
I need to combine this searches in the one search that will have in the result common elements in both searches.

For example, if the first search has the following output:

["112","114","267","456"]

And the second search has the following output:

id (header)

111 (first row)

112 (second row)

255 (third row)

I need to have the following result:

id (header)

112 (first row)

Which Splunk functions or tools could you recommend for this purpose? The Splunk version is 8, so some new functionality from version 9 does not work.

Thank you.

Best regards,

[FelixLeh · Accepted solution]

|eval output3 = replace(output3,"[\[\]\"]","")
|makemv output3 delim=","
|mvexpand output3
|rename output3 as id
|join id
[<second_search>]

If you want to keep the original values of id from the first search add a temporary field:

|eval temp = replace(output3,"[\[\]\"]","")
|makemv temp_id delim=","
|mvexpand temp_id
|rename temp_id as id
|join id
[<second_search>]

If you want to combine the results of the second query back together add this to the end:

| mvcombine id

Keep in mind that join only works with up to 50.000 events but it doesn't seem like this limitation is relevant to your situation based on the example.

If the second search is a static list of codes that you want to match you could also put the results of the second query in to a lookup table:

|eval temp = replace(output3,"[\[\]\"]","")
|makemv temp_id delim=","
|mvexpand temp_id
|rename temp_id as id
|lookup <lookup_name> id OUTPUT id as found
|where isnotnull(found)
|fields - found

[oleg90]

Thank you very much!

...

| eval output1=json(output)
| eval output3 = json_extract(output1, "data.affected_items{}.id")
| eval output3 = replace(output3,"[\[\]\"]","")
| makemv output3 delim=","
| mvexpand output3
| rename output3 as id

returns all ids in a column, so it seems it is what I need for further processing of this data. Thanks a lot!

[ITWhisperer]

Rather than using json_extract(), try using spath

| spath input=output1 path="data.affected_items{}.id{}" output=output3
| mvexpand output3
| table output3

The mvexpand will split the output3 field across multiple events.

[oleg90]

Thank you, but it doesn't work by some reason...

...

...

| eval output1=json(output)
| eval output3 = json_extract(output1, "data.affected_items{}.id")
| table output3

- works fine, but the result in the one row

...

| eval output1=json(output)
| spath input=output1 path="data.affected_items{}.id{}" output=output3
| mvexpand output3
| table output3

- shows "No results found."

[ITWhisperer]

Perhaps if you shared your actual raw unformatted events (anonymised as appropriate) in a code block to preserve any formatting there might be in the event, we might be able to suggest something that might work with your data.

[oleg90]

Thank you very much, it seems the previous recommendation works for me.