match between field with delimited num values and a number field

[praveenvvn]

Hello, am trying to run a query like below:

basequery | where match(stringFieldConsistingOfNumsDelimitedBy#, numField)

Apparently the numField is not able to evaluate and yield rows.

Tried converting numField as below, but returns no rows:

| eval strConvFld = tostring(numField)
| where match(stringFieldConsistingOfNumsDelimitedBy#, strConvFld)

When I inspect strConvFld, it's coming out as Null.

Am not sure if my approach is correct.

Any help is much appreciated. Pls advise.

Thanks.

[praveenvvn]

@ITWhisperer @bowesmana 

Thanks for your feedback and suggestions 👍

In reality the numField is like below:

Am not sure why, but none of the suggestions & examples from previous replies worked when I referenced the above field name as-is.

Conversions when tried resulted as below:

tostring(msg.Results.ErrorCode) resulted in Null
tonumber(msg.Results.ErrorCode) resulted in a blank output
eval isNum=if(isnum(msg.Results.ErrorCode),"IsNum","NotIsNum") resulted in NotIsNum

I then tried rename and all suggestions & trials with the renamed fieldname in the query from previous replies started to yield.

| rename msg.Results.ErrorCode as errorcode

 Per Rename command it's used to give meaningful name to a field.

The resultset is in Json and the field am interested in is a sub-level lookup. Is this not permissible for a query of this kind?

Am puzzled! I know am working with a num field, but unsure why Splunk is responding differently 😕

Is this related to the Splunk version?

Any insights.

Thanks.

[bowesmana]

@praveenvvn 

Ah!!!

This is why is useful to post your exact query when asking questions.

The reason why it didn't work with yourtostring(msg.Results.ErrorCode) is that the . character is not allowed in field names when used in eval statements. In order to use no standard characters in eval statements, you need to quote the field names with single quote marks like this

tostring('msg.Results.ErrorCode')

So always when using json nested field names you will need to do that. Rename works as it removes those characters.

[praveenvvn]

@bowesmana 👍

That did the trick.

Thanks.

[ITWhisperer]

Your approach is correct. Do you have examples of your string field and number field?

[praveenvvn]

They are basically error codes like below. And the numField is a field in the log event.

stringFieldConsistingOfNumsDelimitedBy# = "401#403#404#500"
numField = 404

My another approach that didn't yield either:

basequery | eval errorCodes=trim("401#403#404#500"), errorCodesArr=split(errorCodes,"#") | where IN(errorCodesArr, errorCode)

[bowesmana]

@praveenvvn 

and this example shows your technique is correct, even if numField is a number

| makeresults 
| eval numField=404
| where match("401#403#404#500", numField)
| eval isNum=if(isnum(numField),"IsNum","NotIsNum")

 whereas you can see that this example will not work if the number is a string with trailing space

| makeresults 
| eval numField="404 "
| where match("401#403#404#500", numField)

and even using tonumber() will not fix this, as it does not work

| makeresults 
| eval numField="404 "
| eval numField=tonumber(numField)
| where match("401#403#404#500", numField)

 whereas when using trim, it will make the numField a number and it works

| makeresults 
| eval numField="404 "
| eval numField=trim(numField)
| where match("401#403#404#500", numField)
| eval isNum=if(isnum(numField),"IsNum","NotIsNum")

[bowesmana]

This one won't work because you cannot use IN with a multivalue field.

This would work

| where !isnull(mvfind(errorCodesArr, errorCode))

[ITWhisperer]

@bowesmana IN does work on mv fields (at least in my version of splunk) Try:

| makeresults | eval events=trim("401#403#404#500")
| eval errorCodesArr=split(events,"#")
| eval number=404
| where IN(errorCodesArr,number)

[bowesmana]

@ITWhisperer 

Yes, my bad you are right, I think I was getting the various IN contexts confused (search/where and inputlookup where)

[ITWhisperer]

This approach should work too. How are you extracting numField? Perhaps it isn't working as you expect?