BotS - Error in 'lookup' command: Could not find all of the specified destination fields in the lookup table

[suspense]

Hi,

I am doing Boss of the SOC v1 and I stuck on question, where I need to use lookup. I imported .csv file ad here are my commands:

index=botsv1 dest=192.168.250.70 src="23.22.63.114" http_method=POST
| rex field=form_data "passwd=(?<passwd>[a-zA-Z]{6})"
| lookup coldplay.csv Song as passwd OUTPUTNEW song

Error I get:

Error in 'lookup' command: Could not find all of the specified destination fields in the lookup table.

Here I can find writeup with similar command: https://www.aldeid.com/wiki/TryHackMe-BP-Splunk/Advanced-Persitent-Threat##7_-_One_of_the_passwords_...

I tried to run it and I received the same error.

Do you know how can I solve it? 

[suspense]

This is how my .csv looks like. How can I find which fields I have? I thought name of the column is considered a field? 

 

[yuanliu]

Yes, the field name is Song, not song.  Also, if the lookup only contains one field, what do you expect to look up?  The purpose of a lookup is to associate a known field value in search result to one or more field values that are only known in the lookup.

[suspense]

I am trying to do BotS and answer on question:

One of the passwords in the brute force attack is James Brodsky’s favorite Coldplay song. Which six character song is it?

Basically I am trying to import list of Coldplay songs in .csv and compare it with password used for brute force attack. 

Here is the answer how the query should look like (probably?):

https://www.aldeid.com/wiki/TryHackMe-BP-Splunk/Advanced-Persitent-Threat##7_-_One_of_the_passwords_... 

index=botsv1 sourcetype=stream:http form_data=*username*passwd*
| rex field=form_data "passwd=(?<userpassword>\w+)"
| eval lenpword=len(userpassword)
| search lenpword=6
| eval password=lower(userpassword)
| lookup coldplay.csv song as password OUTPUTNEW song
| search song=*
| table song

[suspense]

BTW. If you look at this website -theirs syntax with lookup and list that they attached in a link - this syntax just cannot work. I tested in my lab and it does not work. But you helped me to understand that last 'song' must be 'Song' 🙂 Thank you.

[yuanliu · Accepted solution]

That's because your coldplay.csv file doesn't contain a field named song.  OUTPUT or OUTPUTNEW can only take what is found in the lookup.  If your lookup contains a field name poem and you want to rename it song, you have to reference poem first, like

| lookup coldplay.csv Song AS passwd OUTPUTNEW poem AS song