topic Re: Need to get a restult from CSV file by searching in CSV in Getting Data In

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

srvnkumr36 — Tue, 16 Aug 2022 13:15:00 GMT

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.

Re: Need to get a restult from CSV file by searching in CSV

gcusello — Tue, 16 Aug 2022 08:03:00 GMT

Hi @srvnkumr36,

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

Did you indexed it?

Ciao.

Giuseppe

Re: Need to get a restult from CSV file by searching in CSV

srvnkumr36 — Tue, 16 Aug 2022 08:06:26 GMT

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

Re: Need to get a restult from CSV file by searching in CSV

gcusello — Tue, 16 Aug 2022 08:22:17 GMT

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.

Ciao.

Giuseppe

Re: Need to get a restult from CSV file by searching in CSV

srvnkumr36 — Tue, 16 Aug 2022 09:45:41 GMT

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

Re: Need to get a restult from CSV file by searching in CSV

gcusello — Tue, 16 Aug 2022 14:20:00 GMT

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