topic Re: Comparing every elements in a list with a table in Splunk Search

Comparing every elements in a list with a table

oleg90 — Wed, 08 Nov 2023 13:10:29 GMT

Hello!

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

First 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:

...

...

| 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,

Re: Comparing every elements in a list with a table

ITWhisperer — Wed, 08 Nov 2023 12:55:43 GMT

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.

Re: Comparing every elements in a list with a table

FelixLeh — Wed, 08 Nov 2023 13:07:38 GMT

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

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:

Re: Comparing every elements in a list with a table

oleg90 — Wed, 08 Nov 2023 16:00:06 GMT

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."

Re: Comparing every elements in a list with a table

ITWhisperer — Wed, 08 Nov 2023 16:02:34 GMT

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.

Re: Comparing every elements in a list with a table

oleg90 — Wed, 08 Nov 2023 16:07:55 GMT

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!

Re: Comparing every elements in a list with a table

oleg90 — Wed, 08 Nov 2023 16:09:16 GMT

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