(I am reposting this question from email, with permission from the person who emailed) I need to basically join 3 indexes where the ‘join’ info is in a 4th index. The 3 former indexes have around 50000 entries while the 4th index around 500000. The fields in the indexes are: Indexes containing the data: Index A, B, C, …: name status id Index containing relationships: Index REL: id parent child The parent and child values in REL match the id value in A, B and C. And also note that the id values in A, B and C never collide, in other words the "single id space" of the REL events has no overlaps across the entities in A, B, C From A to B is normally a 1 to many relation, and from B to C is a many-to-many relation. The specific use case here is that A represents Applications, B represents Deployments of those applications, and C represents Servers implementing those deployments. A server can be used for several deployments hence the many-to-many relation here. The obvious way would be to do something like: index=A
| rename id as A_id, name as Aname, status as Astatus
| join max=0 type=left A_id
[ | search index=REL | rename parent as A_id, child as B_id ]
| join max=0 type=left B_id
[ | search index=B
| rename is as B_id, name as Bname, status as Bstatus ]
| join max=0 type=left B_id
[ | search index=REL | rename parent as B_id, child as C_id ]
| join max=0 type=left C_id
[ | search index=C
| rename id as C_id, name as Cname, status as Cstatus ]
| table Aname Astatus Bname Bstatus Cname Cstatus This, of course, fails miserably because the join only returns 10000 results while the REL index has 400000 events… I can rewrite the first join as: index IN(A REL)
| eval parent=if(index=REL, parent, id),
child =if(index=REL, child, id)
| stats values(name) as Aname values(statu) as Astatus values(child) as childs
by parent
| table Aname Astatus childs But I’m at a loss how to squeeze in the other indexes and relation… And I also have some hope that there's a way to avoid join entirely. UPDATE: Here is a run-anywhere search to fabricate some sample input rows | makeresults
| fields - _time
| eval data="1,A,appl1,,;2,A,appl2,,;3,D,depl1,,;4,D,depl2,,;5,D,depl3,,;6,S,serv1,,;7,S,serv2,,;8,S,serv3,,;9,S,serv4,,;10,R,,1,3;11,R,,2,4;12,R,,2,5;13,R,,3,6;14,R,,4,7;15,R,,5,8;16,R,,5,9",
data=split(data, ";")
| mvexpand data
| rex field=data "(?<sys_id>[^,]*),(?<type>[^,]*),(?<name>[^,]*),(?<parent>[^,]*),(?<child>[^,]*)"
| fields sys_id type name parent child
| eval parent=if(parent=="",null(),parent), child=if(child=="",null(),child) And the desired output, is rows that map app1 to depl1 and serv1 and that map app2 to depl2,depl3 and serv2,serv3,serv4
... View more
