All Apps and Add-ons

How to combine search results with dbxquery results and include unmatched results from search?

kjandhyala
Explorer

Hi, My search data and database table looks like below.
search data:
id name
111 aaa
222 bbb
333 xyz
444 ccc
555 pqr
666 ddd

database data
srid country
111 united states
222 canada
444 china
666 france

When I write a search query and get additional column from database using dbx query, the unmatched rows are missing. Here is my search query and dbxquery.

index="myindex" | fields srid,name| dbxquery connection="myconn" query="select country from country_lkup;" "id" as "srid" OUTPUT "country"| table srid,name,country

The result is showing as
srid name country
111 aaa united states
222 bbb canada
444 ccc china
666 ddd france

But, I would like to see the results including the rows that do match in my database, as below.
srid name country
111 aaa united states
222 bbb canada
333 xyz
444 ccc china
555 pqr
666 ddd france

Can someone please help me how to achieve this?

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

I'd try fillnull first .

index="myindex" | fields srid,name| dbxquery connection="myconn" query="select country from country_lkup;" "id" as "srid" OUTPUT "country"| fillnull value="unknown" country | table srid,name,country

If that doesn't work, you may need to try some SQL tricks. My SQL is rusty, but perhaps COALESCE or ISNULL will work.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I'd try fillnull first .

index="myindex" | fields srid,name| dbxquery connection="myconn" query="select country from country_lkup;" "id" as "srid" OUTPUT "country"| fillnull value="unknown" country | table srid,name,country

If that doesn't work, you may need to try some SQL tricks. My SQL is rusty, but perhaps COALESCE or ISNULL will work.

---
If this reply helps you, Karma would be appreciated.
0 Karma

manunairadavakk
Path Finder

@richgalloway
I am getting an error:
Error in 'dbxquery' command: This command must be the first command of a search.

Get Updates on the Splunk Community!

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...