Splunk DB Connect: How to merge my search query with dbquery command?

[htrednek]

I think I'm close but missing some key bit of syntax...

I've got a search query such as

index=mydbindex col_a="foo" col_b="bar" col_c="yin" | dedup col_a | table col_a col_b col_c

and I've got a dbquery of

| dbquery "myotherdb" "select col_z from jimbobs.table where col_y='$col_a$'"

I've tried using JOIN and can get the columns to combine, but it picks what appears to be a random value from col_z and populates all rows in that column with the same value.

index=mydbindex col_a="foo" col_b="bar" col_c="yin" | dedup col_a | join [|dbquery "myotherdb" "select col_z from jimbobs.table where col_y='$col_a$'"] | table col_a col_b col_c col_z

I've tried using MAP and that gives me 10 rows (don't understand why it stops at 10) for col_z but loses the values in all other columns.

index=mydbindex col_a="foo" col_b="bar" col_c="yin" | dedup col_a | table col_a col_b col_c | map search="| dbquery \"myotherdb\" \"select col_z from jimbobs.table where col_y='$col_a$'\"

Certainly there's gotta be some way to combine both sources of data. Right?

[woodcock]

This makes no sense. In your first search you say ... col_a="foo" ... which means that there is only going to be events with values of foo for field col_a. You then do ... dedup col_a ... which means that you will only have a single event and that event will have a single value for col_a, namely foo. Then you are saying that you would like to parameterize a dbquery using these events but there is only one event so this optimizes down to simply this:

| dbquery "myotherdb" "select col_z from jimbobs.table where col_y='foo'"

So you should just do that (or clarify what you are really trying to do that doesn't oversimplify down to a trivial non-ish-answer).

[woodcock]

Using map will certainly do it but you need to add maxsearches=999999 to eliminate the 10-search/row limit.