topic Re: How to join two sources with common one common field ? in Splunk Search

How to join two sources with common one common field ?

zacksoft_wf — Wed, 01 Dec 2021 11:17:22 GMT

I have sourcetype A that has info about service_accounts such as name, AU, email , full_name, manager_name.
But some of the events in source A, do not contain the field email , manager_name, full_name field. In those cases I have to look into another index and sourcetype, say B to fetch those data. AU is the common field name in both . Can we join the data, without having to use 'join' for performance issue ?

Re: How to join two sources with common one common field ?

isoutamo — Wed, 01 Dec 2021 11:21:13 GMT

this is doable and even preferred way to join those without join. Here is couple of links how to do it and why those are better ways.

https://conf.splunk.com/files/2020/slides/TRU1761C.pdf
Here is another answer about replacing joins https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi... check woodcock’s answer with examples.

r. Ismo

Re: How to join two sources with common one common field ?

zacksoft_wf — Wed, 01 Dec 2021 13:00:10 GMT

I am trying to make it work. Here is my SPL, and this one works and gives me the expected results. and I am having trouble converting it to a stats .

===================================================================
`index=bayseian` account_type="Service Account" OR cred="False" OR type=W
| join type=left au
[ search index=alts sourcetype=auxilary
| fields service_account_id,service_account_name,au,owner_elid,au_owner_name,au_owner_email ]
| eval pwd_expires=if(nopassexpire==1, "True", "False"), account_type=if(type=="S", "Service Account", account_type)
| eval elid=coalesce(elid,owner_elid)
| eval au_owner_email=coalesce(email_address,au_owner_email)
| eval au_owner_name=coalesce(full_name,au_owner_name)
| eval service_accout_name=coalesce(cn,service_account_name)
| eval service_account_id=coalesce(service_account_id,app_id)
| rename acct_name as user, account_type as type
| eventstats dc(sourcetype) as dc_st
| where dc_st>1
| eval user=lower(user)
| dedup user
| rex field=user "[^:]+:(?<user>[^\s]+)"
| table user type pwd_expires is_interactive service_account_id service_account_name au au_owner_name job_title au_owner_email owner_elid manager_name lob
| eval _key=user+".key"
==============================================================

Any help please?

Re: How to join two sources with common one common field ?

Sukisen1981 — Wed, 01 Dec 2021 13:47:56 GMT

Are you sure you want to use a join? what you need is something like this -

(index=bayseian OR index =alt)|stats values (field1, field2) by AU

Here is what I want you to do, limit your query first to 2 fields (field1 and field2 are just placeholders) and assuming AU is your common field , retrofit the above the search and run it. See the output of the above query, I am sure you will get what I am hinting at. If you manage to get all the field values from the above query,the rest of the evals, regexes can be applied later, try it out and let us know

Re: How to join two sources with common one common field ?

zacksoft_wf — Wed, 01 Dec 2021 15:51:07 GMT

In my case , I have to search for fields in index bayseian, if the field/fields is not found (means no value), I have to check the other index, where that field might be there but with a different field name. And there is one common field between these two datasources to correlate .

Re: How to join two sources with common one common field ?

johnhuang — Wed, 01 Dec 2021 18:42:07 GMT

Assuming b_name, b_email, b_manager_name, b_full_name are the fieldnames for index "B".

(index=A OR index=B)
| table AU, name, email, full_name, manager_name, b_name, b_email, b_manager_name, b_full_name
| stats MAX(*) AS * BY AU
| eval name=COALESCE(name, b_name)
| eval email=COALESCE(email, b_email)
| eval full_name=COALESCE(full_name, b_full_name)
| eval manager_name=COALESCE(manager_name, b_manager_name)
| table AU, name, email, full_name, manager_name

Re: How to join two sources with common one common field ?

zacksoft_wf — Thu, 02 Dec 2021 08:33:39 GMT

@johnhuang - I tried with Stats(value) and sharing you the screenshot. Some of them are multivalue fields, I believe thats why the remaining fields are not populating . Stats(max) removes the multivalue with one value , but then again , rest of the fields are not populating, and also I would want each multivalue field to become separate rows. Please find the attachment and suggest.

Re: How to join two sources with common one common field ?

Sukisen1981 — Thu, 02 Dec 2021 08:51:54 GMT

the field 'elid' which is coming empty, is that an index field? Also can you check if elid field is missing for all rows or only for some rows

Re: How to join two sources with common one common field ?

zacksoft_wf — Thu, 02 Dec 2021 09:00:02 GMT

@Sukisen1981 Not an indexed field. Just a regular field. And as i check now elid and others are not empty for all rows.

Re: How to join two sources with common one common field ?

Sukisen1981 — Thu, 02 Dec 2021 09:12:13 GMT

@zacksoft_wf Sorry, I meant to ask the field elid, it is present in one of the indexes, right? All we have done in the query is make a table of the needed fields followed by a stats values(*). But the bigger question is , and I was suspecting the missing elid is not an issue with the stats. Is it possible for you to check(say for the first row in your screen shot where AU=0), if elid is indeed present in either indexes for AU=0?

PS- I think we are near to the actual solution 🙂 its just that we need to have a better understanding of the fields in your index

Re: How to join two sources with common one common field ?

zacksoft_wf — Thu, 02 Dec 2021 10:07:09 GMT

I tried @johnhuang 's solution with a change , it populates but not quite the way i wanted.

=========

(`index=A) OR (index=B)
| eval pwd_expires=if(nopassexpire==1, "True", "False"), account_type=if(type=="S", "Service Account", account_type)
| table is_interactive,account_name,cn,au,acct_name,elid,full_name,full_name,email_address,manager_name,service_account_name,job_title,lob,pwd_expires,service_accout_name,account_type,service_account_id,service_account_id,owner_elid,au_owner_name,au_owner_email
| eventstats MAX(*) AS * BY au
| eval elid=coalesce(elid,owner_elid)
| eval au_owner_email=coalesce(au_owner_email,email_address)
| eval au_owner_name=coalesce(au_owner_name,full_name)
| eval service_accout_name=coalesce(service_account_name,cn)
| eval service_account_id=coalesce(service_account_id,app_id)
| rename acct_name as user, account_type as type| eval user=lower(user)

| table user type pwd_expires is_interactive service_account_id service_account_name au au_owner_name job_title au_owner_email elid manager_name lob

=========

I changed stats to eventstats , it populates value, but i think because of the usage of MAX() function I am not seeing multiple service_account_id or service_acount_names asociated to each au. I am just seeing one entry. But in real the multivalue fields should be split into different rows , but now MAX() messes things up, and stats values() is creating some multivalue fileds !!!! stuck !!

Re: How to join two sources with common one common field ?

zacksoft_wf — Thu, 02 Dec 2021 13:41:11 GMT

for every 'au', there are multiple service_account_name, service_account_id, user. hence a multivalue field creation while using eventstats using max() function is just giving me one value of service_account_name, service_account_id, user per each 'au' . And that's incorrect. If I could see all the multivalue fields split , like multiple au rows for each combination of its associated service_account_name etc field,that would be helpful. 'cos in the end I intent to write this result into a kvstore lookup.

Re: How to join two sources with common one common field ?

Sukisen1981 — Thu, 02 Dec 2021 14:49:38 GMT

@zacksoft_wf eventstats wont give you the correct answer, eventstats over a field will return a function(like max or min) of all the events. Coming back to my question , were you able to see why some field values for eild was missing and some came in the stats values(*) query? If we can fix that our job is almost done, for then we just need to use the mvzip and(or) mvexpand functions to get the desired output.

Can you investigate a bit more on the empty/non empty values for eild?

Re: How to join two sources with common one common field ?

zacksoft_wf — Thu, 02 Dec 2021 14:57:35 GMT

@Sukisen1981 "elid" is present for some events and absent for some.

Re: How to join two sources with common one common field ?

Sukisen1981 — Thu, 02 Dec 2021 15:00:00 GMT

I got that, what i am asking is can you see any pattern or condition under which elid is missing and elid is coming? I mean to say, is it possible to see the raw events which are causing empty elids and events which are returning elid multivalues? Maybe something to do with the way events are being logged in the indexes

Re: How to join two sources with common one common field ?

zacksoft_wf — Thu, 02 Dec 2021 15:06:29 GMT

@Sukisen1981 Actually , elid is not a multivalue field. user, service_account_name, service_account_id are.
elid is present is some events (not all), and that is okay for us. There is no pattern .

Re: How to join two sources with common one common field ?

Sukisen1981 — Thu, 02 Dec 2021 15:08:11 GMT

if that's ok then going back to your original screen shot if we can get one row for each multivalue field, does that solve your requirement?

Re: How to join two sources with common one common field ?

zacksoft_wf — Thu, 02 Dec 2021 15:32:24 GMT

@Sukisen1981 yes

Re: How to join two sources with common one common field ?

johnhuang — Thu, 02 Dec 2021 22:28:55 GMT

Instead of MAX, you can use VALUES to list out the multivalue fields.

VALUES(*) AS *

Re: How to join two sources with common one common field ?

johnhuang — Thu, 02 Dec 2021 23:45:02 GMT

Yes that's easy to do. Instead of MAX, use VALUES(*) AS *.

Then run this command for each mutivalued field that you want to expand/flatten.

| mvexpand field_name_1
| mvexpand field_name_2