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

[PeterGian]

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

[javiergn]

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,

J

[javiergn]

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,

J

[gcusello]

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