topic Re: Join 2 tables with different rows but same columns in Splunk Search

How to join two tables with different rows but same columns?

PeterGian — Thu, 23 Mar 2023 14:09:54 GMT

Hello amazing community!

I'm now stuck with a problem that most probably has a really simple solution 🙂

I have a table that is generated every night with a batch process, I would need to merge the "today" table with the "yesterday" table and see what is different.

This is an example just to keep things easy:

Yesterday table:

A	Old
B	Old
C	Old
D	Old
E	Old
Z	Old

Today Table:

A	New
B	New
C	New
D	New
E	New
F	New

Expected result:

A	Old	New
B	Old	New
C	Old	New
D	Old	New
E	Old	New
Z	Old	null
F	null	New

Any idea about how I can achieve this?
Many thanks in advance

Re: Join 2 tables with different rows but same columns

gcusello — Thu, 09 Mar 2023 09:30:15 GMT

Hi @PeterGian,

could you share your searches?

in the meantime, see the "timewrap" command (https://docs.splunk.com/Documentation/SCS/current/SearchReference/TimewrapCommandOverview) that should solve your request.

Ciao.

Giuseppe

Re: Join 2 tables with different rows but same columns

javiergn — Thu, 09 Mar 2023 10:15:22 GMT

Hi @PeterGian,

Let's say your column names are FieldA and FieldB, in that case your spl could look like the following:

index=foo sourcetype=bar earliest=-1d@d | bucket span=1d _time | eval Yesterday = if(relative_time(now(), "@d") > _time, FieldB, null()), ```this is yesterday``` Today = if(relative_time(now(), "@d") <= _time, FieldB, null()) ```this is today``` | stats values(Yesterday) as Yesterday, values(Today) as Today by FieldA | fillnull value=NULL

Note I have not tested the SPL syntax.

Hope it helps, if not please post more details about your question.

Regards,

Re: Join 2 tables with different rows but same columns

javiergn — Thu, 23 Mar 2023 09:28:29 GMT

Hi @PeterGian please let us know if any of the answers provided helped and therefore we can close the topic, or you need more assistance.

Thanks,