How to join two searches based on two different formatted fields

[sangs8788]

I have an index which contains field - TXN_ID = "24, 25 "
index=index1 TXN_ID ="24,25"

I have another event in different index which has field - ID = 25
index=index2 ID=25

How do i combine both these two queries to get the other details from Index1 ? I know we have to use join. But since the index1 field value contains comma separated value, i am not very sure on how to form the query. Can someone help me on this.

[harishalipaka]

hi @sangs8788

index=index1 TXN_ID ="24,25" | makemv delim="," TXN_ID |mvexpand TXN_ID|rename TXN_ID  as ID |join ID [search index=index2 ID=25 ]

[kamlesh_vaghela]

Try this:

index=index1 TXN_ID ="24,25" | eval ID = split(TXN_ID,",") | mvexpand ID | join ID [  search index=index2 ID=25 ]

Sample:

| makeresults | eval TXN_ID ="24,25" | eval ID = split(TXN_ID,",") | mvexpand ID | join ID [ | makeresults | eval ID = 24 | table _time ID]