topic Re: How to match events from 3 different sources? in Splunk Search

How to match events from 3 different sources?

spnewashik — Wed, 19 Jun 2019 05:55:23 GMT

I have one index with events from 3 different sources. I want to match one field of 1st source with other 2 source's events.

Source "A" have two field/column name "Cell" & "Agent". The value of "Cell" is the phone no and it is also for source B & C.
Source "B" have two field/column name "CALLERNO" & "STAFF".
Source "C" have three field/column name "Cell", "CALLEDNO" & "STAFFNO"

Now I want to match the value of "Cell" from source "A" with source "B" (with field "CALLERNO" ) & "C" (with field "CALLEDNO").
Please help me with command, how to get my expected result. I tried with below code but I can get result from two source and can't get from 3 source. How can I match the events among these 3 sources.

index=cim  source=A  |  rename "Cell" as "CALLERNO" | join CALLERNO type=left usetime=true earlier=true    [search index=cim  source=B   earliest=-10m latest=now     | fields _time,CALLERNO,STAFF] | fillnull value="Not found"  | table _time,CALLERNO,Agent,STAFF

Re: How to match events from 3 different sources?

DavidHourani — Wed, 19 Jun 2019 09:13:07 GMT

Hi @spnewashik,

You can do that without using any join whatsoever by using a stats command as follows :

(index=cim source=A) OR (index=cim source=B) OR (index=cim source=C) 
| rename CALLERNO AS Cell CALLEDNO AS Cell
| stats latest(_time) as _time, values(Agent) as Agent, values(STAFF) as STAFF by Cell

Cheers,
David

Re: How to match events from 3 different sources?

FrankVl — Wed, 19 Jun 2019 09:42:51 GMT

How about something like this:

index=cim
| eval Cell = coalesce(Cell,CALLERNO,CALLEDNO)
| stats values(agent) as agent values(STAFF) as caller_staff values(STAFFNO) as called_staff by Cell

Re: How to match events from 3 different sources?

spnewashik — Wed, 19 Jun 2019 11:21:36 GMT

thanks for your reply. actually it doesn't bring my expected result.
below is my actual expected output if the value of Cell (source A) is matched with value of CALLERNO (source B) and value of CALLEDNO (source C). If not match then it will shows "Not-Found" in event field:

time                  Cell         Agent      STAFF       STAFFNO
2019-06-11 09:50:00 12445678        Bob   Not-found     Jon 
2019-06-11 09:46:00 12997865        Alex      Don          Not-found
2019-06-11 09:45:00 12776547        Alice    Alice        Not-found

Re: How to match events from 3 different sources?

spnewashik — Wed, 19 Jun 2019 11:22:09 GMT

actually it doesn't bring my expected result.
below is my actual expected output if the value of Cell (source A) is matched with value of CALLERNO (source B) and value of CALLEDNO (source C). If not match then it will shows "Not-Found" in event field:

time                  Cell         Agent      STAFF       STAFFNO
2019-06-11 09:50:00 12445678        Bob   Not-found     Jon 
2019-06-11 09:46:00 12997865        Alex      Don          Not-found
2019-06-11 09:45:00 12776547        Alice    Alice        Not-found

Re: How to match events from 3 different sources?

DavidHourani — Wed, 19 Jun 2019 11:26:28 GMT

In that case you need to include a fillnull statement as follows :

  (index=cim source=A) OR (index=cim source=B) OR (index=cim source=C) 
 | rename CALLERNO AS Cell CALLEDNO AS Cell
 | stats latest(_time) as _time, values(Agent) as Agent, values(STAFF) as STAFF by Cell
| fillnull value="Not-found"

Re: How to match events from 3 different sources?

spnewashik — Wed, 19 Jun 2019 11:41:10 GMT

the problem is here, the same field name exist in Source-B and Source-C which is "CALLERNO".
I want to match the value of "Cell" from source-A with other two source . I don't care about the value of "CALLERNO" from source-C while i need to match with field "CALLEDNO" of source-C.
So when I rename the both the field "CALLERNO" & CALLEDNO as "Cell" according to your code, it can't match.

Re: How to match events from 3 different sources?

FrankVl — Wed, 19 Jun 2019 11:56:33 GMT

Well, you never mentioned before that source C also has a CallerNO field. What if you move the coalesce items around a bit (and add the fillnull which I indeed forgot):

 index=cim
 | eval Cell = coalesce(Cell,CALLEDNO,CALLERNO)
 | stats latest(_time) values(agent) as agent values(STAFF) as caller_staff values(STAFFNO) as called_staff by Cell
 | fillnull value="Not-found"

Or does source B also have a CALLEDNO? If so, you'll need to use a case statement, to set the Cell value based on the source:

 index=cim
 | eval Cell = case(source="A",Cell,source="B",CALLERNO,source="C",CALLEDNO)
 | stats latest(_time) values(agent) as agent values(STAFF) as caller_staff values(STAFFNO) as called_staff by Cell
 | fillnull value="Not-found"

Re: How to match events from 3 different sources?

DavidHourani — Wed, 19 Jun 2019 12:42:52 GMT

@spnewashik, I see, you should be able to solve this by creating an alias for your fields:
https://docs.splunk.com/Documentation/Splunk/7.3.0/Knowledge/Addaliasestofields
Make sure the alias is the same for all (and only) the fields you want to use for your join lets call it for example cell_final.
Once you have that fields aliases for the three sources, the rest is very easy and your query should look like this :

(index=cim source=A) OR (index=cim source=B) OR (index=cim source=C) 
  | stats latest(_time) as _time, values(Agent) as Agent, values(STAFF) as STAFF by cell_final
 | fillnull value="Not-found"

This will make things much easier for you.

Re: How to match events from 3 different sources?

FrankVl — Wed, 19 Jun 2019 13:29:42 GMT

If you only want to link entries that happen at the same time, try it like this:

  index=cim
  | eval Cell = case(source="A",Cell,source="B",CALLERNO,source="C",CALLEDNO)
  | stats values(agent) as agent values(STAFF) as caller_staff values(STAFFNO) as called_staff by _time,Cell
  | fillnull value="Not-found"

Re: How to match events from 3 different sources?

spnewashik — Thu, 20 Jun 2019 02:50:53 GMT

thanks @DavidHourani..........Field Alias is more convenient in my case. I have created a field alias with the value of Cell (source A), CALLERNO (source B), CALLEDNO (source C) and the final field name is "Contact". The command I applied:

(index=cim source=A) OR (index=cim source=B) OR (index=cim source=C) | fillnull value="not found"| stats latest(_time) as _time, values(Agent) as Agent, values(STAFF) as STAFF, values(STAFFNO) as STAFFNO by "Contact"

Now I am facing one problem with ur command- it matches the data accurately with source B & C but the value of Agent from source A become "Not-found" in the table which should not be, also it shows a value like below :

time                    Contact       Agent     STAFF      STAFFNO
2019-06-11 09:50:00 12445678        Not found   Not-found      Jon 
                                       Jami
2019-06-11 09:46:00 12997865        Alex         Don             Mart
                                                                Not-found

2019-06-11 09:45:00 12776547        Alice       Alice          Not-found

I can't understand what is wrong I have done.
Then I tried this command and it shows accurately what I expected:

index=cim source=A |join Contact type=left    [search  source=B OR source=C | fields _time,Contact,Agent,STAFF,STAFFNO] | fillnull value="not found" | table _time,Contact,Agent,STAFF,STAFFNO

time                    Contact     Agent       STAFF      STAFFNO
2019-06-11 09:50:00 12445678        Mark        Not-found      Jon 
2019-06-11 09:46:00 12997865        Alex         Don           Mart
2019-06-11 09:45:00 12776547        Alice       Alice        Not-found

Thanks David.....your suggestion for creating Field Alias solve my problem. If you have any more suggestion for me please share.

Re: How to match events from 3 different sources?

DavidHourani — Thu, 20 Jun 2019 04:19:35 GMT

Glad to know I could help! Your command looks great.

If you want to use the statscommand instead, this should give you better results:

(index=cim source=A) OR (index=cim source=B) OR (index=cim source=C) | stats latest(_time) as _time, values(Agent) as Agent, values(STAFF) as STAFF, values(STAFFNO) as STAFFNO by "Contact"| fillnull value="not found"

Let me know if it works out for you! And please accept and upvote if its helpful 🙂