Splunk Search

dbquery command with map command

BP9906
Builder

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)" ]

Tags (1)
1 Solution

BP9906
Builder

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.

View solution in original post

BP9906
Builder

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.

Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...