Can I append lookup results from inputlookup row for filtered results?

[technocratic]

Hi,

I have a CSV file that I would like to filter search results using an inputlookup command, but also to include in the returned events a comment field that is part of that same CSV. Here is an example of my table as stuff.csv:

src	user	comment
192.168.1.1		This matches with the IP only
	john	This matches with the user only
192.168.1.2	bobby	This matches with both IP and user

I would like to do something like this:

index=main [|inputlookup stuff.csv | fields - comment]
| lookup stuff.csv src,user

The main problem here is that the inputlookup subsearch only returns values that have entries, which effectively act as wildcard if the field is empty, while the lookup command treats empty fields as literal blank values. In this example, assuming all events in my index have values for src and user, only matches with the 3rd row would ever return results from the lookup command.

The desired behavior is, for example:

Event contains src=192.168.1.1 and any username - The comment on row 1 is appended

Event contains user=John and any src - The comment on row 2 is appended

Event contains src=192.168.1.2 and user=Bobby - The comment on row 3 is appended

From the snippet above the following behavior is observed:

Example 1 - No comment is appended (Undesired)

Example 2 - No comment is appended (Undesired)

Example 3 - Comment from row 3 is appended as desired

Can I somehow append the comment that associates with the matched row back to the events?

[gcusello]

Hi @technocratic,

if I correctly understood your request, an example of what you're requesting is the solution to search missing hosts.

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup mylookup.csv | eval host=lower(host), count=0 | fields host lookup ]
| stats sum(count) AS total

In this way, you can have all the values, the ones from the search and the ones from the lookup and you can understand if a value (in this case an host) is present or missing, and you can also add other fields from the search or from the lookup, only adding a values option to the stats command.

Ciao.

Giuseppe

[technocratic]

Thank you for the suggestion, but that is not really what I am trying to accomplish. I want to use the CSV to filter a series of events in the main index based on fields present in that CSV, which is why I want to use the inputlookup command. I also would like to return the comment field and append it to the matched event for the associated matched row..

[gcusello]

Hi @technocratic,

to reach the purpose you described, your search is ok, but it doesn't display values=0, so what's your need?

• filter events in the lookup,
• add fields from the lookup,
• list the lookup values that aren't present in the main search,

is it correct?

If this is your need you can use my search:

| metasearch index=_internal [ | inputlookup mylookup.csv | eval host=lower(host) | fields host ]
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup mylookup.csv | eval host=lower(host), count=0 | fields host count comment ]
| stats sum(count) AS total values(comment) AS comment BY host

Ciao.

Giuseppe

[yuanliu]

From your description, the SPL snippet is really doing what it is supposed to do.  Perhaps you can start by describing what is your desired outcome, what the illustrated SPL returns as is, compare the two to demonstrate why that snippet is not meeting your requirement.

[technocratic]

Hi, Sorry if my explanation was not clear.

I would like the use the inputlookup to filter the results, but then include the appropriate comment that corresponds with the row that was matched.

For example:

Event contains src=192.168.1.1 and any username - The comment on row 1 is appended

Event contains user=John and any src - The comment on row 2 is appended

Event contains src=192.168.1.2 and user=Bobby - The comment on row 3 is appended

From the snippet above the following behavior is observed:

Example 1 - No comment is appended

Example 2 - No comment is appended

Example 3 - Comment from row 3 is appended as desired

[yuanliu]

It seems that you want the "blank" values to behave like wildcard without using wildcard as values.  What happens if you change the lookup to use actual wildcard on src and user?  I.e., in CSV file, replace blank values with "*" like this:

src	user	comment
192.168.1.1	*	This matches with the IP only
*	john	This matches with the user only
192.168.1.2	bobby	This matches with both IP and user

Then, in transforms.conf under the appropriate lookup stanza, add

match_type = WILDCARD(src,user)

You can also set up WILDCARD fields in Splunk Web in lookup definitions.

[technocratic]

Thank you for the suggestion. We tested and the issue is that the field needs some value outside of just a wildcard. So for example, it works with "John*" to match all values that start with John, but it will not use "*" by itself to match any value for that field.