Splunk Search
Highlighted

Can inner join be used to join null fields?

Communicator

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)
Highlighted

Re: Can inner join be used to join null fields?

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 typecount by sessionid|search typecount=2|fields - typecount

0 Karma
Highlighted

Re: Can inner join be used to join null fields?

Communicator

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
Highlighted

Re: Can inner join be used to join null fields?

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 typecount by sessionid|search typecount=2|fields - typecount

0 Karma
Highlighted

Re: Can inner join be used to join null fields?

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

Highlighted

Re: Can inner join be used to join null fields?

Communicator

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
Highlighted

Re: Can inner join be used to join null fields?

Builder

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

0 Karma
Highlighted

Re: Can inner join be used to join null fields?

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
Highlighted

Re: Can inner join be used to join null fields?

Communicator

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