topic Re: Combine two columns in a table from two separate searches (where one is a subset of the other) in Splunk Search

Combine two columns in a table from two separate searches (where one is a subset of the other)

splunkuser2127 — Fri, 01 May 2020 14:50:18 GMT

I'm currently running the query (changed to a dog-themed query) where I want to join two logs together by the Dog's name and end up getting the dog's id:

search "something within logs" | join dog_name_field [search "Dogs Name: "]| table "dog_id"

I want another column in the table, which is a subset of the results of the above search, where I get a true or false on whether or not that id belongs to a Golden Retriever. To get the id's for the golden retrievers, I can do something like:

search "something within logs" | join dog_name_field[search "Dogs Name: " AND "Golden Retriever"]| table "dog_id"

How do I get the ids of all the dogs and have another column saying whether or not that dogs is a golden retriever efficiently.

Re: Combine two columns in a table from two separate searches (where one is a subset of the other)

renjith_nair — Mon, 04 May 2020 04:33:30 GMT

@splunkuser2127 ,

If the dog name is a common field in both searches , try below

Let's say search1 includes index1 and search2 includes index2

index=1 OR index=2 |stats values(dog_id) as dog_id , dc(index) as count by dogs_name
|where count >1

This should give you the common records from both search

Add additional field

    index=1 OR index=2 |stats values(dog_id) as dog_id , dc(index) as count by dogs_name
    |where count >1
    |eval  is_golden_retriever=if(dogs_name=="Golden Retriever","True","False")

Re: Combine two columns in a table from two separate searches (where one is a subset of the other)

splunkuser2127 — Mon, 04 May 2020 14:34:13 GMT

This is helpful, but the dog_name can't be golden retriever, the word "Golden retriever" just might be found in the second search's log