Remove spacing from records after inputlookup

[moinyuso96]

The contents of my lookup file, test12345.csv is shown below.

ProductNumber,SerialNumber,StatusDateTime,Status

"A12345 ","MA00000001 ","2021-08-31 01:30:47 ","SHIPPED "

There is some space found "   " at the end of each record. The inputlookup would capture all the records with a spacing at the end which disrupts my joins to work properly. Is there anyway I can remove the spacing at the end of each record after using inputlookup?

ProductNumber =A12345

SerialNumber =MA00000001

StatusDateTime =2021-08-31 01:30:47

Status=SHIPPED

[moinyuso96]

Hi @gcusello , thanks for answering.

However, I am not really sure where to insert the queries you gave me. Currently I am still have issues with the joins and inputlookup.

My join and inputlookup query is as follows:

 ... |  join type=left ProductNumber Serial [ | inputlookup test12345.csv|  rename SerialNumber as Serial] | ...

[gcusello · Accepted solution]

Hi @moinyuso96,

inside square brackets:

 ... 
|  join type=left ProductNumber Serial [ 
     | inputlookup test12345.csv|  rename SerialNumber as Serial
     | rex field=ProductNumber "(?<ProductNumber>.*)\s+$"
     | rex field=SerialNumber "(?<SerialNumber>.*)\s+$"
     | rex field=StatusDateTime "(?<StatusDateTime>.*)\s+$"
     | rex field=Status "(?<Status>.*)\s+$"
     ] 
| ...

Remember only one thing:

in this case, you have the problem of space in each field, so you have to modifiy fields using regexes or evals, but usually you can use the lookup command instead the join (the lookup command is a left join with a lookup).

In other words, remeber that Splunk isn't an SQL DB, so use join only when you haven't any other solution, because it's a very slow command.

For this reason, I'd try to solve the problem in the lookup, eventually modifying values in lookup.

I don't know how lookup is generated but you could correct the values in the lookup using a search, eventually to schedule.

Something like this:

| inputlookup test12345.csv
| rex field=ProductNumber "(?<ProductNumber>.*)\s+$"
| rex field=SerialNumber "(?<SerialNumber>.*)\s+$"
| rex field=StatusDateTime "(?<StatusDateTime>.*)\s+$"
| rex field=Status "(?<Status>.*)\s+$"
| table ProductNumber SerialNumber StatusDateTime Status 
| outputlookup test12345.csv

In this way, you can use a simpler lookup command:

| index=your_index
| lookup test12345.csv ProductNumber SerialNumber AS Serial OUTPUT StatusDateTime Status
| ...

Ciao.

Giuseppe

[gcusello]

Hi @moinyuso96,

you have two ways:

• the trim function in the eval command,
• the regex.

using trim

| eval ProductNumber=trim(ProductNumber)
| eval SerialNumber=trim(SerialNumber)
| eval StatusDateTime=trim(StatusDateTime)
| eval Status=trim(Status)

using regex you have to add a row for each field:

| rex field=ProductNumber "(?<ProductNumber>.*)\s+$"
| rex field=SerialNumber "(?<SerialNumber>.*)\s+$"
| rex field=StatusDateTime "(?<StatusDateTime>.*)\s+$"
| rex field=Status "(?<Status>.*)\s+$"

Ciao.

Giuseppe