topic Re: Convert single row with list of ids to a table and join it with another table in Splunk Search

Convert single row with list of ids to a table and join it with another table

elad — Wed, 17 Nov 2021 11:30:13 GMT

I have this query:

my search | rex field=line ".*customerId\":(?<customer_id>[0-9]+)" | dedup customer_id | table customer_id

That returns multiple rows and generate a table:

customer_id ----------- 1 2 3 4 5

I also have another query that returns a single row with an array of ids:

Synced accounts: [ 1, 3, 5 ]

My questions are :

1) How can I convert the row from query 2 into a table with the ids

2) how can I do left join between the results ( that I will see on the table only the ids from query 2)?

customer_id ---------- 1 3 5

Thanks in advance

Elad

PickleRick — Wed, 17 Nov 2021 11:38:28 GMT

What do you mean by "array"?

And are you sure you want a left join? It looks more like inner join.

ITWhisperer — Wed, 17 Nov 2021 11:43:59 GMT

elad — Wed, 17 Nov 2021 11:53:39 GMT

@PickleRick please see the example I provided:

Synced accounts: [ 1, 3, 5 ]

PickleRick — Wed, 17 Nov 2021 11:56:39 GMT

But is it a single string value, a multivalued field, something else?

elad — Wed, 17 Nov 2021 13:24:27 GMT

@ITWhisperer thanks this was exactly what I was looking for. But I have another issue now - the full row actually is something like :

Synced accounts: [1,3,5], Deleted accounts: [2,4]

How can I make the regex ignore the groups on the deleted accounts list?

elad — Wed, 17 Nov 2021 13:23:22 GMT

Please see @ITWhisperer answer - it does a group regex for the row and then convert it to multiple events with mvexpand

ITWhisperer — Wed, 17 Nov 2021 13:24:13 GMT

You would have to do two rex

| rex "Synced accounts: \[(?<customer_ids>[^\]]+)\]" | rex field=customer_ids max_match=0 "(?<customer_id>\d+)"

elad — Wed, 17 Nov 2021 15:33:14 GMT

Amazing thanks!