Splunk Search

how to perform JOIN with STATS

khannasunil
New Member

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

0 Karma

emiller42
Motivator

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.

0 Karma

emiller42
Motivator

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.

0 Karma

khannasunil
New Member

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

emiller42
Motivator

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try this

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

0 Karma

khannasunil
New Member

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?

0 Karma

linu1988
Champion

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

khannasunil
New Member

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Customer Survey!

If you use Splunk Observability Cloud, we invite you to share your valuable insights with us through a brief ...

Happy CX Day, Splunk Community!

Happy CX Day, Splunk Community! CX stands for Customer Experience, and today, October 3rd, is CX Day — a ...

.conf23 | Get Your Cybersecurity Defense Analyst Certification in Vegas

We’re excited to announce a new Splunk certification exam being released at .conf23! If you’re going to Las ...