simple correlation

[adomila]

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.

[kristian_kolb]

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

[adomila]

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

[kristian_kolb]

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.

[adomila]

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.

[adomila]

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.

[adomila]

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

[kristian_kolb]

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.

[adomila]

Hi, any updates?

[adomila]

Can splunk handle this?

[adomila]

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

[Ayn]

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.