topic Re: Looping in Splunk Query in Splunk Search

Looping in Splunk Query

Gowthamdevaraj — Tue, 21 Apr 2020 15:45:59 GMT

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 ?

Re: Looping in Splunk Query

ktugwell_splunk — Wed, 22 Apr 2020 15:25:34 GMT

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

Re: Looping in Splunk Query

gowtham08091 — Fri, 12 Jun 2020 09:13:49 GMT

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,

Re: Looping in Splunk Query

to4kawa — Fri, 12 Jun 2020 11:56:59 GMT

For three category , It can forced to do .