Rename & Lookup

[hollybross1219]

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)."%"

[hollybross1219]

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?

[to4kawa]

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?

[hollybross1219]

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.

[to4kawa]

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.

[richgalloway]

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)."%"

[hollybross1219]

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

[richgalloway]

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?

[hollybross1219]

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?