Splunk Search

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

mgbersales
Loves-to-Learn

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?

Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...