How to edit my search to compare indexed data with lookup data?

[kumargaurav]

There are 2 sister companies say A & B.

Employee of A and B have access of both the company as they are sister company.
Now I am having data of A as index=A and one lookup=X having data of A & B.
Now I want to know those employee of A who accessed into company B system.

The use case is -

Example-
Index =A contains 2 fields: username, facility
Inputlookup X.csv has 3 fields: username, facility, company

Lookup file has these values-

Username = KG Facility =MO Company= A
Username= SS Facility= CAC Company=B

Question is - User who is from A accessing B facility.

In simple: how to check if KG is accessing CAC?

SPL:

Index=A |join username[|inputlookup X.csv |fields username] |stats count BY username, Facility

[niketn]

@kumargaurav if your index A only has Username which you want to correlate with lookup X.csv to get the Company/Facility information, then you would need to create Lookup Definition from Settings > Lookups. Assuming lookup definition is X try the following search:

index=A Username=*
| lookup X Username
| search Company="A" AND Company="B"
| stats count by Username Facility

Please try out and confirm.