Splunk Search

How to join value using wildcard? (Urgent)

kelvin56887
Explorer

I want to calculate the sum of count value in a tree form of data

Count table:
http://i60.tinypic.com/2qs1bmf.png

In count table,
0.0 is child of 0,
0.0.1 is child of 0.0,
0.0.1.1 is child of 0.0.1,
0.0.2 is child of 0.0,
0.0.1.2 is child of 0.0.1,
0.0.2.1 is child of 0.0.2,
0.0.2.2 is child of 0.0.2

Total count of the node = "sum of all offspring's count"+ self count

Expected result:
http://i58.tinypic.com/vfy3oi.png

I think that I may build up the [Relationship] table first.
Like this: http://i57.tinypic.com/dd83o.png
In SQL, I can write:
SELECT child.id SEQUENCE_ID,parent.id ELDER_GENERATION_SEQUENCE_ID FROM [count_table] child
INNER JOIN [count_table] parent on (child.id like parent.id + '%')

But how about in Splunk??

After having this table, I will group by the second column of [Relationship] table and sum them up.

What is the query to do this?
Thank you!!!!!

1 Solution

somesoni2
Revered Legend

Try this workaround (runanywhere sample with your example data)

|gentimes start=-1 |eval temp="0:1#0.0:0#0.0.1:0#0.0.1.1:1#0.0.2:0#0.0.1.2:1#0.0.2.1:1#0.0.2.2:1" |table temp | makemv delim="#" temp | mvexpand temp | rex field=temp "(?<id>.*):(?<count>.*)" |fields - temp
 | eval sno=mvcount(split(id,".")) | eval t=mvrange(1,sno+1) | mvexpand t |eval tid=substr(id,1,2*t -1)|stats sum(count) as count by tid

View solution in original post

BansodeSantosh
Explorer

Yes, it's too late. But this might help someone looking for same now.

For using wildcard, you can do something like this:

| join type="join_type" wildcard("field_name_to_join" ) [inner search here]

Hope this will help you all.

0 Karma

robertlynch2020
Influencer

Hi

I tried this and cant get it to work, am i assuming the wilidard is * ?

Thanks
Robert

0 Karma

sirpatrick
Explorer

Ditto I too could not get it to work no matter how I tried to set the "field_name_to_join" including with an eval using the same name as the inner search would find. I was so hopeful too!

0 Karma

somesoni2
Revered Legend

Try this workaround (runanywhere sample with your example data)

|gentimes start=-1 |eval temp="0:1#0.0:0#0.0.1:0#0.0.1.1:1#0.0.2:0#0.0.1.2:1#0.0.2.1:1#0.0.2.2:1" |table temp | makemv delim="#" temp | mvexpand temp | rex field=temp "(?<id>.*):(?<count>.*)" |fields - temp
 | eval sno=mvcount(split(id,".")) | eval t=mvrange(1,sno+1) | mvexpand t |eval tid=substr(id,1,2*t -1)|stats sum(count) as count by tid
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...