SQL Query :
SELECT A.a0,A.a1, A.a2, A.a3 FROM TableA AS A, TableB B
WHERE (A.a1=B.b OR A.a2=B.b OR A.a3=B.b)
Splunk search : ???
index=A | fields a0,a1,a2,a3 | join b [search index=B | fields b]
How can i convert it?
Although this is not what new users expect, Splunk searches that are analogous to SQL joins usually dont need an actual
join command. You can usually do these things with a simple OR, sometimes sprinkled with a little
eval to normalize things. In fact there are lots of great reasons not to use the
If you were just joining A.a1 to B.b, you'd use searches like this:
index=A OR index=B | eval myNormalizedIdField=if(index=="A",a1,b) | stats values(someFieldFromA) last(someFieldFromB) sum(someNumericField) by myNormalizedIdField
index=A OR index=B | eval myNormalizedIdField=if(index=="A",a1,b) | transaction myNormalizedIdField
But for your specific example where you want to join A.a1 to B.b, OR A.a2 to B.b OR A.a3 to B.b, then you'll need a little more search language to normalize. I think it'll look more like this:
(index=A OR index=B) | eval myNormalizedIdField=if(index=="A",a1+"-"+a2+"-"+a3,b) | makemv delim="-" field="myNormalizedIdField" | transaction myNormalizedIdField
or replace that
stats, depending on what you're trying to do with the joined set.