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.

Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Maximizing the Value of Splunk ES 8.x

Splunk Enterprise Security (ES) continues to be a leader in the Gartner Magic Quadrant, reflecting its pivotal ...

Operationalizing TDIR: Building a More Resilient, Scalable SOC

Optimizing SOC workflows with a unified, risk-based approach to Threat Detection, Investigation, and Response ...

Introducing .conf Stories Series!

“.conf Stories” Series – First Feature: Rich Mahlerwein   Every year .conf brings together some of the most ...