topic simple correlation in Splunk Search

simple correlation

adomila — Tue, 14 May 2013 13:57:24 GMT

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.

Re: simple correlation

Ayn — Tue, 14 May 2013 19:52:48 GMT

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.

Re: simple correlation

adomila — Mon, 28 Sep 2020 13:54:13 GMT

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 . . . ?

Re: simple correlation

adomila — Thu, 16 May 2013 14:33:18 GMT

Can splunk handle this?

Re: simple correlation

adomila — Sun, 19 May 2013 14:45:49 GMT

Hi, any updates?

Re: simple correlation

kristian_kolb — Mon, 20 May 2013 06:48:24 GMT

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.

Re: simple correlation

adomila — Mon, 28 Sep 2020 13:56:21 GMT

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.

Re: simple correlation

adomila — Mon, 28 Sep 2020 13:56:57 GMT

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...

Re: simple correlation

kristian_kolb — Tue, 21 May 2013 16:09:42 GMT

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

Re: simple correlation

adomila — Wed, 22 May 2013 14:52:38 GMT

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.

Re: simple correlation

kristian_kolb — Fri, 24 May 2013 19:42:05 GMT

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.

Re: simple correlation

adomila — Mon, 17 Jun 2013 01:27:32 GMT

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