topic Re: Combine 3 queries using a common field in Splunk Search

Combine 3 queries using a common field

vinoths_82 — Mon, 19 Oct 2020 21:38:02 GMT

I have 3 queries as below and all 3 of them have a common field "loaderId". I used join to combine their results in a table format and calculated P95 on their times. But I believe there has to be a better approach to this problem. Please let me know one.

Eg:

index=* ... | search eventSource="Page Load" | |table eventSource, duration1, loaderId | join loaderId [search

index=* ... | search eventSource="End to End Time" | table eventSource, duration2, loaderId | join loaderId [

index=* ... | search eventSource="Total Time" table eventSource, duration3, loaderId] | table eventSource, duration1, duration2, duration3, loaderId | stats perc95(duration1), perc95(duration2), perc95(duration3)

Re: Combine 3 queries using a common field

bowesmana — Mon, 19 Oct 2020 22:06:22 GMT

@vinoths_82

I guess the devil's in the detail with your example, as the ... in the query might be significant, however, if you have duration 1,2,3 in your data then the simple

index=* ... (eventSource="Page Load" OR eventSource="End to End Time" OR eventSource="Total Time") | stats perc95(duration1), perc95(duration2), perc95(duration3) by loaderId

would be a start - note I split by loaderId, which I am not sure is what you want. However, if you do not have the duration fields and the '...' is part of your duration calculation, then there's no reason why you could not calculate the durations by using eval/case, something like

| eval duration1=if(eventSource="Page Load",xxx,null()) | eval duration2=if(eventSource="End to End Time",yyy,null()) | eval duration3=if(eventSource="Total Time",zzz,null())

Not sure if this will get you to where you want to get to, but I would need a little more detail on your data, but hope it helps

Re: Combine 3 queries using a common field

vinoths_82 — Thu, 22 Oct 2020 01:48:07 GMT

I think this is what I am looking at. Let me try.

Re: Combine 3 queries using a common field

vinoths_82 — Mon, 26 Oct 2020 19:57:18 GMT

I tried the above, but still I have some issues. Let me explain (what I missed to do above).

What I have:

I have 3 different events being generated async. with their corresponding durations. They all share a common Id called "loaderId". see eg. below

Event Source LoaderId Duration

Page Load 03904756 33.2

Page Load 03904766 23.2

Event Source LoaderId Duration

End To End 03904756 33.4

Event Source LoaderId Duration

Total Time 03904756 33.7

In the above example, you can see there are 2 events in Page Load but only 1 for the other 2.

What I want:

When I used join using LoaderId, I get the final output like below.

LoaderId "Page Load Time" "End to End Time" "Total Time"

03904756 33.2 33.4 33.7

This eliminates rows from all events where there are no duration values in any of 3 event sources.

What Happened with previous Suggestion:

When I tried to group by loaderId I see results like below

LoaderId "Page Load Time" "End to End Time" "Total Time"

03904756 33.2 33.4 33.7

03904766 23.2

I tried to remove the 2nd row by using len function or isnotnull for durations, but it didn't help. Let me know how I can get the result.