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!

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

[Coming Soon] Splunk Observability Cloud - Enhanced navigation with a modern look and ...

We are excited to introduce our enhanced UI that brings together AppDynamics and Splunk Observability. This is ...