topic What's the best way to only do a Lookup based on the results of the main search? in Splunk Search

What's the best way to only do a Lookup based on the results of the main search?

finchy — Wed, 04 Oct 2023 18:06:31 GMT

Hi,

What's the best way to only do a Lookup based on the results of the main search? I want to only run this when 2 fields don't match. Pseudo would be

If field1!=field2 THEN | lookup accounts department as field2 OUTPUT

So like an if then statement most programming languages allow

Thanks

Lee

Re: What's the best way to only do a Lookup based on the results of the main search?

fredclown — Wed, 04 Oct 2023 18:17:17 GMT

You cannot do a conditional lookup, but you could do the lookup across all the data and then only conditionally display the data that was looked up.

Re: What's the best way to only do a Lookup based on the results of the main search?

finchy — Wed, 04 Oct 2023 19:23:32 GMT

Hi, i wanted to avoid doing a lookup if certain conditions are in place if that's not possible will just have to do it which returns the data if it finds any was trying to just save some cpu and time

Re: What's the best way to only do a Lookup based on the results of the main search?

yuanliu — Wed, 04 Oct 2023 20:17:49 GMT

How to save CPU will depends on the actual flow of your search. For example, if field1!=<fixed string pattern> is exceedingly rare in a large dataset, you can include the lookup in an append subsearch, like

<your main search> | append [search <somesearch> field1 != <fixed string pattern> | lookup accounts department as field2 OUTPUT]

Then manipulate the combined stream to utilize lookup output.

Because field1 != field2 is inapplicable in search command, this technique will not save you index search time. However, if you have a situation where index search is cheap but lookup is exceedingly expensive (it can happen), you can still do it, like

<your main search> | append [search <same main search> | where field1 != field2 | lookup accounts department as field2 OUTPUT]

Alternatively, you are ONLY interested in annotating field2 for which field1 != field2, you can use appendpipe (which is very efficient)

<your main search> | appendpipe [stats count by field1 field2 | where field1 != field2 | lookup accounts department as field2 OUTPUT | fields - count]

In all cases, you will need to massage these annotations back into your final results. Hope this helps.

Re: What's the best way to only do a Lookup based on the results of the main search?

bowesmana — Wed, 04 Oct 2023 21:11:12 GMT

You CAN actually do conditional lookup, as long as your lookup is a CSV

https://docs.splunk.com/Documentation/Splunk/9.1.1/SearchReference/ConditionalFunctions#lookup.28.26lt.3Blookup_table.26gt.3B.2C.26lt.3Bjson_object.26gt.3B.2C.26lt.3Bjson_array.26gt.3B.29

I don't think it's very commonly used, but works well

... search... | eval output=if(field1!=field2, lookup("mylookup.csv", json_object("department", field2), json_array("output_field1","output_field2")), "{}")

You will get back a field output with a JSON representation of the output fields listed in the JSON array

Re: What's the best way to only do a Lookup based on the results of the main search?

yuanliu — Thu, 05 Oct 2023 04:02:55 GMT

Nice ref. Thanks, @bowesmana! (Looks like something since 8.)