Empty rows in Table

[Vani_26]

We have a table where i see no data for few coloumns tried fillnull value=0 but its not working.
But this is happening only when there no count for complete column, for example, For invalidcount we have data for Login but no data for other applications so it automatically filled zero values,  but for rejectedcount, trmpcount, topiccount there is no data for any application  0 value is not getting filled up.

Application	incomingcount	rejectedcount	invalidcount	topcount	trmpcount	topiccount
Login	1		2	5
Success	8		0	2
Error	0		0	10
logout	2		0	4
Debug	0		0	22
error-state	0		0	45
normal-state	0		0	24

[yuanliu]

---

We have a table where i see no data for few coloumns tried fillnull value=0 but its not working.

---

Now I see this must be a tricky homework that everybody is fishing for an answer.  Let me point out that in none of your code illustrations have you demonstrated HOW you "tried" fillnull.  Could you show one example you tried, and show actual results?

[bowesmana]

What's your SPL?

[Vani_26]

Please find the query and sample logs, Issue is when there are no logs with any of the  Msgs, that coloumns are showing null, tried fill null command but not working.

index=app-index source=application.logs 
|rex field= _raw "application :\s(?<Application>\w+)"
| rex field= _raw "(?<Msgs>Initial message received with below details|Letter published correctley to ATM subject|Letter published correctley to DMM subject|Letter rejected due to: DOUBLE_KEY|Letter rejected due to: UNVALID_LOG|Letter rejected due to: UNVALID_DATA_APP)"
|chart count over Application by Msgs
|rename "Initial message received with below details" as Income, "Letter published correctley to ATM subject" as  ATM, "Letter published correctley to DMM subject" as DMM, "Letter rejected due to: DOUBLE_KEY" as Reject, "Letter rejected due to: UNVALID_LOG" as Rej_log, "Letter rejected due to: UNVALID_DATA_APP" as Rej_app 
|table Income Rej_app ATM DMM Reject Rej_log Rej_app

Sample logs:

2024-01-24 11:21:55,123 [app-product-network-thread | payments_acoount_history_app_hjutr_12nj567fghj5667_product] INFO STREAM_APPLICATION - 
Timestamp:2024-01-24 11:21:55,123
Initial message received with below details:
Application:Login
Code name: payments_acoount_history_app_hjutr_12nj567fghj5667_product
Code offset: -12
Code partition: 4

2024-01-24 11:21:55,123 [app-product-network-thread | payments_acoount_history_app_hjutr_12nj567fghj5667_product] INFO STREAM_APPLICATION - 
Timestamp:2024-01-24 11:21:55,123
Letter published correctley to ATM subject:
Application:Success
Code name: payments_acoount_history_app_hjutr_12nj567fghj5667_product
Code offset: -1
Code partition: 10

2024-01-24 11:21:55,123 [app-product-network-thread | payments_acoount_history_app_hjutr_12nj567fghj5667_product] INFO STREAM_APPLICATION - 
Timestamp:2024-01-24 11:21:55,123
Letter published correctley to DMM subject:
Application:normal-state
Code name: payments_acoount_history_app_hjutr_12nj567fghj5667_product
Code offset: -1
Code partition: 6

2024-01-24 11:21:55,123 [app-product-network-thread | payments_acoount_history_app_hjutr_12nj567fghj5667_product] INFO STREAM_APPLICATION - 
Timestamp:2024-01-24 11:21:55,123
Letter rejected due to: DOUBLE_KEY:
Application:error-state
Code name: payments_acoount_history_app_hjutr_12nj567fghj5667_product
Code offset: -1
Code partition: 4

2024-01-24 11:21:55,123 [app-product-network-thread | payments_acoount_history_app_hjutr_12nj567fghj5667_product] INFO STREAM_APPLICATION - 
Timestamp:2024-01-24 11:21:55,123
Letter rejected due to: UNVALID_LOG:
Application:Debug
Code name: payments_acoount_history_app_hjutr_12nj567fghj5667_product
Code offset: -18
Code partition: 2

2024-01-24 11:21:55,123 [app-product-network-thread | payments_acoount_history_app_hjutr_12nj567fghj5667_product] INFO STREAM_APPLICATION - 
Timestamp:2024-01-24 11:21:55,123
Letter rejected due to: UNVALID_DATA_APP:
Application:logout
Code name: payments_acoount_history_app_hjutr_12nj567fghj5667_product
Code offset: -4
Code partition: 0

[yuanliu]

How did you apply fillnull?  Do you mean to say that the following doesn't give you 0 when the value is null?

index=app-index source=application.logs 
|rex field= _raw "application :\s(?<Application>\w+)"
| rex field= _raw "(?<Msgs>Initial message received with below details|Letter published correctley to ATM subject|Letter published correctley to DMM subject|Letter rejected due to: DOUBLE_KEY|Letter rejected due to: UNVALID_LOG|Letter rejected due to: UNVALID_DATA_APP)"
|chart count over Application by Msgs
|rename "Initial message received with below details" as Income, "Letter published correctley to ATM subject" as  ATM, "Letter published correctley to DMM subject" as DMM, "Letter rejected due to: DOUBLE_KEY" as Reject, "Letter rejected due to: UNVALID_LOG" as Rej_log, "Letter rejected due to: UNVALID_DATA_APP" as Rej_app 
|table Income Rej_app ATM DMM Reject Rej_log Rej_app
|fillnull Income Rej_app ATM DMM Reject Rej_log Rej_app

[Vani_26]

How did you apply fillnull?  

|fillnull value=0

Do you mean to say that the following doesn't give you 0 when the value is null?

yes

[yuanliu]

Do you get all 0 from this?

| makeresults format=csv data="Income, Rej_app, ATM, DMM, Reject, Rej_log< Rej_app
,,,,,
,,,,,
,,,,,"
| fillnull

This is what I get

ATM	DMM	Income	Rej_app	Rej_log< Rej_app	Reject
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0

[Vani_26]

Tried below query:  Where is no data for any Msgs it displaying zero only for 1st 3 rows remaining rows are displaying null.

index=app-index source=application.logs 
|rex field= _raw "application :\s(?<Application>\w+)"
| rex field= _raw "(?<Msgs>Initial message received with below details|Letter published correctley to ATM subject|Letter published correctley to DMM subject|Letter rejected due to: DOUBLE_KEY|Letter rejected due to: UNVALID_LOG|Letter rejected due to: UNVALID_DATA_APP)"
|chart count over Application by Msgs
|rename "Initial message received with below details" as Income, "Letter published correctley to ATM subject" as  ATM, "Letter published correctley to DMM subject" as DMM, "Letter rejected due to: DOUBLE_KEY" as Reject, "Letter rejected due to: UNVALID_LOG" as Rej_log, "Letter rejected due to: UNVALID_DATA_APP" as Rej_app 
|table Income Rej_app ATM DMM Reject Rej_log Rej_app
|appendcols
[| makeresults format=csv data="Income, Rej_app, ATM, DMM, Reject, Rej_log, Rej_app
,,,,,
,,,,,
,,,,,"
| fillnull]

output:

Application	ATM	DMM	Income	Rej_app	Rej_log	Reject
Login	10	0	0	2	0	0
Success	12	0	0	1	0	0
Error	23	0	0	11	0	0
Debug	2			3
logout	1			50
error-state	61			20
normal-state	1			10

[yuanliu]

@yuanliu wrote:

Do you get all 0 from this?

---

What I meant was for you to run that query standalone, not embedded in a complex search.  The purpose is to directly confirm/demonstrate that your Splunk instance performs fillnull as designed.  Nevertheless, your results with my silly test still demonstrates that fillnull works perfectly in your Splunk.

Read appendcols to see why only DMM, Rej_log, and Reject have 0s, and why only three rows have zeros. But let me give some hints:

• Your search does not return events with the following strings:  "Letter published correctley to DMM subject", "Letter rejected due to: DOUBLE_KEY", and "Letter rejected due to: UNVALID_LOG"; but it does return events with "Letter rejected due to: UNVALID_DATA_APP". (You can verify this by, e.g., search without stats and observe; there are many other ways to verify.)
• makeresults in appendcols subsearch only fills 3 rows. (Do run it standalone so you understand what it does.)

Now that we have established that fillnull works correctly, let me point out that this latest illustrated output contains no "empty" cells in ATM (aka "Letter published correctley to ATM subject") and Rej_app (aka "Letter rejected due to: UNVALID_DATA_APP"), the only two columns where your present search actually returns results.  Can you reproduce the problem you described? (No appendcol-makeresults business.)

I also want to point out that your OP illustrated drastically different column names as your later comments.  This type of unexplained differences make volunteers' mind-reading a lot more difficult.  Always explain your dataset, desired results, logic between the two in plain language (preferably without SPL), attempted SPL and actual results, then explain how actual results differ from desired results if that's not painfully obvious - oftentimes it is not to outsiders.  If you need to change mock data/results from a previous message, immediately point out and explain those changes.

The biggest discrepancy I see in your case is that, it is impossible for count in any stats command (including chart and timechart) to give "empty" output.  So, there must be some other commands AFTER stats that gives bad output.  You need to first examine/exemplify output from chart, then scrutinize every command after that to find which one/ones.

[Vani_26]

@yuanliu , pleas find my answers below:

1. What I meant was for you to run that query standalone, not embedded in a complex search.  The purpose is to directly confirm/demonstrate that your Splunk instance performs fillnull as designed.  Nevertheless, your results with my silly test still demonstrates that fillnull works perfectly in your Splunk.

yes correct when i run the query makeresults i am getting the output same like you as shown below:

ATM	DMM	Income	Rej_app	Rej_log< Rej_app	Reject
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0

2. Your search does not return events with the following strings:  "Letter published correctley to DMM subject", "Letter rejected due to: DOUBLE_KEY", and "Letter rejected due to: UNVALID_LOG"; but it does return events with "Letter rejected due to: UNVALID_DATA_APP". (You can verify this by, e.g., search without stats and observe; there are many other ways to verify.)

Here i gave an example, but the issue is with all the 6 strings. like if i search data for last 15 mins,  if logs is present for a particular it showing the count, but if logs are not present its showing null.

3. makeresults in appendcols subsearch only fills 3 rows. (Do run it standalone so you understand what it does.)
When i run it as  standalone also i am seeing same  3rows only

ATM	DMM	Income	Rej_app	Rej_log< Rej_app	Reject
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0

4. Now that we have established that fillnull works correctly, let me point out that this latest illustrated output contains no "empty" cells in ATM (aka "Letter published correctley to ATM subject") and Rej_app (aka "Letter rejected due to: UNVALID_DATA_APP"), the only two columns where your present search actually returns results.  Can you reproduce the problem you described? (No appendcol-makeresults business.)

Here i gave an example, but the issue is with all the 6 strings. when i select a particular time range  if logs are present then i see the count otherwise it is displaying null.

5. I also want to point out that your OP illustrated drastically different column names as your later comments.  This type of unexplained differences make volunteers' mind-reading a lot more difficult.  Always explain your dataset, desired results, logic between the two in plain language (preferably without SPL), attempted SPL and actual results, then explain how actual results differ from desired results if that's not painfully obvious - oftentimes it is not to outsiders.  If you need to change mock data/results from a previous message, immediately point out and explain those changes.

I apologies  for that will make sure to provided uniform data

6. The biggest discrepancy I see in your case is that, it is impossible for countin any stats command (including chart and timechart) to give "empty" output.  So, there must be some other commands AFTER stats that gives bad output.  You need to first examine/exemplify output from chart, then scrutinize every command after that to find which one/ones.

Sorry i did not get you, can u pls let me know the query 

[yuanliu]

Can you illustrate how you obtain incomingcount rejectedcount invalidcount topcount trmpcount topiccount?  As a habit, always share how data looks like.  If you just count stuff, there should be no "empty" column. (Also, are you asking about empty row or empty column?)

For example, if you have this data set

Application	incoming	invalid	rejected	top	trmp	top
Login	come	something		some other
Login		some more		some stuff
Login				stuff stuff
Success	come in			more stuff

and you use this to produce those count columns

| stats count(incoming) as incomingcount count(rejected) as rejectedcount count(invalid) as invalidcount count(top) as topcount count(trmp) as trmpcount count(topic) as topiccount by Application

Splunk should give you

Application	incomingcount	rejectedcount	invalidcount	topcount	trmpcount	topiccount
Login	1	0	2	3	0	0
Success	1	0	0	1	0	0

Here is my data emulation to produce that mock input.

| makeresults format=csv data="Application,	incoming,	rejected,	invalid,	top,	trmp,	topic
Login, come, , something, some other
Login, , , some more, some stuff
Login, , , , stuff stuff
Success, come in, , , more stuff"