All Apps and Add-ons

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

Explorer

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?

0 Karma

Esteemed Legend

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).

0 Karma

Esteemed Legend

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!