Can inner join be used to join null fields?

[jrodriguezap]

Hi
Someone may have required this case can support me.
I have the following logs

Aug 27 17:42:40 172.24.20.35 type=A sessionid=53f2b45b0526 sender=jorge@domain.com
Aug 27 17:42:40 172.24.20.35 type=A sessionid=53f2b45b0526 subject="regards"
Aug 27 17:42:40 172.24.20.35 type=B sessionid=53f2b45b0526 dst=luis@example.com
Aug 27 17:42:40 172.24.20.35 type=B sessionid=53f2b45b0526 dst=jhon@example.com
Aug 27 17:42:40 172.24.20.35 type=B sessionid=53f2b45b0526 dst=alex@example.com
Aug 27 17:42:45 172.24.20.35 type=B sessionid=53f2b45b0526 deliver=luis@example.com
Aug 27 17:42:53 172.24.20.35 type=B sessionid=53f2b45b0526 deliver=jhon@example.com
Aug 27 17:42:53 172.24.20.35 type=B sessionid=53f2b45b0526 deliver=alex@example.com
And by linking them to an INNER JOIN in SQL as:

index=summary type=A | join type=outer max=0 session_id [ search index=summary type=B ] 
| table session_id  sender subject dst deliver 

I get the following table:

sessionid           sender      subject          dst        deliver
53f2b45b0526  jorge@domain.com  regards  luis@example.com
53f2b45b0526  jorge@domain.com  regards  jhon@example.com
53f2b45b0526  jorge@domain.com  regards  alex@example.com
53f2b45b0526  jorge@domain.com  regards                     luis@example.com
53f2b45b0526  jorge@domain.com  regards                     jhon@example.com
53f2b45b0526  jorge@domain.com  regards                     alex@example.com

That sentence could be used for the following table?

sessionid           sender      subject          dst          deliver
53f2b45b0526  jorge@domain.com  regards  luis@example.com  luis@example.com
53f2b45b0526  jorge@domain.com  regards  jhon@example.com  jhon@example.com
53f2b45b0526  jorge@domain.com  regards  alex@example.com  alex@example.com

[musskopf · Accepted solution]

Have a look on the "transaction" command. I believe would suites you best!

index=temp sourcetype=syslog | transaction fields=sessionid | table sessionid,sender,subject,dst,deliver

will return

sessionid     sender            subject  dst               deliver
53f2b45b0526  jorge@domain.com  regards  alex@example.com  alex@example.com
                                         jhon@example.com  jhon@example.com
                                         luis@example.com  luis@example.com

Cheers!

[jrodriguezap]

thank you very much Musskopf
The latter option INNER JOIN helped me a lot in getting what he wanted, I had to associate in many fields, but if proved.
Best Regards

[musskopf]

The other option is to do a JOIN for each field you need...

index=temp sourcetype=syslog type=B dst=*
| join max=1 type=left sessionod, dst [ search index=temp sourcetype=syslog type=B deliver=* | eval dst=deliver | fields sessionid, dst, deliver ]
| join max=1 type=left sessionid [ search index=temp sourcetype=syslog type=A sender=* | fields sessionid, sender ]
| join max=1 type=left sessionid [ search index=temp sourcetype=syslog type=A subject=* | fields sessionid, subject ]
| table sessionid,sender,subject,dst,deliver

[musskopf]

What sort of stats do you need? For mvexpand, you probably need to "|mvexpand dst | mvexpand deliver | where dst=deliver| table..."

[jrodriguezap]

Hi there
Thanks for the data transaction, it is very good, but in this case I have multivalue fields will prevent getting the stats I need at the end of this association.
Also try to do it, then mvexpand, but nothing.
:(

[HiroshiSatoh]

Do not use STATS?

(EX.)
index=summary (type=A OR type=B) | stats first(sender) as sender,first(subject) as subject,first(dst) as dst,first(deliver) as deliver,dc(type) as type_count by session_id|search type_count=2|fields - type_count

[HiroshiSatoh]

What should I combined to deliver and dst?
Is it okay if values?

index=summary (type=A OR type=B) | stats first(sender) as sender,first(subject) as subject,values(dst) as dst,values(deliver) as deliver,dc(type) as type_count by session_id|search type_count=2|fields - type_count

[jrodriguezap]

Hi HiroshiSatoh
Thanks for your support, look, those commands only show me the first row

sessionid           sender      subject          dst          deliver
53f2b45b0526  jorge@domain.com  regards  luis@example.com  luis@example.com

it may be?