how to perform JOIN with STATS

[khannasunil]

Hi I am having two indexes
INDEX_A
with following fields : name,packets,sourceip
and INDEX_B
has following fields : category,classification,ipaddress

In the above two indexes fields sourceip and ipaddress both contains the ipadresses(ex. 1.1.1.1 , 192.12.11.124 etc..) . So you can see here sourceip and ipaddress are the common fields .Now i want to perform join over these two indexes with the help of STATS not with JOIN because JOIN has performance issues.

I'll be very thankful if someone can provide the solution on same.

Thanks

[emiller42]

Here's how I would tackle this with stats. (This assumes each ip address can only appear in each data set once)

index=A OR index=B | rename sourceip as ipaddress | stats count first(name) as name first(packets) as packets first(category) as category first(classification) as classification by ipaddress | where count>1

The where filter will omit anything which only shows up in both datasets. (You could also filter on a specific field being null, such as |where isnull(category) for example.

[emiller42]

What is the timeframe of your search? The section without an explicit earliest/latest will be bound by the timeframe you select in the UI.

[khannasunil]

Hi emiller,

Providing earliest and latest with index ex. (index=A earliest=-1d@d latest=@d) OR (index=B) is not working for me.

can you elaborate more on same.

[somesoni2]

Somehow this didn't work for me earlier forcing me to use |multisearch. It works fine now. stumped...

[emiller42]

Multisearch isn't necessary. Just use simple boolean operators.

(index=A earliest=-1d@d latest=@d) OR (index=B) | ...

modify earliest/latest requirements as needed.

[somesoni2]

Try this

|multisearch [search index=A earliest=-25h@h][search index=B] | ....rest of the search

[khannasunil]

Thanks emiller.

Query :: How can i handle two different time range in this search??

Ex.
I want to perform search on where index A result for last 24 hrs and index B for All time. How do i write earliest/latest two time here in this single search?

[linu1988]

Hello Sunil,
You can do by renaming the fields.

index=A OR index=B|rename ipaddress as sourceip|stats  avg(packets) by sourceip

OR

|multisearch [search index=A|table name,packets,sourceip][search index=B | table category,classification,ipaddress|rename ipaddress as sourceip]|stats  avg(packets) by sourceip

Thanks,
L

[somesoni2]

Basically do you want to know common IP Address among two sources or looking of other information as well?

[khannasunil]

Thanks for your quick response linu.... but it does not solve my problem ... let me elaborate this more ..

The sourceIP has following values ex.
1.1.1.1,12.12.11.1,123.211.1.1
AND ipaddress field has following value ex.
1.1.1.1,191.121.121.1

You can see above there is only one value "1.1.1.1" WHICH is common for both fields sourceip and ipaddress.
I want result which gives me only this value not all(like natural join).

How can i achieve this??

Waiting for your reply !!

Thanks