How to get a result from CSV file by searching in CSV?

[srvnkumr36]

Hi,

We have a CSV file with the master data where all the constants are stored and have four columns, in the Splunk query we will get one of the columns as a result.  need to change the outcome with another column name from the CSV file. 

Sample - We have an id like this - "58vv1578eff-985sfv294-asfd" from the query result and this need to be changed to -  2897 in the final result. 

TIA., 

Regards,

SM. 

[srvnkumr36]

index=***** sourcetype="*****" "Properties.RequestPath"="*/v1/locations*" "Properties.StatusCode">399 "Properties.TraceId"!="" | dedup "Properties.TraceId" | rex field=RenderedMessage "/v1/locations/(?<LocationUUID>[^\"]+)" | table "Properties.TraceId" LocationUUID "Properties.StatusCode"| eval IsExist=1 | append [|inputlookup tam_inf_hosts.csv | table LocationUUID | eval IsExist=0] | stats max(IsExist) as Exist by LocationUUID | where Exist=0 | stats values(store) as Stores


above is the query. CSV file will have 2 columns - Location UUID and Host. 

Location UUID needs to be replaced by the host. No there is no index  on CSV file 

[gcusello]

Hi @srvnkumr36,

the problem is that after a stats command you have only the fields in the stats, in your case you want to use  at the end of your search the field store that isn't present in the previous stats command, you you haven't it.

You have to modify the first stats command adding store.

index=***** sourcetype="*****" "Properties.RequestPath"="*/v1/locations*" "Properties.StatusCode">399 "Properties.TraceId"!="" 
| dedup "Properties.TraceId" 
| rex field=RenderedMessage "/v1/locations/(?<LocationUUID>[^\"]+)" 
| table "Properties.TraceId" LocationUUID "Properties.StatusCode"
| eval IsExist=1 
| append [
   | inputlookup tam_inf_hosts.csv 
   | table LocationUUID 
   | eval IsExist=0
   ] 
| stats max(IsExist) as Exist values(store) AS store by LocationUUID 
| where Exist=0 
| stats values(store) as Stores

Ciao.

Giuseppe

[srvnkumr36]

Still, the location UUID is not replaced by the Store number which is in the CSV file 😞 @gcusello 

[gcusello]

Hi @srvnkumr36,

sorry but I don't understand: what's the relation between LocationUUID and store?

to aggregate two data sets (one from the main search and one from the lookup) you need to have the same fieldname.

What are the firlds from the main search and what the ones from the lookup to use?

Ciao.

Giuseppe

[gcusello]

Hi @srvnkumr36,

could you share your search and the structure of your csv file?

Did you indexed it?

Ciao.

Giuseppe