I have a set of events as follows for a chain of SQL Server blocked processes. It's a tree based structure. I am trying to join the data set on itself to determine which resources are blocking the most. Either result is okay but I prefer the first one. I'm able to figure out a solution for result #2 using a join
and searching the same data twice. However, the number of events is more than 10K so it truncates the results. I have seen the selfjoin command in docs but am not certain how to join between two different fields in the same data set.
Does anyone know how to produce either of the results below?
Sample Events
Process ID, Blocked By Process ID, Resource Name, Wait Time
1, 0, Resource 1, 0
2, 1, Resource 2, 15
3, 1, Resource 3, 10
4, 2, Resource 4, 5
Result set 1
Blocker, Total Blocked Victim Time
Resource 1, 30 <- recursively sum the wait time
Resource 2, 5
Result set 2
Blocker, Total Blocked Victim Time
Resource 1, 25 <-- only sum the wait time of the children (not grandchildren, etc)
Resource 2, 5
Here's an example of one way to achieve this.
It's most efficient as a two-step process
Step one creates a csv file of the data to be recursed.
Step two iteratively joins that data to itself however many times you want it to so so.
https://answers.splunk.com/answers/96043/recursive-search.html?childToView=495518#answer-495518
I'm not sure whether this one contains anything else you might find useful, but check it out as well...
https://answers.splunk.com/answers/170487/recursively-join-the-same-table.html