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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...