Searching csv files with an input that has multiple words with white spaces separated between them. Uses a single token.

[learningsplunk]

Seems to be an odd issue when using tokens to search an entire csv file. I don't know if this is built into splunk on purpose but......

Example: 

stuff.csv file contains : 

Equipment	Location_within_building	Street	Price	CI_Number
TV stand	first floor	1404 Bay street	$29.99	121
Wireless headset	second floor	1404 Bay street	$39.99	223
Laptop	second floor	29th Bay Pl	$999.99	3334
Wireless Microphone	first floor	404 Bay Street	$9.99	5552

And the user has an input text box  where they can search the entire CSV file. with a token of : 

*$Item$*

The issue is that IF a user  put into the text box  1404 Bay street . They won't get any results back. However if  a user puts in 1404 , they will get results back......

is there  anyway to modify the search query (within a statistics table ) of  : " |inputlookup stuff.csv| search Equipment=*$Item$* OR Location_within_building = *$Item$* OR  Street = *$Item$*  OR Price = *$Item$* OR CI_Number = *$Item$* 

so that if  a user puts in 1404     or    1404 Bay    or  1404 Bay street 

they would get a return of : 

Equipment	Location_within_building	Street	Price	CI_Number
TV stand	first floor	1404 Bay street	$29.99	121
Wireless headset	second floor	1404 Bay street	$39.99	223

instead of getting nothing as a return value when searching the entire csv file? 

[bowesmana · Accepted solution]

The search *$Item$* needs to be quoted in the right hand side of the match, because if you enter 1404 Bay Street, it would look like you are giving the command

| search Equipment=1404 Bay Street

which won't give you your answer.

One thing you can do for this type of input is to do 

| inputlookup stuff.csv
| eval expr=replace("$item$"," ","|")
| where (match(Equipment,expr) OR match(Location_within_building,expr) OR match(Street,expr) OR match(Price,expr) OR match(CI_Number,expr))

where this is then creating a regular expression with all the entered words used as an OR search for each field. If you don't want to do OR within words, then just expr="$item$"

Using this syntax

$item|s$

is the same as quoting it.

[learningsplunk]

@bowesmana  

Thanks for the reply, although when i do try the new query, as per the attached screenshot, this is what i'll get now : 

When i put in 1404 Bay street,  I will get this as a return  :

CI_Number	Equipment	Location_within_building	Price	Street	expr
121	TV stand	first floor	$29.99	1404 Bay street	1404|Bay|street
223	Wireless headset	second floor	$39.99	1404 Bay street	1404|Bay|street
3334	Laptop	second floor	$999.99	29th Bay Pl	1404|Bay|street
5552	Wireless Microphone	first floor	$9.99	404 Bay Street	1404|Bay|street

While using this query :

| inputlookup stuff.csv
| eval expr=replace("$item$"," ","|")
| where (match(Equipment,expr) OR match(Location_within_building,expr) OR match(Street,expr) OR match(Price,expr) OR match(CI_Number,expr))

a new column is created and I will get everything back as a return now.... 

Seems like this inserts a new field  ( for whatever I've typed in be it 1404 Bay Street or 1404 Bay ) , and also when using wildcards such as * , it will not return anything back either. 

This is the raw xml : 

<form>
<label>Where are the items located within the city</label>
<description>Location of office equipment</description>
<fieldset submitButton="true">
<input type="text" token="item">
<label>Input here:</label>
<default>*</default>
</input>
</fieldset>
<row>
<panel>
<title>Where's the equipment?</title>
<table>
<title>Equipment location</title>
<search>
<query>| inputlookup stuff.csv
| eval expr=replace("$item$"," ","|")
| where (match(Equipment,expr) OR match(Location_within_building,expr) OR match(Street,expr) OR match(Price,expr) OR match(CI_Number,expr))</query>
<earliest>$earliest$</earliest>
<latest>$latest$</latest>
</search>
<option name="count">10</option>
<option name="drilldown">none</option>
<option name="refresh.display">progressbar</option>
</table>
</panel>
</row>
</form>

Perhaps white spaces in an input box for a dashboard ...just like when using white spaces in  a field for an eval command , isn't a feasible option  for dashboards?

[learningsplunk]

I'm assuming that  using replace for 

| eval expr=replace("$item$"," ","|")

Seems like it just replaces white spaces with  |  and then the eval command creates a new field....

However..

 When searching for a single value ( be it 1404 or Bay )  , this will  return the correct  rows , and it will create a new field called " expr"  (granted that'll occur when using eval....),

but when searching for the word street now ( i'm assuming that with eval statements, it would naturally make searches case sensitive ) 

searching for the word street

it only returns  2 rows not 3....  

However ,though when searching for 1404 bay or Bay street or 1404 bay street, an incorrect return will still occur......

Is it because by using replace, we're using regex which means that case sensitivity is enforced as well as the exact number of white spaces, etc that one types into the input search text field ?

[learningsplunk]

Played around with the query abit and 

Seems like i was able to modify it to  :

| inputlookup stuff.csv
| eval expr=replace("(?i)$item$"," "," ")
| where (match(Equipment,expr) OR match(Location_within_building,expr) OR match(Street,expr) OR match(Price,expr) OR match(CI_Number,expr))

so that  (?i) ignores the case for the actual search .... and by putting in  $item$"," "," ")   instead of  $item$"," ","|") .

It works. just had to remove the | .   Used (?i) within the expression to include case insensitivity.

[bowesmana]

@learningsplunk 

The eval will create a new field expr. To remove that field when you have finished using it, just use

| fields - expr

As for case sensitivity - the match statement is just a regex, so as you have discovered, adding (?i) in front of the regex will make it case insensitive.

The intention of the replace with | symbol was to make the search OR within words. If that is NOT what you want then you would not need the replace statement at all. You only need to add the (?i) to the start, in which case you would do

| eval expr="(?i)$item$"

which is simply prepending the regex modifier to the front of the token.  The reason to do this using a field is to avoid having to do it many times, once for each of the field OR statements - although that can be done.