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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...