How to join two tables?

[jvmerilla]

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 🙂

[mayurr98]

Can you try this :

| savedsearch Cycle_11 | appendcols [| savedsearch Cycle_10]

let me know if this helps!

[jvmerilla]

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.

[FrankVl]

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?

[HiroshiSatoh]

Try this!

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

[jvmerilla]

Hi @HiroshiSatoh,

Why do I have to rename fields?

[jvmerilla]

I tried it but I got this error:

Error in 'rename' command: Wildcard mismatch:

[HiroshiSatoh]

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

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

[493669]

Try this:

| savedsearch Cycle_11 | join ID [| savedsearch Cycle_10]

[jvmerilla]

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

[493669]

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 *