lookup merge help

[broccolo]

Hello,

My index is called "index=main_ruolo", the fields in it are: user_id, Function, Role
The lookup table with which I need to make a merge is called "merge.csv", the fields are: Function_L, Role_L

In the lookup table "merge.csv", the Function_L - Role_L is the correct one

The purpose is to create a list of all user_id in "index=main_ruolo" where Function and Role do not match Function_L and Role_L

Ex wrong:

Function User Role
foo AFC RoleSTLC
Andrew STLC RoleAFC

Ex correct:

User Function Role Error
Fox AFC RoleSTLC X
Andrew STLC RoleAFC X

The proper Function-Role association is defined within the lookup table

Thank's for help

[broccolo]

Thank you for the reply,
The lookup is existing.
The purpose is to verify if the pair Function, Role in the "main_ruolo" index matches the pair Function_L, Role_L in the "merge.csv" lookup.

The "merge.csv" lookup contains the correct associations.
In case role-function does not match Function_L, Role_L then error = "X"

Given my bad English I translate it into SPL:

| Eval concat = function. "" Role
| Eval concat_L = Function_L. "" .Role_L
| Eval error = if (index="main_ruolo" AND concat! = Concat_L," X "," OK ")

Please help me...
Thx

[broccolo]

Thank you for the reply,
The lookup is existing.
The purpose is to verify the pair, Function - Role in the "main_ruolo" index matches the pair Function_L, Role_L in the "merge.csv" lookup.

The "merge.csv" lookup contains the correct associations.
In case role-function does not match Function_L, Role_L then error = "X"

Given my bad English I translate it into SPL:

| Eval concat = function. "" Role
| Eval concat_L = Function_L. "" .Role_L
| Eval error = if (index="main_ruolo" AND concat! = Concat_L," X "," OK ")

Please help me..

thx

[maciep]

I think the search I gave you should is right or close to right. For each event in the main_ruolo index, it will lookup up the Function field in the merge.csv lookup. If there is a matching Function_L field in the lookup, then the Role_L field will be added to each event.

Then it checks each event. If the Role_L field doesn't exist at all, then that means the Function field didn't match anything in the lookup - which would be a problem (Error = X). OR If the Role_L field does exist but doesn't match the Role field, then the mapping is wrong and so also a problem (Error = X). Otherwise the Function/Role pairing seem correct (Error = "").

Does that make sense? I think that is what you are looking to do.

[maciep]

So the lookup table already exists? And you just want to verify your data against it? Is the Function-to-Role a one-to-one relationship? Maybe something like this?

index=main_ruolo 
| lookup merge.csv Function_L as Function OUTPUT Role_L 
| eval Error = if(NOT match(Role_L,".") OR Role_L!=Role,"X","") 
| table User, Function, Role, Error

So essentially lookup up the Function and spit out the expected role. If no expected role was found for that function or it doesn't match the role in the event, then consider it an error. Kinda hard to know if this is what you're asking or whether it will work with your data, but maybe at least point you in the right direction.