query returned field passed to another query

[atul9771]

I need help to write a search query where the result from the one query is passed onto the second query

1 we import the users from the active directory group in the okta group and the event eventType="group.user_membership.add" captures this Json event

Following the query get me the name of the group and user name.

index="indexName"   eventType="group.user_membership.add" | spath "target{}.displayName" |rename target{}.displayName as grpID| eval groupName=mvindex(grpID, 1) |  rename "target{}.alternateId" AS "targetId" | rename "target{}.type" AS "targetType"| eval target_user=mvindex(targetId, mvfind(targetType, "User")) | table target_user groupName

2. After the user is added to the Okta group, I want to find the occurrence of the user authentications during time range  . I can separately find user authentication using eventType="user.authentication.sso" this event doesn't have a group name.

index="indexName"   eventType="user.authentication.sso"  target_user  | stats count by date

How do I pass the user in the first query to the second query. I cannot use subsearch since the main search eventype is not the same as the second sub search.

Basically, I want to create a report by groupname/username authentications for the selected time range

Any help is appreciated.

[ITWhisperer]

I am not sure why different eventtypes can't be combined into the same search - assuming they can, try something like this

index="indexName"   eventType="group.user_membership.add" OR eventType="user.authentication.sso"
| spath "target{}.displayName" 
|rename target{}.displayName as grpID
| eval groupName=mvindex(grpID, 1) 
|  rename "target{}.alternateId" AS "targetId" 
| rename "target{}.type" AS "targetType"
``` Assuming target_useris already extracted for sso events (otherwise extract it here) ```
| eval target_user=if(eventType=="user.authentication.sso",target_user,mvindex(targetId, mvfind(targetType, "User")))
| table target_user groupName date
| eventstats values(groupName) as groupName by target_user
| where eventType="user.authentication.sso"
| stats count by date

[atul9771]

Thanks for your reply. but if the statement is not returning only a specific event user name.  here are two events.

group.user_membership.add Event
{"actor": {"id": "spr1g8od2gOPLTfra4h7", "type": "SystemPrincipal", "alternateId": "system@okta.com", "displayName": "Okta System", "detailEntry": null}, "client": {"userAgent": null, "zone": null, "device": null, "id": null, "ipAddress": null, "geographicalContext": null}, "device": null, "authenticationContext": {"authenticationProvider": null, "credentialProvider": null, "credentialType": null, "issuer": null, "interface": null, "authenticationStep": 0, "externalSessionId": "trs-tF3wuwOTRiKM_BZirBk9A"}, "displayMessage": "Add user to group membership", "eventType": "group.user_membership.add", "outcome": {"result": "SUCCESS", "reason": null}, "published": "2024-02-20T15:40:04.384Z", "securityContext": {"asNumber": null, "asOrg": null, "isp": null, "domain": null, "isProxy": null}, "severity": "INFO", "debugContext": {"debugData": {"triggeredByGroupRuleId": "0pr7fprux4jw2hORP4h7"}}, "legacyEventType": "core.user_group_member.user_add", "transaction": {"type": "JOB", "id": "cpb7g4ndq8ZaAR5S14h7", "detail": {}}, "uuid": "5115faa0-d006-11ee-84e8-0b1ac5c0434f", "version": "0", "request": {"ipChain": []}, "target": [{"id": "00u7g4ndmhZ2j2J1i4h7", "type": "User", "alternateId": "USER-EMAIL", "displayName": "USER-NAME", "detailEntry": null}, {"id": "00g7fpoiohiAF2JrY4h7", "type": "UserGroup", "alternateId": "unknown", "displayName": "GROUP-NAME", "detailEntry": null}]}

user.authentication.sso 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": "0oa2n26twxcr3lNWO4h7", "type": "AppInstance", "alternateId": "APPLICATION-NAME": "OpenID Connect Client", "detailEntry": {"signOnModeType": "OPENID_CONNECT"}}, {"id": "0ua2n4im21IccI2Eh4h7", "type": "AppUser", "alternateId": "USER-EMAIL, "displayName": "USER-NAME, "detailEntry": null}]}

And my query

Index= "IndexName"(eventType="group.user_membership.add" OR eventType="user.authentication.sso") | rename "target{}.alternateId" AS "targetId" |rename "target{}.type" AS "targetType" | eval User=if(eventType="group.user_membership.add",mvindex(targetId, mvfind(targetType, "User")),"SSO User") |spath "target{}.displayName" |rename target{}.displayName as grpID| eval groupName=mvindex(grpID, 1) | table User groupName | where eventType="user.authentication.sso"

What I'm looking is

grab the user name and group name for  the  eventType="group.user_membership.add only , this event type will tell me when the user is added to the particular group

then search the User name in the eventType="user.authentication.sso and display the result as group name and user name.

Basically I want to get the list of users by group name started using authentication service.

Thanks again for your time.