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!

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 ...