Hi
I have a data set with parent ID and child ID in a same table.
I am looking for a search that produce the following.
A sample data:
p c
--- ---
111 222
222 333
111 444
555 666
and, the output I want to get is :
p c
--- ---
111 222,333
111 444
555 666
I was looking at the search documentation and find selfjoin command, but I still can not get it work..
Any comment would be appreciated!
Thank you in advance.
The solution given by somesoni2, I am afraid, is not recursive. Though not a completely recursive one,
I have managed to come up with a decent solution as follows.
You need repeat a line (n - 2) times in order to get n-deep tree from parent-child pairs.
I needed to start from the end nodes instead of arbitrary nodes
in order to avoid (partially) duplicate branches.
(Please pardon my formatting below. This is my first post.)
sourcetype=csv
| join type=left c [search sourcetype=csv | rename c as c2 | rename p as c ] | where isnull (c2)
| eval t=p." -> ".c | eval r=p | eval e=c | table t, r, e
| eval k=r | join type=left k [search sourcetype=csv | rename c as k ] | eval t=if(isnull(p), t, p." -> ".t) | eval r=p | table t, r, e
| eval k=r | join type=left k [search sourcetype=csv | rename c as k ] | eval t=if(isnull(p), t, p." -> ".t) | eval r=p | table t, r, e
| eval k=r | join type=left k [search sourcetype=csv | rename c as k ] | eval t=if(isnull(p), t, p." -> ".t) | eval r=p | table t, r, e
| sort t | table t | outputcsv "result.csv"
p: parent
c: child
c2: temporary child
r: root
k: (join) key
e: end
t: tree
# --------------------
# [Initial Data Processing]
# --------------------
sourcetype=csv
| join type=left c [search sourcetype=csv | rename c as c2 | rename p as c ] | where isnull (c2)
| eval t=p." -> ".c | eval e=c | eval r=p | table t, r, e
# --------------------
| join type=left c [search sourcetype=csv | rename c as c2 | rename p as c ] | where isnull (c2)
# (1) Leave only parent-child pairs where the child is an end node
| eval t=p." -> ".c | eval r=p | eval e=c | table t, r, e
# (2) Set p(arent) to r(oot), c(hild) to e(nd), and output t(ree), r(oot), e(nd)
# --------------------
# [Repeatable Part]
# --------------------
| eval k=r | join type=left k [search sourcetype=csv | rename c as k ] | eval t=if(isnull(p), t, p." -> ".t) | eval r=p | table t, r, e
# You repeat this line n - 2 times to get n-deep tree
# --------------------
| eval k=r
# (1) Set r(oot) to k(ey)
| join type=left k [search sourcetype=csv | rename c as k ]
# (2) Join the output with the csv with c(hild) renamed as k(ey) based on k(ey)
| eval t=if(isnull(p), t, p." -> ".t)
# (3) Add p(arent), " -> " in front of t(ree) if p(arent) is not null
| eval r=p | table t, r, e
# (4) Set p(arent) to r(oot) and output t(ree), r(oot), e(nd)
# --------------------
# --------------------
# [Finishing Touch]
# --------------------
| sort t | table t | outputcsv "result.csv"
# --------------------
# Sort by t(ree) and output the result to result.csv
# --------------------
111 --> 222 --> 777 --> 555 --> 321
| +-> 789
+-> 999
333 --> 444 --> 888 --> 234
+-> 666
123 --> 456 --> 567
987 --> 654
p,c
111,222
222,777
333,444
777,555
333,666
444,888
222,999
123,456
987,654
777,789
555,321
888,234
456,567
t
"111 -> 222 -> 777 -> 555 -> 321"
"111 -> 222 -> 777 -> 789"
"111 -> 222 -> 789"
"111 -> 222 -> 999"
"123 -> 456 -> 567"
"333 -> 444 -> 888 -> 234"
"333 -> 666"
"987 -> 654"
The solution given by somesoni2, I am afraid, is not recursive. Though not a completely recursive one,
I have managed to come up with a decent solution as follows.
You need repeat a line (n - 2) times in order to get n-deep tree from parent-child pairs.
I needed to start from the end nodes instead of arbitrary nodes
in order to avoid (partially) duplicate branches.
(Please pardon my formatting below. This is my first post.)
sourcetype=csv
| join type=left c [search sourcetype=csv | rename c as c2 | rename p as c ] | where isnull (c2)
| eval t=p." -> ".c | eval r=p | eval e=c | table t, r, e
| eval k=r | join type=left k [search sourcetype=csv | rename c as k ] | eval t=if(isnull(p), t, p." -> ".t) | eval r=p | table t, r, e
| eval k=r | join type=left k [search sourcetype=csv | rename c as k ] | eval t=if(isnull(p), t, p." -> ".t) | eval r=p | table t, r, e
| eval k=r | join type=left k [search sourcetype=csv | rename c as k ] | eval t=if(isnull(p), t, p." -> ".t) | eval r=p | table t, r, e
| sort t | table t | outputcsv "result.csv"
p: parent
c: child
c2: temporary child
r: root
k: (join) key
e: end
t: tree
# --------------------
# [Initial Data Processing]
# --------------------
sourcetype=csv
| join type=left c [search sourcetype=csv | rename c as c2 | rename p as c ] | where isnull (c2)
| eval t=p." -> ".c | eval e=c | eval r=p | table t, r, e
# --------------------
| join type=left c [search sourcetype=csv | rename c as c2 | rename p as c ] | where isnull (c2)
# (1) Leave only parent-child pairs where the child is an end node
| eval t=p." -> ".c | eval r=p | eval e=c | table t, r, e
# (2) Set p(arent) to r(oot), c(hild) to e(nd), and output t(ree), r(oot), e(nd)
# --------------------
# [Repeatable Part]
# --------------------
| eval k=r | join type=left k [search sourcetype=csv | rename c as k ] | eval t=if(isnull(p), t, p." -> ".t) | eval r=p | table t, r, e
# You repeat this line n - 2 times to get n-deep tree
# --------------------
| eval k=r
# (1) Set r(oot) to k(ey)
| join type=left k [search sourcetype=csv | rename c as k ]
# (2) Join the output with the csv with c(hild) renamed as k(ey) based on k(ey)
| eval t=if(isnull(p), t, p." -> ".t)
# (3) Add p(arent), " -> " in front of t(ree) if p(arent) is not null
| eval r=p | table t, r, e
# (4) Set p(arent) to r(oot) and output t(ree), r(oot), e(nd)
# --------------------
# --------------------
# [Finishing Touch]
# --------------------
| sort t | table t | outputcsv "result.csv"
# --------------------
# Sort by t(ree) and output the result to result.csv
# --------------------
111 --> 222 --> 777 --> 555 --> 321
| +-> 789
+-> 999
333 --> 444 --> 888 --> 234
+-> 666
123 --> 456 --> 567
987 --> 654
p,c
111,222
222,777
333,444
777,555
333,666
444,888
222,999
123,456
987,654
777,789
555,321
888,234
456,567
t
"111 -> 222 -> 777 -> 555 -> 321"
"111 -> 222 -> 777 -> 789"
"111 -> 222 -> 789"
"111 -> 222 -> 999"
"123 -> 456 -> 567"
"333 -> 444 -> 888 -> 234"
"333 -> 666"
"987 -> 654"
Sorry, It's possible instead of write N times the same query, insert a loop?
Thank you
Is there a loop function or functionality available in Splunk? I looked around, but could not find loop when I worked on my answer above.
Unfortunately, there is no loop function. Maybe you could use "map" command, but probably it would be better to write a custom search command that does loop operation.
Try something like this
your base search | table parent, child | join type=left child [search your base search | rename child as child2 | rename parent as child]
| eval child=child.coalesce(",".child2,"") | table parent, child
Updated answer
Try this
sourcetype=csv |join type= left c [search sourcetype=csv |stats delim="," values(c) as cvals by p |rename p as c]|eval c=c.coalesce(",".cvals,"") | table p,c