why few values in a field moved to null automatically when it has actual values

[dtccsundar]

Hi,

Facing a strange issue in splunk .First of all we are ingesting data into splunk from sql server as a view .The sql server view returns the correct value but the splunk sourcetype doesn't.

Particular field  like reporting has 2 values (Yes or No ) where Yes will have count like 215 and No 44 .But the actual count required is Yes 246 and No 48 ,this is from warehouse view.

When i run the

search index=x sourcetype=y|search reporting="No"|stats count by z 

the count  am getting is 44 and not 48.THe interesting field category has 215 and 44  .The field reporting in interesting field has around 86 % of values only .Is this the reason ?One more thing is the events tab has the no null values for these 2 values in reporting field and when  am searching using above SPL  am getting this issue.

When fill null in reporting , am getting 48 but those null are not available in warehouse view.

Can anyone help me to find why there is difference in count from warehouse view  to splunk sourcetype? Why the values are moved to "0" (Null).

[ITWhisperer]

If I understand correctly, you are not getting events where z is null included in the stats count?

search index=x sourcetype=y|search reporting="No"
| fillnull value="no z" z 
| stats count by z

[dtccsundar]

Thank you ITwhisperer.

I am getting the same count ie 44 as earlier after using your search which is not expected count .

No , 5 rows of Z doesn't have value. But when i run the below search 

index=x sourcetype=Y |fillnull| search reporting="0" , i am getting 35 rows as results and this causes the count mismatch .

All the reporting rows has values either yes or no in it in events page .The reporting field in interesting field area also has count mismatch .

Not sure why events page has values but not in interesting field.Can you help me to solve this issue.

[ITWhisperer]

The fillnull is applied after the initial search for events

The interesting fields are extracted from the raw event data and does not include values that are null in the raw data, hence the mismatch in count for reporting

It sounds like what you are seeing is consistent with some of the events not having values in the reporting field.

[dtccsundar]

Yes , You are right .

But the warehouse (sql) view has data for all and when i run the view it gives expected count and the not the count given by splunk.

Any splunk issue ? i need to add any command to grab the values in splunk from warehouse ?

This is the first time i am facing this issue and its a strange one .

[dtccsundar]

Can you please help me out in this issue.

[ITWhisperer]

I am not sure I understand the issue

"Particular field  like reporting has 2 values (Yes or No ) where Yes will have count like 215 and No 44 .But the actual count required is Yes 246 and No 48 ,this is from warehouse view."

This isn't quite true, reporting has 3 values, (Yes, No or null).  The counts you are getting are consistent with this. Right?

[dtccsundar]

Yes the count i am getting is consistent ,but few rows in the Null values need to be moved to No and few to Yes as per warehouse view .But not sure how to segregate that as expected.

[ITWhisperer]

The question is how did splunk and warehouse get out of step? Has the warehouse been updated since the data was ingested into splunk? Did something go wrong for just these events when they were ingested into splunk? Can you re-ingest the events to fix them?

[dtccsundar]

Thank you for continues support.

I have recreated the sourcetype from same view but no success .All other stuffs are working fine ,but not sure why it happens to this alone.