topic Re: Can I perform a lookup on 1 lookup field AS 2 existing fields? in Splunk Search

Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Fri, 19 Feb 2016 10:09:14 GMT

Hello,

I have two existing fields: mailto, mailfrom.
I also have a lookup with 2 fields: Mail and Country

I would like to perform a lookup like this:

| lookup mail_country Mail AS mailfrom , Mail AS mailto  OUTPUT Country

But this doesn't work... I need to perform 2 lookups:

| lookup mail_country Mail AS mailfrom OUTPUT Country  | lookup mail_country Mail AS mailto  OUTPUTNEW Country

But I don't think it is efficient to perform 2 lookups like this...

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

chimell — Fri, 19 Feb 2016 10:35:42 GMT

Hi
let try this

 |set union[search .................| lookup mail_country Mail AS mailfrom OUTPUT Country |fields Country][search ...............| lookup mail_country Mail AS mailto  OUTPUTNEW Country |fields Country]

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

jeffland — Fri, 19 Feb 2016 12:34:08 GMT

I know it's not pretty, but you could do

... | eval mail_copy=Mail | lookup mail_country Mail AS mailfrom , mail_copy AS mailto  OUTPUT Country

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Fri, 19 Feb 2016 14:03:54 GMT

It doesn't seem more efficient, as there is still two lookups

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Fri, 19 Feb 2016 14:04:09 GMT

But the variable "Mail" doesn't exist before the lookup ..?

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

jeffland — Mon, 22 Feb 2016 11:09:17 GMT

Ah, sorry - I misunderstood your question.
You could do the same you are doing with the two lookups like this:

... | eval coalesced_mail=coalesce(mailfrom, mailto) | lookup mail_country Mail AS coalesced_mail OUTPUT Country

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Mon, 22 Feb 2016 13:56:39 GMT

Since there is always a value for "mailfrom", coalesced_mail will always take this value and never the "mailto" value. No?

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

somesoni2 — Mon, 22 Feb 2016 16:30:14 GMT

Does your (every) event has both the fields (mailfrom, mailto) OR they are available in separate events?

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Mon, 22 Feb 2016 16:45:09 GMT

Each event has both fields.

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

somesoni2 — Mon, 22 Feb 2016 16:47:27 GMT

So, which Country you want to retrieve, for mailfrom , for mailto OR both?

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

jeffland — Tue, 23 Feb 2016 07:41:30 GMT

Well then your initial search with two lookups will never lookup any events from the second lookup either.

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Tue, 23 Feb 2016 15:22:57 GMT

Both, of course

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Tue, 23 Feb 2016 15:24:12 GMT

Why not? I tried it, it works. (but it is not very efficient I think. Hence my question.)

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

jeffland — Wed, 24 Feb 2016 07:41:20 GMT

No, it can't work. lookup OUTPUTNEW will not overwrite a field if it's already there, so either the first lookup did not return anything (and Country is still empty) or the second lookup has no effect. This is exactly the behavior coalesce features, so it should be identical.

For reference:

If the OUTPUTNEW clause is specified, the lookup is not performed for events in which the output fields already exist

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Wed, 24 Feb 2016 08:35:30 GMT

Oh OK... So any idea to make it work ?

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

jeffland — Tue, 29 Sep 2020 08:55:16 GMT

There is more than one way to "make this work". It depends on what you want to achieve, see somesoni2's question above.
Consider what happens: you have two fields, mailto and mailfrom. They may be different, they may be the same. You could run a lookup on each of them, but then the output of those lookups may be different or may be the same, depending on the values of mailto and mailfrom. You could place the result of the lookup in two different fields, e.g. mailto_country and mailfrom_country, or you could only care for one of those - it depends on what you want to do. You can't, however, "make this work" by having only one lookup and one field. If you want one field containing the info "From Country - To Country", you could concatenate the two individual fields after looking them up.

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

ctaf — Tue, 29 Sep 2020 08:55:19 GMT

OK Thank you.
In the end I decided to create two fields (mailto_country and mailfrom_country) with two lookups and to use mvappend(mailto_country,mailfrom_country) to create a mv field with all the countries.

Re: Can I perform a lookup on 1 lookup field AS 2 existing fields?

jeffland — Wed, 24 Feb 2016 10:38:14 GMT

I'm glad you found a solution that works for you. Two lookups in a search should not be too much of a performance hit, especially since assuming a lookup on countries only has a few hundred lines in it anyway.