topic Re: Search and display results of a single field among two sourcetypes in Splunk Search

Search and display results of a single field among two sourcetypes

aaroncherian — Sat, 18 Jul 2020 20:52:20 GMT

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.

Re: Search and display results of a single field among two sourcetypes

richgalloway — Sat, 18 Jul 2020 21:51:03 GMT

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 ]

Re: Search and display results of a single field among two sourcetypes

aaroncherian — Sat, 18 Jul 2020 22:03:53 GMT

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 ]

Re: Search and display results of a single field among two sourcetypes

to4kawa — Sun, 19 Jul 2020 02:08:49 GMT

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.

Re: Search and display results of a single field among two sourcetypes

aaroncherian — Sun, 19 Jul 2020 04:48:57 GMT

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