topic Re: how to perform JOIN with STATS in Splunk Search

how to perform JOIN with STATS

khannasunil — Mon, 28 Sep 2020 17:03:41 GMT

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

Re: how to perform JOIN with STATS

linu1988 — Mon, 14 Jul 2014 11:32:26 GMT

Hello Sunil,
You can do by renaming the fields.

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

|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

Re: how to perform JOIN with STATS

khannasunil — Mon, 14 Jul 2014 12:49:05 GMT

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

Re: how to perform JOIN with STATS

somesoni2 — Mon, 14 Jul 2014 13:09:17 GMT

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

Re: how to perform JOIN with STATS

emiller42 — Mon, 14 Jul 2014 14:59:10 GMT

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.

Re: how to perform JOIN with STATS

khannasunil — Thu, 17 Jul 2014 16:59:17 GMT

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?

Re: how to perform JOIN with STATS

somesoni2 — Thu, 17 Jul 2014 17:29:49 GMT

Try this

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

Re: how to perform JOIN with STATS

emiller42 — Thu, 17 Jul 2014 17:36:11 GMT

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.

Re: how to perform JOIN with STATS

somesoni2 — Thu, 17 Jul 2014 18:48:15 GMT

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

Re: how to perform JOIN with STATS

khannasunil — Fri, 18 Jul 2014 06:02:31 GMT

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.

Re: how to perform JOIN with STATS

emiller42 — Fri, 18 Jul 2014 22:15:09 GMT

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.