How to lookup?

[rvencu]

I have a lookup table of IDs like this:
(id)uuid - (myid)numeric id
(id)uuid - (myid)email
(id)email - (myid)numeric id

So the same visitor that I am identifying by uuid later on can be identified by email or by a numeric id. For me the numeric id is most important and the email is second important. So if there is any identification like
(id)uuid - (myid)numeric id
(id)uuid - (myid)email
I want to end up with the numeric id only.

Every record might have uuid, email or numeric id fields. I then want to calculate a field theID by attaching the numeric ID then email only if numeric ID is not existent. In care there is nothing in the lookup table for the uuid then I can leave theID as uuid.

I came up to this formula but it is not correct:
host="myhost" | where id!="" | lookup sp_aliases id OUTPUT myid | eval theId=if(myid!="",myid,id) but this just leave the first myid encountered into the lookup table

I guess I need to convert the lookup table into a new lookup table better preprocessed. I need some suggestions on how to do that.

[woodcock]

Like this:

host="myhost" | where id!=""
| lookup sp_aliases id OUTPUT myid
| eval numeric=mvfilter(match(myid, "^[^@]+$"))
| eval email=mvfilter(match(myid, "@"))
| eval theId=coalesce(numeric, email, id)