topic Re: Rename & Lookup in Splunk Search

Rename & Lookup

hollybross1219 — Fri, 17 Jan 2020 00:13:44 GMT

I'm selecting data from two sourcetypes. There is a field in each sourcetype that is the same, but named differently (ie. EIN vs ein). I then want to implement a lookup from a separate csv file, but need to change the name of a column within the csv to map onto one of the EIN fields.

How do I implement two renames so that all of my EIN have field aliases that are standardized?

( index=summary_dac_tax partnerId=* tax_year=2018 ofx_appid=tt* error_code_host!=null EIN=*) OR ( index=hds* sourcetype=hdsperf* partner_id=* ein=* tax_year=2018 
| rename ein AS EIN) 
[| inputlookup ty_18_ein_conflicts.csv 
| rename ein AS EIN 
| fields EIN] 
| stats dc(intuit_tid) as total_request dc(eval(if(error_msg_service="OK",intuit_tid,null))) as successful_request by partnerId EIN 
| eval success_rate = round(100*(successful_request/total_request),2)."%"

Re: Rename & Lookup

hollybross1219 — Fri, 17 Jan 2020 00:15:12 GMT

Basically how can I get EIN type of fields to have the same alias so they map together with the lookup properly. Should I use append?

Re: Rename & Lookup

richgalloway — Fri, 17 Jan 2020 01:29:42 GMT

There are a couple of ways to do that. First, you can't use | within (). Try this.

( index=summary_dac_tax partnerId=* tax_year=2018 ofx_appid=tt* error_code_host!=null EIN=*) OR ( index=hds* sourcetype=hdsperf* partner_id=* ein=* tax_year=2018)
| rename ein AS EIN
| lookup ty_18_ein_conflicts.csv EIN
| stats dc(intuit_tid) as total_request dc(eval(if(error_msg_service="OK",intuit_tid,null))) as successful_request by partnerId EIN 
| eval success_rate = round(100*(successful_request/total_request),2)."%"

Re: Rename & Lookup

to4kawa — Fri, 17 Jan 2020 01:35:52 GMT

use coalesce

 ( index=summary_dac_tax partnerId=* tax_year=2018 ofx_appid=tt* error_code_host!=null EIN=*) OR ( index=hds* sourcetype=hdsperf* partner_id=* ein=* tax_year=2018 )
 |  eval EIN = coalesce(ein, EIN) 
 [| inputlookup ty_18_ein_conflicts.csv 
 | rename ein AS EIN 
 | fields EIN] 
 ....

Hi, how about this?

Re: Rename & Lookup

hollybross1219 — Fri, 17 Jan 2020 16:39:19 GMT

Doesn't work. The

EIN after | lookup ty_18_ein_conflicts.csv doesn't work. Also, I understand with a .csv file I need inputlookup function

Re: Rename & Lookup

hollybross1219 — Fri, 17 Jan 2020 16:45:42 GMT

tried this and didn't work. Error output is "Error in 'eval' command: The expression is malformed." Doesn't coalesce evaluate the value of a field? My problem is changing the alias of the field itself.

Re: Rename & Lookup

hollybross1219 — Fri, 17 Jan 2020 17:20:32 GMT

What I settled with:

  
( index=summary_dac_tax partnerId=* tax_year=2018 ofx_appid=tt* error_code_host!=null EIN=*) OR ( index=hds* sourcetype=hdsperf* partner_id=* ein=* tax_year=2018 ) 
| stats dc(intuit_tid) as total_request dc(eval(if(error_msg_service="OK",intuit_tid,null))) as successful_request by partnerId EIN 
| eval success_rate = round(100*(successful_request/total_request),2)."%" 
| rename EIN as ein 
| lookup ty_18_ein_conflicts.csv ein 
| fields partnerId, ein, total_request, successful_request

Follow up question though. The output for this is GIANT. Is there any dedup methods / tricks that aren't already covered in stats line?

Re: Rename & Lookup

richgalloway — Fri, 17 Jan 2020 20:15:16 GMT

lookup and inputlookup are two commands for getting data from lookup files.

You say EIN doesn't work. What error do you get? What is the correct field name in the CSV file?

Re: Rename & Lookup

to4kawa — Sat, 18 Jan 2020 00:35:31 GMT

Error output is "Error in 'eval' command: The expression is malformed."

This result is not where I described it.

Doesn't "coalesce" evaluate the value of a field?

Yes, coalesce can alias other field name.

|  eval EIN = coalesce(ein, EIN)

As this result, both ein and EIN is same field EIN
This order is evaluated in the order of the arguments.
If the event has ein , the value of ein is entered, otherwise the value of the next EIN is entered.