Splunk Search

Return matching fields from two sources

cpowell
New Member

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
0 Karma

lguinn2
Legend

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"
0 Karma

lguinn2
Legend

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

0 Karma

cpowell
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...