dbquery command with map command

[BP9906]

Has anyone been able to use inputlookup with the map command to run multiple DB queries?

When I run it, I get an error that dbquery doesnt understand database named $DATABASE$.
Definitely the inputlookup returns 1 column named "DATABASE" with the database names equal to the naming I put for the database name when I run the command manually.
Any ideas?

I found that "|append [ |dbquery ..." works too, but more than a few with a complex query makes it look like a scary splunk search.

Thank you for your help.


|inputlookup db.csv | map [ | dbquery "$DATABASE$" "select column1 from $DATABASE$.table1 where table1.last_updated_date >= TRUNC(SYSDATE)" ]


[BP9906 · Accepted solution]

I figured out that using a subsearch ie [ ] is not ideal in most situations. Its better to use the map search="" notation and escape the quotes inside the quoted search=.

Thus, this search works:


index=log sourcetype=app_log "keyword" | rex "(?i)primary key: (?P[^ ]+)" | join type=outer host [ | inputlookup db_info.csv ] | dedup host, primary_key | fields SID, primary_key | map search="| dbquery $SID$ \"select column1, column2 from $SID$.table where _id = '$primary_key$'\"" | table column1, column2


I also noticed that if you remove the last "table" command, and run it straight out, you wont see the result but Splunk displays results count.