Pass value to another search

[michael_mai]

Hi,

I currently have a simple query that returns a table of data. Let's say...

1) index=test source=test_log groupId="1234" nameSearch="true" | table firstName middleName lastName

firstName middleName lastName
A B C
D E F
G H I
J K L

I would like to add a new column, address, that isn't in the same splunk line as nameSearch but is within the same index. Let's go with...

2) index=test source=test_log addressSearch="true" | table fullName address

fullName address
ABC 1
DEF 2
GHI 3

To get the data for the address column for each row, I would pass information from the first search to the second one. fullName is a combination of firstName, middleName, and lastName (so eval fullName=firstName.middleName.lastName). Would I generate a search for each result of the 1st search? If the first search returned 10000 results, would it be in my best interest to shoot off another 10000 searches for each address data?

What should my approach look like? Is there an easy way to send fullName from the first search to the second and then just add the address column to the end of the table? I'm not sure what command(s) to look into?

I would like the final result to look like this:
firstName middleName lastName address
A B C 1
D E F 2
G H I 3
J K L

Pointers would be great.

I imagined something like:

index=test source=test_log groupId="1234" nameSearch="true" | eval fullName=firstName.middleName.lastName | search [index=test source=test_log fullName="?evalFullName?" addressSearch="true" | table address]| table firstName middleName lastName address

[elliotproebstel · Accepted solution]

Given that I expect there are some oversimplifications at hand in the example (a very fair thing to do in order to get a more generic and broadly applicable answer), here's how I'd approach the problem as described:

Step One: Gather all applicable events

index=test source=test_log (groupId="1234" nameSearch="true") OR addressSearch="true"

Step Two: Build the "fullName" values that enable correlation

| eval fullName=firstName.middleName.lastName

Step Three: Perform correlation using eventstats
This is the least intuitive step, and it asks Splunk to find events where fullName and address appear together and then look for other events containing the same fullName value and copy over the address value.

| eventstats values(address) AS address BY fullName

Step Four: Build result table
You've cross applied the address value, but naively building a table like this:

| table firstName middleName lastName address

is going to result in a sparsely populated table, because it will include lines for the events that contained only fullName and address, which will result in a line showing only a value for address. Not great. So you could filter down to the lines where all and individual name values are not null:

| where isnotnull(firstName) AND isnotnull(middleName) AND isnotnull(lastName)
| table firstName middleName lastName address

And if you knew for sure that every line that contains a firstName will also contain a middleName and lastName, you could do this:

| where isnotnull(firstName)
| table firstName middleName lastName address

Final Solution
You can adjust the end as appropriate, but this is a conservative solution:

index=test source=test_log (groupId="1234" nameSearch="true") OR addressSearch="true"
| eval fullName=firstName.middleName.lastName
| eventstats values(address) AS address BY fullName
| where isnotnull(firstName) AND isnotnull(middleName) AND isnotnull(lastName)
| table firstName middleName lastName address

[elliotproebstel]

Just to address some of the questions you asked in the post - the best way to approach a Splunk problem, in general, is to gather up the narrowest set of events that will be used to solve the problem at once to begin. (Sometimes this will be a broader set than you'll use to finalize your solution, but it's usually better to gather a bit extra in the beginning than to gather some events and then gather some more or spin off a separate search using append or join or, much worse, map.) So if you can find a way to gather a relatively tailored set of events and then correlate them, that will almost always be better than a very narrow search that spins off another search, and almost any approach you can imagine will be better than anything that spins off another 1000 searches, each of which will require a lot of resources (including a dedicated processor core for the duration of the search) to spin up and track.