topic Re: Joining Data in Splunk Search

Joining Data

jimjohn — Mon, 28 Sep 2020 16:02:42 GMT

HostA contains employer_code like (A,B,C,D,E,F,G)
HostB contains ER Code like (A,A,B,D,D)

I am trying to join 2 data sources with below query.
host=HostA|join employer_code [search host=HostB| eval "ER Code"=employer_code]

I am not getting result like inner join in SQL.
Can anybody help.Is there any other way to solve this issue rather than join?
Can we achieve this by sub search?

Re: Joining Data

martin_mueller — Wed, 05 Mar 2014 10:39:29 GMT

What's your actual use case?

Working off an existing search only is often futile for finding the best approach.

Re: Joining Data

jimjohn — Wed, 05 Mar 2014 12:55:59 GMT

My SQL will be like this.

select avg(a.field1),count(b.field2)
from HostA a
join HostB b on a.empId=b.ErID
group by a.field,b.field2;

Re: Joining Data

jimjohn — Wed, 05 Mar 2014 12:56:30 GMT

i need to convert above SQL to splunk serarch

Re: Joining Data

wpreston — Wed, 05 Mar 2014 13:10:10 GMT

Try something like this:

host=HostA OR host=HostB ... rest of your search string...
| eval employer_code=if(host="HostB",ErID,employer_code)
| stats avg(field1) count(field2) by employer_code

Join may not be necessary in Splunk and is often an expensive operation. Does this get you closer to what you need?

Re: Joining Data

MuS — Wed, 05 Mar 2014 13:14:29 GMT

or use Splunk DB connect and run the SQL statment as

| dbquery database your SQLfu

if you are more comfortable with SQL