Passing lookup value to search

[atul9771]

I have users.csv as a lookup file with almost 20K users.  I'm writing a query for authentication events for a specific time range for all these users.  CSV file has only one column with the email address of each user and the column header is email.

1) Get the user email from the lookup user.csv file

2) pass user email in the search 

3) Authentication counts per day for specific time range.

I don't have email as a field in the authentication event.

. i can get USER-EMAIL in the authentication event using  formula 

Index="IndexName"| fields "_time", "eventType", "target{}.alternateId", "target{}.type" | 

| search "eventType" = "user.authentication.sso" | rename "target{}.alternateId" AS "targetId" | rename "target{}.type" AS "targetType" | eval "Application"=mvindex(targetId, mvfind(targetType, "AppInstance")) | eval "USER-EMAIL"=mvindex(targetId, mvfind(targetType, "AppUser")

authentication event

{"actor": {"id": "00u1p2k8w5CVuKgeq4h7", "type": "User", "alternateId": "USER-EMAIL", "displayName": "USER-NAME", "detailEntry": null}, "device": null, "authenticationContext": {"authenticationProvider": null, "credentialProvider": null, "credentialType": null, "issuer": null, "interface": null, "authenticationStep": 0}, "displayMessage": "User single sign on to app", "eventType": "user.authentication.sso", "outcome": {"result": "SUCCESS", "reason": null}, "published": "2024-02-20T22:25:18.552Z", "signOnMode": "OpenID Connect",}, "target": [{"id": "XXXXXXX", "type": "AppInstance", "alternateId": "APPLICATION-NAME": "OpenID Connect Client", "detailEntry": {"signOnModeType": "OPENID_CONNECT"}}, {"id": "YYYYYY", "type": "AppUser", "alternateId": "USER-EMAIL, "displayName": "USER-NAME, "detailEntry": null}]}

Index="indexName" "eventType" = "user.authentication.sso" [|inputlookup "users.csv"] 

is not working.

any help is appreciated. 

[yuanliu]

First, thank you for presenting your use case with all necessary information.  As this forum can evidence, I am a strong advocate for not treating structured data as string.  But I will take a very intentional exception in your case because your data volume could be large.  Try

Index="indexName" "eventType" = "user.authentication.sso"
  [inputlookup "users.csv"
  | rename email AS search] 

Here, this is using the email field from the lookup as pure search terms in hope that there is no event commingled with multiple users' emails.

[atul9771]

Thank you, @yuanliu , for your quick response.  Your query returned authentication events for the first user in the users.csv file. 

How can we modify the query to get the authentication events for all the users in the user.csv file?

[yuanliu · Accepted solution]

Not sure why it only gives you one user.  But try these My mistake.  To use the search meta-keyword, format is required.  Try

Index="indexName" "eventType" = "user.authentication.sso"
  [inputlookup "users.csv"
  | rename email AS search
  | format] 

Or

Index="indexName" "eventType" = "user.authentication.sso"
  [inputlookup "users.csv"
  | stats values(email) AS search
  | format]

Sorry about my mistake.

[atul9771]

Brilliant, @yuanliu. Both solutions work.

Thanks again.