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
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"
Answer updated to including trimming white space fromt he last name...
Each search individually returns the correct lowercase last name, but the join is not returning anything.