topic Re: How to write a recursive search to build a tree structure? in Splunk Search

How to write a recursive search to build a tree structure?

unchura — Mon, 05 Dec 2016 17:52:27 GMT

I have a csv index imported in Splunk and it represents static pairs "child-account" structure i,e:

account,parent
126,783
125,783
124,783
123,783
321,555
555,777
789,777
999,222
783,222
777,222
222,111
111,0

I need to build a search that would bring back specific branch of the tree structure based on the given parent/branch name i.e

Search for "783" would bring a table result that looks like this:

account,parent
126,783
125,783
124,783
123,783
783,0

or if a search for "777" would bring only children and all below successors, i.e:

account,parent
321,555
555,777
789,777
777,0

Ideally, I also need a way to search and bring resulting ancestors for any given child i,e:
when searching for "777", that would bring:

account,parent
777,222
222,111
111,0

Thank you in advance!

Re: How to write a recursive search to build a tree structure?

niketn — Mon, 05 Dec 2016 19:09:52 GMT

I am not able to correlate the pattern of all your three examples. Do you want single search to do all three? or they are three different scenarios you want to capture?

In any case here is the search string for first case of 783 example (I have used my own input lookup csv file similar to what you have provided and searchField is hard-coded as 783 which you can have a input Dashboard form)

 | inputlookup account_tree | eval searchField=783 |where account=searchField OR parent=searchField | eval parent=if(parent==searchField,parent,0) | fields - searchField

Following is the output:
account parent
126 783
125 783
124 783
123 783
783 0

Re: How to write a recursive search to build a tree structure?

unchura — Mon, 05 Dec 2016 19:24:58 GMT

this is great working one, however it unable to retrieve all the children recursively, ie. when search for "777" it would bring only 1 level of children but not their children, i.e:

account,parent
555,777
789,777
777,0

missing 321,555

Re: How to write a recursive search to build a tree structure?

maciep — Tue, 06 Dec 2016 01:30:46 GMT

There may be a way, but I don't think Splunk is really built for this kind of iteration/recursion. I don't suppose there is a depth limit to this tree, is there?

Here's an example that starts to build the whole tree out, but I'm not sure if there's a way (maybe with foreach or map) to actually build it out to completion.

| inputlookup account_child.csv 
| table parent account 
| join type=left max=0 account 
    [| inputlookup account_child.csv 
    | rename account as account2 
    | rename parent as account] 
| join type=left max=0 account2 
    [| inputlookup account_child.csv 
    | rename account as account3 
    | rename parent as account2] 
| join max=0 type=left account3 
    [| inputlookup account_child.csv 
    | rename account as account4 
    | rename parent as account3]

Re: How to write a recursive search to build a tree structure?

niketn — Tue, 06 Dec 2016 04:12:03 GMT

Could you please confirm whether you want single query to do all three or separate queries will work?

Identifying single child and all parents would be possible as two separate queries if that works for you.

Re: How to write a recursive search to build a tree structure?

unchura — Thu, 08 Dec 2016 15:52:43 GMT

It worked and exactly what I looking for. There is no depth limit, but at least its predictable value and I do a finite amount of iterations that would cover the task.. Thanks!