Why are some searches only rarely executed without any warning or error?

[hbusch]

Hey everyone

i have a little bit of a problem with some of my searches, as I am only rarely able to execute them. Splunk Web just keeps showing the "new search" page (in a dashboard it shows "Could not create search") without any error or warning occurring.
I will try to explain what I am trying to do, maybe that helps finding my mistake. So here is one of the searches which failed this way just now:

| inputcsv fileone.csv where condition | eval fieldone_first=substr(fieldone,1,2) | lookup filetwo.csv fieldtwo AS fieldone_first OUTPUTNEW lat lon

I want to match zip codes in a field from one csv to another. While the first file is basically the origin, the second csv is a table matching zip codes to coordinates. Because it is rather incomplete I would like to only match the first two characters of the zip code
I have currently imported two csv files into Splunk which are working fine on their own. Now I try to match the first two characters of the one table to a field of the other table.

I hope anyone here has an idea.

[DalJeanis · Accepted solution]

Try this and see what happens. Change "75" to any 2-digit value that is in your table, and XX to any value that is NOT in your table. This will test whether the basic lookup is working, with one match record and one unmatched record.

| makeresults 
| eval fieldone_first="75 XX"
| makemv fieldone_first
| mvexpand fieldone_first 
| lookup filetwo.csv fieldtwo AS fieldone_first 

...then if that works, then change out the last line for this and see what happens...

| lookup filetwo.csv fieldtwo AS fieldone_first OUTPUTNEW lat lon

[hbusch]

Thank you for your suggestion, so far this works, great! It seems that my error is located somewhere in the search, I will have a further look, maybe I can get it to work now.

Edit: It works now! Thank you so much!

[DalJeanis]

@hbusch - Please post the code that finally worked for you, just in case anyone else experiences a similar issue and finds this thread.

[hbusch]

I ended up with exactly the same search. I suppose it was hidden in the details, I am unsure about what I can share, because they are related directly to my work. In theory the original search should work, but maybe there is something I am missing here. If there would have been a difference I would have gladly shared it already.
My final search looks like this (I have left a few more details in):

| inputcsv origin.csv where <condition>
| eval zip_data_first=substr(zip,1,2)
| lookup zip.csv zip_first as zip_data_first OUTPUTNEW lat lon

and because I am ending up with multiple matches I added this (maybe someone make a good use of it even though my problem has somehow just vanished):

| stats avg(lat) avg(lon) by zip_data_first, <addtl. fields>

[niketn]

@hbusch.. Please accept DalJeanis' answer if it has helped you resolve your issue and mark this question as answered.