Splunk Search

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

krwinters11
Path Finder

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.

0 Karma
1 Solution

davebrooking
Contributor

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.

View solution in original post

davebrooking
Contributor

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.

View solution in original post

jrodman
Splunk Employee
Splunk Employee

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.

davebrooking
Contributor

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

krwinters11
Path Finder

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!

0 Karma

davebrooking
Contributor

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?

krwinters11
Path Finder

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!

0 Karma

krwinters11
Path Finder

Can regex help me here?

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!