topic Re: How to join two tables? in Splunk Search

How to join two tables?

jvmerilla — Thu, 08 Feb 2018 03:23:15 GMT

Hi All,

I have a search:
| savedsearch Cycle_11 | append [| savedsearch Cycle_10]

with the results:

The result I want is something like this:

What do I need to do to get the result I want?
I have tried appendcols but the results is somehow messed up.

Note:
Both savedsearch returns more than 30,000 results.

I hope someone can help me with this.

Thanks in advance and Best Regards 🙂

Re: How to join two tables?

493669 — Thu, 08 Feb 2018 03:36:56 GMT

Try this:

| savedsearch Cycle_11 | join ID [| savedsearch Cycle_10]

Re: How to join two tables?

jvmerilla — Tue, 29 Sep 2020 17:58:56 GMT

I already tried join but what happen was that it duplicates the "Skill 12 from Field_1(CYCLE 10) and the 2 from Field_2(CYCLE 10).

It looks something like this:

ID Name Field_1(CYCLE 10) Field_2(CYCLE 10) Field_1(CYCLE 11) Field_2(CYCLE 11)
123 James Skill 11 0 Skill 11 0
123 James Skill 12 2 Skill 12 2
123 James Skill 12 2 Skill 13 0

Re: How to join two tables?

HiroshiSatoh — Thu, 08 Feb 2018 06:05:30 GMT

Try this!

| savedsearch Cycle_11 | append [| savedsearch Cycle_10]
|rename Field_1* as  Field_1
|stats  latest(*) AS * BY ID,Name,Field_1

Re: How to join two tables?

jvmerilla — Thu, 08 Feb 2018 08:43:46 GMT

Hi @HiroshiSatoh,

Why do I have to rename fields?

Re: How to join two tables?

jvmerilla — Thu, 08 Feb 2018 08:47:16 GMT

I tried it but I got this error:

Error in 'rename' command: Wildcard mismatch:

Re: How to join two tables?

FrankVl — Thu, 08 Feb 2018 08:59:59 GMT

I'm a bit puzzled at what your (functional) end goal is here. Might be good to explain that (perhaps also rethink it yourself), to get some good suggestions on how to accomplish it.

Glueing tables together like this does not make a whole lot of sense to me. But if you really need that, we would at least need a definition of how the tables should be glued together. In other words: how should splunk decide which rows from one table to merge with rows from the other table?

Re: How to join two tables?

mayurr98 — Thu, 08 Feb 2018 09:04:11 GMT

Can you try this :

| savedsearch Cycle_11 | appendcols [| savedsearch Cycle_10]

let me know if this helps!

Re: How to join two tables?

jvmerilla — Thu, 08 Feb 2018 09:41:26 GMT

HI @mayurr98,

I already tried appendcols but the problem is that the result is messed up.
The alignment of some of the fields is wrong because there are some fields with no values.

Re: How to join two tables?

HiroshiSatoh — Tue, 29 Sep 2020 18:02:02 GMT

It was a mistake. "rename" is for use with By.

Re: How to join two tables?

493669 — Thu, 08 Feb 2018 10:39:43 GMT

try this:

| savedsearch Cycle_11 | append [| savedsearch Cycle_10]|eval "Field_1"=coalesce('Field_1(CYCLE 11)','Field_1(CYCLE 10)')|stats values(*) as *  BY Field_1|table ID Name *