Splunk Search

SPL: Clicking sort order in a table, doesn't display null values

genesiusj
Builder

Hello,
Here is my SPL (although I don't believe it is necessary(?) as this is a (mis)functioning of SPL in general).

index="pay_test"
    AND host IN ("pay2", "pay1")
AND (appName=TYTR OR APPLICATIONNAME=TYTR)
| sort +_time
| eval refid = mvdedup(mvappend(lclrefid, LCLREFID)) 
| eval tok = mvdedup(mvappend(token, TOKEN)) 
| eval app = mvdedup(mvappend(appName, APPLICATIONNAME)) 
| transaction refid, tok
| table _time, app, tok, refid, AMOUNT, TOTALAMOUNT, FAILCODE, ERRORCODE, _raw

When I click arrows over the AMOUNT column, the table is sorted in descending order. I notice the TOTALAMOUNT column has a blank (null?) value in its column. I want to view all of the events that are blank/null. When I click the arrows in the TOTALAMOUNT column, the table is sorted in descending order. When I click the arrow again, the table is ordered in ascending order. Where do the blanks/nulls come in?
Yes, I could write code to filter on the blank/null for the TOTALAMOUNT column. But what if I want to sort on any of the other 5 columns and find these blank/null values? What if a future table has tens or hundreds of columns? I should not have to write code to handle each column.
In most of the IT apps I have used in my time (Excel, or any spreadsheet; Word; etc.) default sort behavior is blank/null values on one end of a sort, or another. Why can't Splunk do this?
OR is this something that is configurable through one of the .conf files?

Thanks in advance for your wisdom.
God bless,
Genesius

0 Karma
1 Solution

genesiusj
Builder

@woodcock
Apologies for.... I know, you read this several times from me today. 🙂
I'm thankful for your, and others' assistance.

Ok. I had to sanitize the data, so if a field name or value seems incorrect, just ask me to clarify.

Below is the results (_raw) of my code followed by the results (_raw) of yours. Not using the Answers code sample format in order to highlight particular values and/or fields.

INFO:2019-10-21 09:17:04,917 >> util.GeneralTimeTracker --> FINISHED: preparePayment [2] - |appName = TYTR| lclrefid = 33062| token = 236f3dff| errorCode = 0| errorMessage = null| - Total Time 0.322 seconds. ==========

DEBUG:2019-10-21 09:17:04,915 >> client.jms.DatabaseInsertMBClient --> LCLREFID=33062
AMOUNT=960.63
ERRORCODE=0
TRANSACTION_TIME=0.32
TOKEN= 236f3dff

INFO:2019-10-21 09:17:04,595 >> util.GeneralTimeTracker --> STARTED : preparePayment [1] - |appName = TYTR | localrefid = 33062| ==========

My code returned 3 events in this transaction. Here are the column results AMOUNT and TOTALAMOUNT for this transaction. There is no DEBUG with the TOTALAMOUNT field; therefore, TOTALAMOUNT is null or blank.
AMOUNT TOTALAMOUNT
960.63


Here is the result of your code. Only 2 events returned; the INFO…STARTED event is missing.

DEBUG:2019-10-21 09:17:04,915 >> client.jms.DatabaseInsertMBClient --> LOCALREFID=33062
AMOUNT=960.63
ERRORCODE=0
TRANSACTION_TIME=0.32
TOKEN= 236f3dff

INFO:2019-10-21 09:17:04,917 >> util.GeneralTimeTracker --> FINISHED: preparePayment [2] - |appName = TYTR | localrefid = 33062| token = 236f3dff| errorCode = 0| errorMessage = null| - Total Time 0.322 seconds. ==========

Here are the AMOUNT and TOTALAMOUNT columns. Even though there is no DEBUG with the TOTALAMOUNT field, your code calculated the TOTALAMOUNT to be equal to the AMOUNT. This is inaccurate.
AMOUNT TOTALAMOUNT
960.63 960.63

Your code also removed all null values from the TOTALAMOUNT column.


Note: The difference between the AMOUNT and TOTALAMOUNT fields is AMOUNT is calculated without a service charge. From these results we need to sort on the TOTALAMOUNT field to view those null / blank values.


As an aside, it looks like the code you wrote is doing what transactions does but without the 5000 event limitation. Is there any documentation on this so I can better understand the process?

Thanks again for all your help. Not only on this post, but on several where you have chimed.

God bless,
Genesius

View solution in original post

0 Karma

genesiusj
Builder

@woodcock
Apologies for.... I know, you read this several times from me today. 🙂
I'm thankful for your, and others' assistance.

Ok. I had to sanitize the data, so if a field name or value seems incorrect, just ask me to clarify.

Below is the results (_raw) of my code followed by the results (_raw) of yours. Not using the Answers code sample format in order to highlight particular values and/or fields.

INFO:2019-10-21 09:17:04,917 >> util.GeneralTimeTracker --> FINISHED: preparePayment [2] - |appName = TYTR| lclrefid = 33062| token = 236f3dff| errorCode = 0| errorMessage = null| - Total Time 0.322 seconds. ==========

DEBUG:2019-10-21 09:17:04,915 >> client.jms.DatabaseInsertMBClient --> LCLREFID=33062
AMOUNT=960.63
ERRORCODE=0
TRANSACTION_TIME=0.32
TOKEN= 236f3dff

INFO:2019-10-21 09:17:04,595 >> util.GeneralTimeTracker --> STARTED : preparePayment [1] - |appName = TYTR | localrefid = 33062| ==========

My code returned 3 events in this transaction. Here are the column results AMOUNT and TOTALAMOUNT for this transaction. There is no DEBUG with the TOTALAMOUNT field; therefore, TOTALAMOUNT is null or blank.
AMOUNT TOTALAMOUNT
960.63


Here is the result of your code. Only 2 events returned; the INFO…STARTED event is missing.

DEBUG:2019-10-21 09:17:04,915 >> client.jms.DatabaseInsertMBClient --> LOCALREFID=33062
AMOUNT=960.63
ERRORCODE=0
TRANSACTION_TIME=0.32
TOKEN= 236f3dff

INFO:2019-10-21 09:17:04,917 >> util.GeneralTimeTracker --> FINISHED: preparePayment [2] - |appName = TYTR | localrefid = 33062| token = 236f3dff| errorCode = 0| errorMessage = null| - Total Time 0.322 seconds. ==========

Here are the AMOUNT and TOTALAMOUNT columns. Even though there is no DEBUG with the TOTALAMOUNT field, your code calculated the TOTALAMOUNT to be equal to the AMOUNT. This is inaccurate.
AMOUNT TOTALAMOUNT
960.63 960.63

Your code also removed all null values from the TOTALAMOUNT column.


Note: The difference between the AMOUNT and TOTALAMOUNT fields is AMOUNT is calculated without a service charge. From these results we need to sort on the TOTALAMOUNT field to view those null / blank values.


As an aside, it looks like the code you wrote is doing what transactions does but without the 5000 event limitation. Is there any documentation on this so I can better understand the process?

Thanks again for all your help. Not only on this post, but on several where you have chimed.

God bless,
Genesius

View solution in original post

0 Karma

woodcock
Esteemed Legend

Do not use transaction. Try this:

index="pay_test" AND host IN ("pay2", "pay1") AND (appName="TYTR" OR APPLICATIONNAME="TYTR")
| sort 0 +_time
| eval lclrefid = mvdedup(mvappend(lclrefid, LCLREFID)) 
| eval token= mvdedup(mvappend(token, TOKEN)) 
| eval appName = mvdedup(mvappend(appName, APPLICATIONNAME)) 
| stats range(_time) AS duration min(_time) AS _time list(_raw) AS _raw list(appName) AS app sum(AMOUNT) AS TOTALAMOUNT list(AMOUNT) AS AMOUNT list(FAILCODE) AS FAILCODE list(ERRORCODE) AS ERRORCODE BY lclrefid token
| rename lclrefid AS refid token AS tok
| table _time, app, tok, refid, AMOUNT, TOTALAMOUNT, FAILCODE, ERRORCODE, _raw
0 Karma

gaurav_maniar
Builder

Hi @genesiusj ,

It seems, it is issue with Splunk or it is designed in this way.
To overcome this issue, the workaround is to replace all null values with some values.

Add fillnull value=" ", it will replace all null values with space for all fields or you can specify specific field fillnull value=" " field_1 field_2
Now if you sort any fields, the null values will be considered first in ascending order and last in descending order.

Note - if you use it on any numeric field, it will get converted to text field. In case of using stats or any calculation, better to use it afterwards.

accept and up-vote the answer if it helps.

0 Karma

genesiusj
Builder

@gaurav_maniar
Thanks for the reply.
Replacing numeric nulls with a default string value is not the way I want to go. If a calculation needs to be performed against this field in the future, I don't want to have to deal with converting from string to number. It is the same inconvenience with formatting currency with commas and a dollar sign. I have revert the string back to a numeric any time I want to add them.
I could use a numeric default 0. But again in the future the application owner might be interested in when 0's being entered by the customer or programmer, vs. nulls.
Splunk should make nulls similar to every other application out there.
Thanks again.
God bless,
Genesius

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.