Left outer join in splunk

[asarolkar]

I have a sourcetype A - which has a field by the name of MOD_SN
I have another index B ( I can only use the name of the index and not the name of the sourcetype for the second table) which has a field by the name of MOD_SN as well.

I am trying to do a left outer join on MOD_SN - the Great Goal is to be able to get every MOD_SN in sourcetype A which does NOT match a MOD_SN in B

sourcetype = "A" MOD_SN=* | dedup MOD_SN | fields MOD_SN | 
join type=outer MOD_SN [search index="B" | where isnull(MOD_SN) | fields MOD_SN ] | table MOD_SN

Does this look ok ????

I am trying to recreate what can be done in sql in splunk and I actually tried doing a subsearch but that was not beneficial

[asarolkar]

This search did not work at all :

sourcetype=A MOD_SN=* NOT [ search index=B MOD_SN=* | return 10000 MOD_SN ] | dedup MOD_SN | table MOD_SN

[gkanapathy]

Splunk isn't a relational database, so while it's possible to do a left outer join, it's not a good way to implement it using the join command. The most efficient way in Splunk to get the results you want is probably:

sourcetype=A MOD_SN=* NOT [ search index=B MOD_SN=* | return 10000 MOD_SN ] | dedup MOD_SN | table MOD_SN

which will work as long as there are no more than 10000 distinct MOD_SN values in B. Unfortunately, this is simply not a terribly easy result to compute. Another way that would work (and again, be more efficient than the join command) is:

sourcetype=A OR index=B MOD_SN=* 
| eval s=case(index=="B","b",sourcetype=="A","a",null())
| chart count by MOD_SN,s
| where a > 0 AND b==0

There are other possible queries, but selecting which one is best depends on the approximate expected sizes of A and B and the number of distinct values of MOD_SN in each, much in the way that a SQL database may generate table statistics to help the optimizer choose a query plan.

[asarolkar]

What are lowercase a and lowercase b ?

I dont get this segment

| eval s=case(index=="B","b",sourcetype=="A","a",null())
| chart count by MOD_SN,s
| where a > 0 AND b==0