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?
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).
Using map
will certainly do it but you need to add maxsearches=999999
to eliminate the 10-search/row limit.