Splunk Search

Looping in Splunk Query

Gowthamdevaraj
New Member

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 ?

alt text

0 Karma

to4kawa
Ultra Champion
| 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 .

0 Karma

ktugwell_splunk
Splunk Employee
Splunk Employee

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

0 Karma

gowtham08091
Explorer

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,

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...