Splunk Search

Search and display results of a single field among two sourcetypes

aaroncherian
Path Finder

Hello, 

I have a sourcetype called "signons" and it has a field called "Session_ID" and "System_Account"

In my search, I am looking for any proxy sessions and want to display those proxy sessions with the same "Session_ID" in the sourcetype called "user_activity".

To check if a session is a proxy session, the "System_Account" field has the words "on behalf of".

Here is my search so far: 

 

 

index="foo" host="bar" sourcetype="signons" System_Account="*on behalf of*"

 

 

One example of an event that returns:

 

 

 "System_Account": "12345 / Aaron Cherian on behalf of 67890 / John Doe",
 "Authentication_Type": "Proxy Started",
 "Session_ID": "4743ha",
 "Is_Admin": "1",
 "Elapsed_Time_Minutes": "1029"

 

 

I want to take this Session_ID (There are multiple different Session_ID's because there are many proxy sessions that are being run during the day) and search for the events in a different sourcetype called "user_activity" (This basically checks the user activity for that specific Session_ID. 

Here is my search for that:

 

 

index="foo" host="bar" sourcetype="user_activity" 4743ha

 

 

This is just displaying the events for that specific Session_ID. Is there a way to search for all Session_ID's that have the words "on behalf of" in the "System_Account" field in the "user_activity" sourcetype and display the events? Basically I want to combine these two searches for all proxy Session_ID's

Thanks!

EDIT: I have posted the same post accidentally under a different category. I am unsure to how to delete it. I apologize for the double post.

 

Labels (5)
0 Karma
1 Solution

to4kawa
Ultra Champion

 

index="foo" host="bar" sourcetype="user_activity" [search index="foo" host="bar" sourcetype="signons" System_Account="*on behalf of*"
| rex "Session_ID\": \"(?<session_id>\w+)\""
| fields session_id
| format ]

 

well, your log is JSON? your query has unescaped quotes.

please provide sample logs. we can make the appropriate query.

 

 

index="foo" host="bar" sourcetype="user_activity" OR  sourcetype="signons" 
| rex "Session_ID\": \"(?<session_id>\w+)\""
| stats values(System_Account) as System_Account values(Authentication_Type) as Authentication_Type values(Is_Admin) as Is_Admin values(Elapsed_Time_Minutes) as Elapsed_Time_Minutes count(eval(like(System_Account,"%on behalf of%"))) as SA_count by session_id
| where SA_count > 0
| table System_Account Authentication_Type session_id Is_Admin Elapsed_Time_Minutes

 

this works ,I guess.

 

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

You can combine the two searches using the subsearch approach.  Subsearches execute first, so use the subsearch to find the session_id then they'll be passed to the other search to be located in user_activity.  This assumes the user_activity sourcetype has a field called session_id.  If the field has a different name then the subsearch will need modification (change 'session_id' to something else).

index="foo" host="bar" sourcetype="user_activity" [ index="foo" host="bar" sourcetype="signons" System_Account="*on behalf of*"
| rex "Session_ID\": "(?<session_id>\w+)"
| fields session_id
| format ]

 

---
If this reply helps you, Karma would be appreciated.

aaroncherian
Path Finder

Hi @richgalloway 

Thanks for the reply, when trying this, it gives me an error which says "Unbalanced Quotes" I am unsure why because the quotes seem correct.

Here is the code: 

EDIT: Added the keyword "search" after the "["

index="foo" host="bar" sourcetype="user_activity" [search index="foo" host="bar" sourcetype="signons" System_Account="*on behalf of*"
| rex "Session_ID\": "(?<session_id>\w+)"
| fields session_id
| format ]
0 Karma

to4kawa
Ultra Champion

 

index="foo" host="bar" sourcetype="user_activity" [search index="foo" host="bar" sourcetype="signons" System_Account="*on behalf of*"
| rex "Session_ID\": \"(?<session_id>\w+)\""
| fields session_id
| format ]

 

well, your log is JSON? your query has unescaped quotes.

please provide sample logs. we can make the appropriate query.

 

 

index="foo" host="bar" sourcetype="user_activity" OR  sourcetype="signons" 
| rex "Session_ID\": \"(?<session_id>\w+)\""
| stats values(System_Account) as System_Account values(Authentication_Type) as Authentication_Type values(Is_Admin) as Is_Admin values(Elapsed_Time_Minutes) as Elapsed_Time_Minutes count(eval(like(System_Account,"%on behalf of%"))) as SA_count by session_id
| where SA_count > 0
| table System_Account Authentication_Type session_id Is_Admin Elapsed_Time_Minutes

 

this works ,I guess.

 

aaroncherian
Path Finder

Worked like a charm! Exactly what I needed. Thank you sir.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...