Splunk Search

way to use case insensitive fields - Not Value

Shariq
Engager

Mydata is like below where the customerNumber can come like CustomerNumber or customernumber or CUSTOMERNUMBER

AND isoCountryCode can come as IsoCountryCode, ISOCountryCode or any other combination.

 

Now during field extraction Splunk considers all these fields as seperate. though while writing query i want to consider all these fields as one.

Environment = prod-dmz-usch01 | API = testapi| RequestURI = /test/v5/tesdt/10-12345?customerNumber=01-12345&isoCountryCode=US | ProxyRequestFlowName = testDetails-OpenAPIv3GetVerb.

 

My query is as below:

 

index=test sourcetype="testsamples" testapi "ProxyRequestFlowName = testDetails-OpenAPIv3GetVerb"
| search isocountrycode=US OR isoCountryCode=US   -- this seems to be taking care of multiple values but it is not a good idea to write each field here, how to handle all scenario's ?
| bucket _time span="24h"
| chart count by customerNumber where count in top100 -- i am able to give only one value of customer number here , how can i handle all use cases ?

Labels (2)
1 Solution

bowesmana
Influencer

@Shariq 

There are a number of ways to resolve this. 

1. Fix the data 🙂

2. Set up a new calculated field which will create your preferred field name based on the others, for example like this

| eval customerNumber=coalesce(customerNumber, CUSTOMERNUMBER, CusomerNumber, customernumber, customerNo)
| eval isoCountryCode=coalesce(isoCountryCode, isocountrycode, ISOCountryCode)

In the calculated field definition, you just use the coalesce() part and put in all the field variants you want to normalise to the preferred field name.

Set up one calculated field for each of the fields you want to normalise.

Then in your search you will only need to do

index=test sourcetype="testsamples" testapi "ProxyRequestFlowName = testDetails-OpenAPIv3GetVerb" isoCountryCode=US 

You can read about the coalesce function here

https://docs.splunk.com/Documentation/Splunk/8.1.0/SearchReference/ConditionalFunctions#coalesce.28X...

There are other ways to do this, but this is probably the simplest and most typical way to solve this problem. If you come across a new variant then you can always add it to the coalesce list.

Setting up aliases is another way to do this, but it's not so simple to manage multiple aliases to the same base name.

Hope this helps.

 

View solution in original post

Shariq
Engager

eval coalesce worked well for me without changing the data at ingestion. thanks.

0 Karma

inventsekar
Super Champion

yeah, exactly, .. the coalesce is a simple, superb command, many of the new Splunkers(including me ;)) are not aware of. 

 

Splunk guys should include these basic commands into the Splunk Fundamentals 1/2 training! 

tagging some splunk employees... @esix_splunk @gkanapathy @yannK @jbsplunk , thanks.!

0 Karma

bowesmana
Influencer

@Shariq 

There are a number of ways to resolve this. 

1. Fix the data 🙂

2. Set up a new calculated field which will create your preferred field name based on the others, for example like this

| eval customerNumber=coalesce(customerNumber, CUSTOMERNUMBER, CusomerNumber, customernumber, customerNo)
| eval isoCountryCode=coalesce(isoCountryCode, isocountrycode, ISOCountryCode)

In the calculated field definition, you just use the coalesce() part and put in all the field variants you want to normalise to the preferred field name.

Set up one calculated field for each of the fields you want to normalise.

Then in your search you will only need to do

index=test sourcetype="testsamples" testapi "ProxyRequestFlowName = testDetails-OpenAPIv3GetVerb" isoCountryCode=US 

You can read about the coalesce function here

https://docs.splunk.com/Documentation/Splunk/8.1.0/SearchReference/ConditionalFunctions#coalesce.28X...

There are other ways to do this, but this is probably the simplest and most typical way to solve this problem. If you come across a new variant then you can always add it to the coalesce list.

Setting up aliases is another way to do this, but it's not so simple to manage multiple aliases to the same base name.

Hope this helps.

 

View solution in original post