topic Re: Comparing Logs from two different sourcetypes with same session id in Alerting

Comparing Logs from two different sourcetypes with same session id

aaroncherian — Sat, 18 Jul 2020 20:22:49 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!

Re: Comparing Logs from two different sourcetypes with same session id

isoutamo — Sat, 18 Jul 2020 21:00:12 GMT

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.

Re: Comparing Logs from two different sourcetypes with same session id

aaroncherian — Sat, 18 Jul 2020 21:29:25 GMT

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.

Re: Comparing Logs from two different sourcetypes with same session id

isoutamo — Sun, 19 Jul 2020 10:37:49 GMT

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.

Re: Comparing Logs from two different sourcetypes with same session id

aaroncherian — Sun, 19 Jul 2020 21:07:18 GMT

Thank you @isoutamo

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