topic Re: Why does my int field from mssql database not become an extracted field in Splunk when it is NULL? in Splunk Search

Why does my int field from mssql database not become an extracted field in Splunk when it is NULL?

krwinters11 — Tue, 11 Nov 2014 18:36:57 GMT

I have an mssql database that I am importing using DB Connect. I have an int field type that could equal NULL or 1 through 19. When I search in the splunk search app, this field is not extracted when it is equal to NULL in the database. Is there a way to get Splunk to extract this still? I am using a lookup table (csv file) to give the numbers 1-19 definitions.

Re: Why does my int field from mssql database not become an extracted field in Splunk when it is NULL?

krwinters11 — Wed, 12 Nov 2014 16:40:07 GMT

Can regex help me here?

Re: Why does my int field from mssql database not become an extracted field in Splunk when it is NULL?

davebrooking — Wed, 12 Nov 2014 17:42:29 GMT

I'm not sure I'm totally understanding your question. What is your SQL statement? What should appear in the Splunk output if the int column is zero?

Could you use the SQL COALESCE function on the int column that's being returned to set it to a value - maybe zero, and add this to your lookup table?

Re: Why does my int field from mssql database not become an extracted field in Splunk when it is NULL?

krwinters11 — Wed, 12 Nov 2014 19:43:28 GMT

Right now I don't have an SQL statement; I'm pulling in the entire table. In the database, it appears as NULL (if I veiw it in something like SQL Server Management Studio). When I look at my raw data in Splunk, the field will show up as "fieldName=" and is immediately followed by the next field. The data doesn't get extracted as a specific field for the events with a NULL value for this field. Is there a way to set a rule on indexing, or a way to write a regex expression insert a "0" or "NULL" after the equals sign in the events that have a NULL in the database?

Hopefully that makes more sense. Thank you for your help!

Re: Why does my int field from mssql database not become an extracted field in Splunk when it is NULL?

davebrooking — Thu, 13 Nov 2014 17:07:22 GMT

I think your field is being ingested by Splunk as a null value, It's just that displaying the raw data in Splunk doesn't show NULL in the same way as the SSMS interface does.

You can make the field appear as NULL in a search by using an eval statement, something like

... | eval fieldname=if(isnull(fieldname),"NULL",fieldname) | ...

The eval won't change the raw data, it'll just change that field's value for the rest of the search.

Do you really want to manipulate the raw data before indexing it? Personally, I'd need a really good reason to do that in Splunk, as it can adversely affect indexing performance.

There is a section in the Getting Data In documentation regarding anonymizing data that allows data to be manipulated prior to indexing, you should be able to use that method if you really must.

Re: Why does my int field from mssql database not become an extracted field in Splunk when it is NULL?

krwinters11 — Thu, 13 Nov 2014 17:11:50 GMT

Thanks! The search statement worked. I'll see if that can give me everything I need before manipulating the data prior to indexing. I really appreciate the help!

Re: Why does my int field from mssql database not become an extracted field in Splunk when it is NULL?

jrodman — Sat, 15 Nov 2014 02:08:59 GMT

To be clear, a "null field" in Splunk is effectively identical to the field not being present. (In the internal in-memory representation there's a table, and that column has a null in it for that row, in a csv representation, the distinction bewteen null fields and empty strings is very subtle.)

Therefore it's pretty likely that Splunk is doing the right thing, but not guaranteed, as failing to do anything would also produce a null field. Basically any made-up field name you test for that is not defined will be considered null.

FWIW, I think |eval fieldname=coalesce(fieldname, "NULL") reads a little better, but perhaps that's a matter of taste.

Re: Why does my int field from mssql database not become an extracted field in Splunk when it is NULL?

davebrooking — Sat, 15 Nov 2014 12:52:51 GMT

I think you're right - I should have used coalesce