Splunk Search

How to force DBconnect to send fields with NULL values to the index?

rajacybermak
Explorer

DBconnect is not sending fields with NULL values to the index
Is there a way to force DBconnect to do this ?

0 Karma

rajacybermak
Explorer

I have used query as below in DBconnect
SELECT COALESCE(field1,'NULL') AS field1, field2 FROM "schema1"."table1"
and getting the desired results

0 Karma

rajacybermak
Explorer

Thank You Rich
The major problem is , most of the time, making changes to the DB side is near impossible !

Thanks
Raja

0 Karma

Richfez
SplunkTrust
SplunkTrust

The easiest way to fix this is on the DB side of things, not the Splunk side. If you handle null values there and convert them into just an empty string, they'll come across into Splunk fine.

So how to do that? Well, you don't mention which DBMS you are using so you'll have to look up your own, but most DBs have some variant of COALESCE which will just take fields or values until one is not null. So, ON THE QUERY side of things that SometimesNullField would be selected out using a SQL statement vaguely like this one...

select field1, field2, COALESCE(SometimesNullField, "") AS SomettimeNullFieldFixed, field4, .... FROM ....

Though another option would be to use that same sort of syntax (Whatever it is for your DBMS) to create a View, then have Splunk call the view instead of the raw table (this is also useful for abstracting complex queries into a simple and easy to read one that Splunk uses, and pushes Business logic into the DB layer).

Lastly, you could also get around this whole entire problem by not ever allowing null values in your database. Again, a problem that needs fixing on the DB side of things.

I hope these help!
H
appy Splunking,
Rich

Tags (3)
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...