Looping in Splunk Query

[Gowthamdevaraj]

Hello Everyone.

I have a traceability report as below
Parent Child
A B
A C
B D
C E
C F

Where as I have create the link traceability as
Parent Son Grandson
A B D
A C E
A C F

I am struggling to identify the base query for this use case, can anyone suggest ?

[to4kawa]

| makeresults count=200
| eval Parent=mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ",""),random()%26)
| eval Son=mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ",""),random()%26)
| table Parent Son
| dedup Parent Son
| rename COMMENT as "from here, the logic"
| eval tmp=Parent.Son
| eventstats values(tmp) as listed
| mvexpand listed
| rex field=listed max_match=0 "(?<GroundSon1>\w)(?<GroundSon2>\w)"
| where Son==GroundSon1
| table Parent Son GroundSon2
| rename GroundSon2 as Groundson
| dedup Parent Son Groundson
| sort Parent Son Groundson

For three category , It can forced to do .

[ktugwell_splunk]

Hey there,

This had be puzzled for a bit! And I do hope I haven't over-engineered it.

I think the best way for you to achieve this is to potentially use a lookup. This will work on small to moderate datasets, if your dataset larger, you may want to consider the KV Store.

First, I reproduced your dataset like this:

| makeresults count=5 
| streamstats count 
| eval Parent=CASE(count=1,"A",count=2,"A",count=3,"B",count=4,"C",count=5,"C",1==1,0) 
| eval Child=CASE(count=1,"B",count=2,"C",count=3,"D",count=4,"E",count=5,"F",1==1,0)
| fields - _time
| fields Parent Child

Then i output the results to a CSV | outputlookup family.csv

I then used that output to link the family members together.

| makeresults count=5 
| streamstats count 
| eval Parent=CASE(count=1,"A",count=2,"A",count=3,"B",count=4,"C",count=5,"C",1==1,0) 
| eval Child=CASE(count=1,"B",count=2,"C",count=3,"D",count=4,"E",count=5,"F",1==1,0) 
| fields - _time 
| fields Parent Child 
| lookup family.csv Parent AS Child OUTPUT Child AS Grandchild 

Finally, you'll see, because C is both the parent of E and F. Splunk will give you a multivalued field for Grandchild.

You can then use this | mvexpand Grandchild which should give you the result you're looking for.

I hope this works for you and demonstrates how a lookup can be used to match data like this. Remember, you can always schedule the outputlookup to keep the family.csv up to date.

Thanks

[gowtham08091]

Hi @ktugwell_splunk 

Thanks for your response, In this case the count 5 is dynamic, They may 100's of rows with the parent child relationship. in such a use case how would I build the relationship.

Thanks,