Matching fields from 3 different sources?

[pratibha0610]

Hi all,

Could some please help me with this query. I have 3 different sources from which i want to match the fields. I am 

Source A contains K_user, ID

Source B contains RECID, USER_RESET

Source C contains USER, NAME

i have do the query in 2 steps

1.  Join A and B using ID. (RECID=ID) and get USER_RESET

2. Join the result from step 1 with C. Match K_USER and USER_RESET to get the name from source C. 

if i explain using example

Source A

K_USER  ID

ABN        1

XYZ          2

Source B

RECID   USER_RESET

1.            MNP

3.             IJK

SOURCE C

USER  NAME

ABN  John

XYZ   Mary

MNP Philip

IJK  Cathy

Final result should look like

K_USER | ID | USER_RESET | NAME(K_USER) | NAME(USER_RESET)

ABN 1 MNP John Phillip

Can i achieve  this without using join.

Thanks in Advance!!

[ITWhisperer · Accepted solution]

Sure - just read all the sources at once (keeping track of which events came from which source), then use eventstats to join them

| eval RECID=if(source="A", ID, RECID)
| eventstats values(USER_RESET) as USER_RESET by RECID
| eval USER=if(source="A", K_USER, USER)
| eventstats values(NAME) as NAME by USER
| where source="A" AND isnotnull(USER_RESET)

Note that this works because all your fields have different names from your different sources. If you have the same names across sources, you may have to be a little more creative.