All Apps and Add-ons

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

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

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, an upvote would be appreciated.

View solution in original post

0 Karma

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, an upvote would be appreciated.

View solution in original post

0 Karma

Path Finder

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

0 Karma