Splunk Search

Empty rows in Table

Vani_26
Path Finder

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.

Applicationincomingcountrejectedcountinvalidcounttopcounttrmpcounttopiccount
Login1 25  
Success8 02  
Error0 010  
logout2 04  
Debug0 022  
error-state0 045  
normal-state0 024  

 

Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

What's your SPL?

0 Karma

Vani_26
Path Finder

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
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

 

0 Karma

Vani_26
Path Finder

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

ATMDMMIncomeRej_appRej_log< Rej_appReject
000000
000000
000000

 

0 Karma

Vani_26
Path Finder

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:

ApplicationATMDMMIncomeRej_appRej_logReject
Login1000200
Success1200100
Error23001100
Debug2  3  
logout1  50  
error-state61  20  
normal-state1  10  
0 Karma

yuanliu
SplunkTrust
SplunkTrust
@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.

0 Karma

Vani_26
Path Finder

@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:

ATMDMMIncomeRej_appRej_log< Rej_appReject
000000
000000
000000

 

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

ATMDMMIncomeRej_appRej_log< Rej_appReject
000000
000000
000000

 

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 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

Applicationincominginvalidrejectedtoptrmptop
Logincomesomething some other  
Login some more some stuff  
Login   stuff stuff  
Successcome 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

Applicationincomingcountrejectedcountinvalidcounttopcounttrmpcounttopiccount
Login102300
Success100100

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"

 

 

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...