topic Passing lookup value to search in Splunk Search

Passing lookup value to search

atul9771 — Wed, 28 Feb 2024 21:05:45 GMT

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.

Re: Passing lookup value to search

yuanliu — Wed, 28 Feb 2024 21:01:24 GMT

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.

Re: Passing lookup value to search

atul9771 — Wed, 28 Feb 2024 21:13:39 GMT

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?

Re: Passing lookup value to search

yuanliu — Thu, 29 Feb 2024 01:42:12 GMT

~~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]

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

Sorry about my mistake.

Re: Passing lookup value to search

atul9771 — Thu, 29 Feb 2024 00:26:34 GMT

Brilliant, @yuanliu. Both solutions work.

Thanks again.