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