I have one index which have events from 3 different sources (A, B & C). The value of CELL, CALLERNO & CALLEDNO are the phone no. The data of the sources are as below:
Source:A
time CELL Agent
2019-06-11 09:50:00 12445678 Bob
2019-06-11 09:46:00 12997865 Alex
2019-06-11 09:45:00 12776547 Alice
Source:B
time CALLERNO STAFF
2019-06-11 10:50:00 12445634 Jon
2019-06-11 11:46:00 12997897 Alex
2019-06-11 02:45:00 12776547 Alice
Source:C
time CALLERNO STAFFNO CALLEDNO
2019-06-11 08:50:00 12445678 Jon 12445634
2019-06-11 07:46:00 12737865 July 12453255
2019-06-11 06:45:00 12776547 Alice 12997865
I want to match the value of "CELL" from source "A" with the value of "CALLERNO" (source B) & "CALLEDNO" (source C).
Here one condition for matching, if the value of "CALLERNO" (source B) & "CALLEDNO" (source C) are within past 10min from the value of "CELL" (source A).
If match the condition, then it will show me the value of "STAFF" name from source B and "STAFFNO" from source C in one table along with the "CELL" & "Agent" value of source "A".
If not match the condition then it will show "Not match"
Here one problem I faced that is the same field name of source B & C - "CALLERNO". I don't care value of it from source C.
For that, I create a single Field Alias named as "CONTACT" with value of "CELL" (source-A), "CALLERNO" (source-B) & "CALLEDNO" (source-C)
I applied below command which perfectly shows only for matching without my time condition. Here I just test whether matching is working or not and the time condition is not tested here:
index=cim source=A |join Contact type=left [search source=B OR source=C| fields _time,Contact,Agent,STAFF,STAFFNO] | fillnull value="not match" | table _time,Contact,Agent,STAFF,STAFFNO
For matching the time condition (10min ), I applied the below command which doesn't show me expected result:
index=cim source=A |join Contact type=left usetime=true earlier=true [search source=B OR source=C earliest=-10m latest=now| fields _time,Contact,Agent,STAFF,STAFFNO] | fillnull value="not match" | table _time,Contact,Agent,STAFF,STAFFNO
Please note that it is working perfectly if I join only two source instead of three sources. Please help me.
The expected output I mention below
Expected Output:
time CONTACT Agent STAFF STAFFNO
2019-06-11 08:50:00 12445678 Jon Jon Not-match
2019-06-11 07:46:00 12737865 July Adam July
2019-06-11 06:45:00 12776547 Alice Not-match Jud
... View more