Splunk Search

Can inner join be used to join null fields?

jrodriguezap
Contributor

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
Tags (3)
1 Solution

musskopf
Builder

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!

View solution in original post

musskopf
Builder

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
Contributor

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

0 Karma

musskopf
Builder

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

0 Karma

musskopf
Builder

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

0 Karma

jrodriguezap
Contributor

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.
:(

0 Karma

HiroshiSatoh
Champion

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

0 Karma

HiroshiSatoh
Champion

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

0 Karma

jrodriguezap
Contributor

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?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...