Return matching fields from two sources

[cpowell]

I have two different sources that I need to find and return all matching instances of a field. Unfortunately, the fields both represent a name, but in different formats. I have tried multiple incarnations of match, subsearches and joins and cannot get it to work.

Sample source one. This is from a csv file. The headers are Name,Permissions,Domain,Group_Name. And the host name would be server1 and sourcetype is csv-1.

Chris Powell,Full,TESTING,Domain Admin

Sample source two is more csv data. The headers are Name,Title,Department Description,Manager,Hire Date,Term Date,Location. The sourcetype is csv-2. Host name is server2.

"POWELL, CHRISTOPHER",KEYBOARD MONKEY,IT,"PILSON, BOB",4/16/12, ,Hollywood - CA

I have tried a few different queries along the lines of:

host=server1 | eval names=split(Name," ") | eval last=mvindex(names,-1) | eval last=lower(last) | table last Group_Name | join last [search sourcetype="csv-2" | eval names1=split(Name,", ") | eval last1=mvindex(names1,0) | eval last1=lower(last1) | table last1]

I would like to return the matching names and the Group_Name along the lines of:

Name               Group_Name
powell             Domain_Admin

[lguinn2]

Well, in your subsearch, you are using the field "last1" - it needs to be "last", just like the outer search. Try

host=server1 
| eval names=split(Name," ") | eval last=mvindex(names,-1) | eval last=trim(lower(last))
| fields last Group_Name 
| join last 
    [search sourcetype="csv-2" 
    | eval names1=split(Name,", ") | eval last=mvindex(names1,0) | eval last=trim(lower(last))
    | fields last ]
| table last Group_Name
| rename last as "Last Name" Group_Name as "Group Name"

[lguinn2]

Answer updated to including trimming white space fromt he last name...

[cpowell]

Each search individually returns the correct lowercase last name, but the join is not returning anything.