Alerting

Comparing Logs from two different sourcetypes with same session id

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!

 

Labels (2)
0 Karma
1 Solution

isoutamo
SplunkTrust
SplunkTrust

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.

View solution in original post

isoutamo
SplunkTrust
SplunkTrust

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
Path Finder

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.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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
Path Finder

Thank you @isoutamo 

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...