Comparing Logs from two different sourcetypes with same session id

[aaroncherian]

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!

[isoutamo · Accepted solution]

Hi

Something like this:

index=foo host=bar (sourcetype=signons System_Account="*on behalf of*") OR (sourcetype=user_activity)
| eval sys_acc=if (sourcetype=="signons", 1, 0)
| stats values(*) as * by Session_ID
| where mvcount(sys_acc) > 1

 This found events if there are Session_ID on both sourcetypes. If you want also events only in sourcetype=signons, then you must update last where conditions match to sys_acc has value 1.

[aaroncherian]

Hi @isoutamo 

Thanks for the reply. I guess I asked the question wrong. Both sourcetype already has the same session id. I want whatever Session id's that came in the signons search to be searched for in the user_activity sourcetype. Kind of like a subsearch I guess.

Also, what do I replace the * with? Kind of confused on that.

[isoutamo]

Hi

Have you tried this query already?

in Splunk it’s almost always better to avoid sub search. Usually it can avoid with stats (as above). * in above query will replaced by all other fields than Session_ID, as I don’t know other interesting fields which you are needing.  If you are interested only subset of fields then replace values(*) ... part with named fields. 

[aaroncherian]

Thank you @isoutamo 

This worked well! This query gives me exactly what I want.