Splunk Search

simple correlation

adomila
Explorer

Hi,
Basically, I'm trying to correlate 2 datasources with 2 fields. For example, I have datasource1 and datasource2 then I need to be able to return all field1 with corresponding field2. But I also need to validate if field1 with corresponding field2 exists in datasource2 before it is dispalyed or returned. Finally show in a graph which fields exists in both datasources and which fields do not exists. I tried the ff:

sourcetype=* field1=* field2=2

also tried join and sub query approach but no luck

sourcetype=datasource1 | join field1 [sourcetype=datasource2]

Please point me in the right direction. TIA.

Tags (1)
0 Karma

kristian_kolb
Ultra Champion

You could try a subsearch approach

datasource=xta [search datasouce=mru | fields + cp_num, ref_num] | table cp_num ref_num

The inner search (within the square brackets) will be executed first, and return the fields from mru. Effectively the search will then be (if there are three events in the mru set);

datasource=xta ((cp_num=X AND ref_num=Y) OR (cp_num=Z AND ref_num=Q) OR (cp_num=W AND ref_NUM=Y))

So the result will be the events (or what ever you choose to table) that match on both fields. However there are some limits (configurable) on how many events can be returned from a subsearch, so this may not be optimal.

Hope this works as a start, at least.

/Kristian

0 Karma

adomila
Explorer

I've tried this but its really slow. Are there any other options?

0 Karma

kristian_kolb
Ultra Champion

There is a setting in limits.conf (maxresults I believe). It is configurable, but the default limit is probably there for a reason. You could try to change that.

0 Karma

adomila
Explorer

Many thanks Kristian, I already tried the sub-search approach, I just forgot to mention, it does not work for me as there is a 500k something limitation. Nevertheless, you replied so it means I have convened the problem statement clearly already. Are there any other options/approach available? Or is this considered a splunk limitation? TIA.

0 Karma

adomila
Explorer

Thank you for being patient with my question. Allow me to try again with specific details:


datasource = xta cp_num=9996631244 ref_num=333556144

datasource = xta cp_num=9396631341 ref_num=224556141


datasource = mru cp_num=9996631244 ref_num=333556144

datasource = mru cp_num=9166631243 ref_num=434566143


Basically, I need to result presented with all the cp_num and/with ref_num on xta that matches on the mru datasource. Somtthing like this:

XTA_MRU MATCHES:

cp_num=9996631244 ref_num=333556144


NOT XTA_MRU MATCHES:

cp_num=9396631341 ref_num=224556144

cp_num=9166631243 ref_num=434566143


Please take note that both cp_num and ref_num should be exactly the same match. Kindly let me know if this ok already.Tia.

0 Karma

adomila
Explorer

Please allow me to elaborate or clarify. Something like this; in sql

select cp_num, ref_num from xta and mru where (xta.cp_num = mru.cp_num) AND (xta.ref_num = mru.ref_num)

so this should return all matching cp_num and/with ref_num. I hope this helps clarify...

0 Karma

kristian_kolb
Ultra Champion

As Ayn said, please provide some real events (mask ip-addresses, usernames etc as needed) and some sketch of how you want the results presented.

0 Karma

adomila
Explorer

Hi, any updates?

0 Karma

adomila
Explorer

Can splunk handle this?

0 Karma

adomila
Explorer

Sorry for not being clear. Basically, I'm trying to join 2 datasources by 2 fields. The said 2 fields should be present on each datasource. And those said 2 fields should also exists on the other datasource. In other words; the existence of those said 2 fields should be the joining factor for the 2 datasources. In the end, I should be able to present a list of all existing fields. Something like the ff:

datasource=abc cp_num=(all_values) ref_num=(all_values)

datasource=def cp_num=(all_values) ref_num=(all_values)

  • The cp_num and ref_num should have an exact match on both datasources, Something like

[abc.cp_num = def.cp_num] AND [abc.ref_num = def.ref_num]

I tried join with sub-search

datasource=abc | join cp_num=(all values) ref_num=(all_values) [datasource=def cp_num=(all values) ref_num=(all_values)]
But I'm not so sure about this idea 😞

Btw, I tried to join even with just a single field but I'm getting a limit or max warning/error. Now I'm not sure if this is still feasible?

I hope I have provide a better detail . . . ?

0 Karma

Ayn
Legend

I think you need to give us more specific details and log samples, because at least I have troubles understanding what exactly you want to achieve and how.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...