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

[ctaf]

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...

[jeffland · Accepted solution]

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

[ctaf]

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

[jeffland]

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

[ctaf]

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

[jeffland]

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

[ctaf]

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

[jeffland]

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

[ctaf]

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

[jeffland]

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.

[ctaf]

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.

[jeffland]

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.

[somesoni2]

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

[ctaf]

Each event has both fields.

[somesoni2]

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

[ctaf]

Both, of course

[chimell]

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]

[ctaf]

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