topic Joining fields from three different indexes and sourcetypes in Splunk Search

Joining fields from three different indexes and sourcetypes

ismarslomic — Fri, 14 Apr 2017 10:49:29 GMT

I have three searches that I want to merge into one single table as search output. I will try to explain my case through three individual searches:

Search 1 (base search)

(index="website_monitoring" sourcetype="url_check") title="wiki-via-bfrm-lbs" `filter_service_period_hours` total_time > `response_time_threshold` | table _time total_time time_namelookup corr_id | sort -_time

Output fields:
- _time
- total_time
- time_namelookup
- corr_id
Example event:

2017-04-13T10:06:03+0000 title="wiki-via-bfrm-lbs" corr_id=1492077961074495474 response_code=200 expected_content=True total_time=2604.000 time_redirect=0 time_appconnect=0 time_connect=2515.000 time_namelookup=2513.000 time_pretransfer=2515.000 time_starttransfer=2573.000 request_time=2604.000 timed_out=False content_size=55914 url_effective=http://mydomain.com/wiki/pages/viewpage.action?pageId=65538&corr_id=1492077961074495474&script=wiki-via-bfrm-lbs url=http://mydomain.com/wiki/pages/viewpage.action?pageId=65538 content="Let&#39;s edit this page (step 3 of 9) - Demonstration Space "

Search 2 (additional fields based on base search)

index=confluence_prod sourcetype="confluence:app:access" corr_id=1492068301527265031 | table requesttime_in_ms

Correlation fields
- field corr_id is present in Search 1 and Search 2. Value 1492068301527265031 is ment to be retrieved from Search 1
Output fields
- requesttime_in_ms
Example events

[2017-04-13T10:06:03+0000] ip=172.0.0.1 user=TestUser http_method=GET url=“/wiki/pages/viewpage.action?pageId=65538&corr_id=1492077961074495474&script=wiki-via-bfrm-lbs" protocol_version=HTTP/1.1 http_status_code=200 responsesize_bytes=55977 requesttime_in_ms=2176 referer="-" user_agent="curl/7.29.0"

Search 3 (additional fields based on base search)

index="os" sourcetype="cpu" host="aca-db*" cpu="all" earliest="04/13/2017:10:06:00" latest="04/13/2017:10:06:59" | eval total_usage=(100-pctIdle) | stats avg(total_usage) as cpu_usage

Correlation fields
- fields earliest and latest in Search 2 shall be equal to earliest and latest in Search 1.
Output fields
- cpu_usage

Desired search output (merged)

_time (from Search 1)
total_time (from Search 1)
time_namelookup (from Search 1)
requesttime_in_ms (from Search 2)
cpu_usage (from Search 3)

What I have tried so far
I have been reading different answers and Splunk doc about append, join, multisearch. I wanted to give a try solution described in the answer: https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html, but so far I have not succeeded for all three searches.

Search (merging output from Search 1, Search 2 and Search 3)

(index="website_monitoring" sourcetype="url_check" title="wiki-via-bfrm-lbs" total_time > `response_time_threshold`) 
OR (index="confluence_prod" sourcetype="confluence:app:access") `filter_service_period_hours` 
OR (index="os" sourcetype="cpu" host="aca-db*" cpu="all" earliest="04/13/2017:12:05:05" latest="04/13/2017:12:07:05")
| eval corr_id-{index}=corr_id 
| eval Time=strftime(_time, "%F %T") 
| eval total_usage=(100-pctIdle)
| stats values(corr_id-*) AS * values(total_time) as "Response time Monitoring" values(requesttime_in_ms) as "Response time App" values(time_namelookup) as "DNS lookup" avg(total_usage) as cpu_usage by corr_id Time
| mvexpand website_monitoring  
| mvexpand confluence_prod 
| where website_monitoring=confluence_prod 
| fields - website_monitoring,confluence_prod

Output
Output shows correct values for fields
- _time (from Search 1)
- total_time (from Search 1)
- time_namelookup (from Search 1)
- requesttime_in_ms (from Search 2)

but for cpu_usage (from Search 3) I dont get any value in output.

I would really appreciate tips/hints on how to fulfil desired search output.

Re: Joining fields from three different indexes and sourcetypes

Richfez — Fri, 14 Apr 2017 13:03:01 GMT

If I may - nicely done question! It is a hard enough one that it'll take some thinking and staring at, though. Is there any chance you could paste in a few events of each type? I tend to be able able to visualize these things better when I can see a bit of the data.

Anyway,

Partial "potential enhancement" for search 1 and 2.

I think your problem with the multiple results from those two is because of your stats command. You have

| stats values(corr_id-*) AS * <more stuff> by Time

But I think what you need might be

| stats <do NOT include the values for corr_id> <more stuff> BY corr_id, Time

I could be wrong about that. But values(something) by _time is going to give you for each time slice however many values of something you had in that time slice, and I'm positive this is not what you want.

Re: Joining fields from three different indexes and sourcetypes

Richfez — Fri, 14 Apr 2017 13:05:13 GMT

What is all the | eval corr_id-{index}=corr_id about? I am not trying to say there's anything wrong with it, I just don't understand what you are doing there, or especially why you are doing it.

Yes, some source data would be very helpful. Even one event from each (especially if they all "match", though source three you may need one "non-match" as well so we can see how they do NOT connect, too. 🙂

AH - Search three is just grabbing a quick CPU value from the time period involved based on the host, right?

Re: Joining fields from three different indexes and sourcetypes

DalJeanis — Fri, 14 Apr 2017 15:06:21 GMT

Because you have those macros in that spot with no pipe before them, I can't tell how you would go about making a global search that got you the same results.

So, here's an appendpipe and map version. The second appendpipe could also be written as an append, YMMV.

The escaping on the double-quotes inside the search will probably need to be corrected, since that's pretty finnicky. While testing, just start with corr_id=1492068301527265031 in the first search and add one section at a time and debug until it's working.

(index="website_monitoring" sourcetype="url_check") title="wiki-via-bfrm-lbs" `filter_service_period_hours` total_time > `response_time_threshold` | table _time total_time time_namelookup corr_id | sort -_time

| appendpipe [ | map maxsearches=0 search="index=confluence_prod sourcetype=\"confluence:app:access\" corr_id=$corr_id$ | table corr_id requesttime_in_ms"]

| appendpipe [| addinfo | dedup info_min_time | map search="earliest=$info_min_time$ latest=$info_max_time$ index=\"os\" sourcetype=\"cpu\" host=\"aca-db*\" cpu=\"all\"" | eval total_usage=(100-pctIdle) | stats avg(total_usage) as cpu_usage]

| eventstats max(cpu_usage) as cpu_usage
| stats values(*) as * by corr_id

Re: Joining fields from three different indexes and sourcetypes

ismarslomic — Fri, 14 Apr 2017 15:33:57 GMT

Thanks, @rich7177! Group by corr_id helped! Now I get the results Im expecting for Search 1 and 2. Need to extend it for Search 3. Will post updated solution when done so others can reuse it as well.

Re: Joining fields from three different indexes and sourcetypes

ismarslomic — Fri, 14 Apr 2017 15:36:22 GMT

Hehe, I was wondering about the same. But it is already explained by original author of the search query in Answer: https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html (see question from @wrangler2x)

Re: Joining fields from three different indexes and sourcetypes

ismarslomic — Fri, 14 Apr 2017 16:23:36 GMT

Thanks, @DalJeanis. I have tried to execute line 1 - 3 + 8 like this:

(index="website_monitoring" sourcetype="url_check") title="wiki-via-bfrm-lbs" | table _time total_time time_namelookup corr_id | sort -_time
 | appendpipe [ | map maxsearches=0 search="index=confluence_prod sourcetype=confluence:app:access corr_id=1492068301527265031 | table corr_id requesttime_in_ms"]
 | stats values(*) as * by _time corr_id

But column requesttime_in_ms is missing in output. To avoid too much complexity, I have removed completely usage of custom macros in query above.

Re: Joining fields from three different indexes and sourcetypes

ismarslomic — Fri, 14 Apr 2017 19:50:11 GMT

Example events added for Search 1 and 2. Search 3 events are very simple, like

all       8.36       0.00       5.40       0.00      86.24

Hope this helps. Im still working with including the third search into query mentioned in "What I have tried so far", with addition of corr_id for grouping. Still no luck.

Re: Joining fields from three different indexes and sourcetypes

MuS — Sat, 15 Apr 2017 09:05:17 GMT

Hi ismarslomic,

I'm on holiday right now ...

Don't expect any answer within the next two weeks, but I'm happy to help when I'm back 😉

cheers, MuS

Re: Joining fields from three different indexes and sourcetypes

ismarslomic — Sun, 16 Apr 2017 15:22:37 GMT

@rich7177 or @DalJeanis: would you be able to support me finalising the last search? I hope that updates I have done in my question is giving you more insight into the complexity and events?