Dashboards & Visualizations

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

learningsplunk
Path Finder

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 : 

EquipmentLocation_within_buildingStreetPriceCI_Number
TV standfirst floor1404 Bay street$29.99121
Wireless headsetsecond floor1404 Bay street$39.99223
Laptopsecond floor29th Bay Pl$999.993334
Wireless Microphonefirst floor404 Bay Street$9.995552

 

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 : 

EquipmentLocation_within_buildingStreetPriceCI_Number
TV standfirst floor1404 Bay street$29.99121
Wireless headsetsecond floor1404 Bay street$39.99223

 

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

Labels (1)
Tags (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

learningsplunk
Path Finder

@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_NumberEquipmentLocation_within_buildingPriceStreetexpr
121TV standfirst floor$29.991404 Bay street1404|Bay|street
223Wireless headsetsecond floor$39.991404 Bay street1404|Bay|street
3334Laptopsecond floor$999.9929th Bay Pl1404|Bay|street
5552Wireless Microphonefirst floor$9.99404 Bay Street1404|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?

0 Karma

learningsplunk
Path Finder

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 streetsearching 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......

items 5.png

 

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 ?

 

Tags (1)
0 Karma

learningsplunk
Path Finder

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.

 

 

 

 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...