Joining Data

[jimjohn]

Hi

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?

[wpreston]

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?

[MuS]

or use Splunk DB connect and run the SQL statment as

| dbquery database your SQLfu

if you are more comfortable with SQL

[jimjohn]

i need to convert above SQL to splunk serarch

[jimjohn]

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;

[martin_mueller]

What's your actual use case?

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