Splunk Search

query returned field passed to another query

atul9771
Engager

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.

Labels (1)
Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

atul9771
Engager

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. 

 

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...