How to check if Data in a Lookup Table does not exist in another Index

[mgbersales]

I am creating a query to check if a list of accounts owned by our team exists and with correct privilege type in another index which contain a listing of all DB servers and account logins and privilege types.

I have a lookup table masterlist of all accounts owned by our team and their corresponding Privilege_Types:
Account_Name Privilege_Type
account1 sa_role
account1 sso_role
account2 sa_role
account2 mon_role
account3 sa_role

My index contains the following data:
Server_Name Account_Name Privilege_Type
server1 account1 sa_role
server1 account 1 sso_role
server1 account2 sa_role
server2 account1 sa_role
server2 account2 sso_role
server2 account2 mon_role
server2 account3 mon_role

My desired output should show if an account exists and the privilege types are correct
Server_Name Account_Name AccountExists CorrectPrivilegeType
server1 account1 Y Y
server1 account2 Y N
server1 account3 N N
server2 account1 Y N
server2 account2 Y Y
server3 account3 Y N

How do i achieve this result?

[gcusello]

Hi @mgbersales,
see something like this:

index=my_index
| lookup my_lookup Account_Name OUTPUT Privilege_Type AS New_Privilege_Type
| eval AccountExists=if(isnull(New_Privilege_Type),"N","Y"), CorrectPrivilegeType=if(Privilege_Type=New_Privilege_Type,"Y","N")
| stats values(AccountExists) AS AccountExists values(CorrectPrivilegeType) AS CorrectPrivilegeType BY Server_Name Account_Name 

Ciao.
Giuseppe

[MuS]

Hi mgbersales,

Please read this great post of @dwaddle https://www.duanewaddle.com/proving-a-negative/ that covers exactly this topic.

Hope this helps ...

cheers, MuS