Splunk Search

Correlate two events with one common field value

mvagionakis
Path Finder

Hello,

I have some logs with a common field and I'd like to correlate them.

here my first event:

26/02/2020 16:34:21|toto|test|600|440|Session End|device=titi|sessionId=3bee772f147|ext=External

Here my second one:

26/02/2020 16:34:21|toto|test|600|440|Upload|sessionId=3bee772f147|ext=External|username=mvag

So, my question is how can I extract the username when the sessionid is the same for the two events by searching the "Session End" information (first event)?

The main idea is to search the "Session End" information and find the username where the sessionId is the same.

Thank you in advance.
Michael

p.s: It would be better if the device manufacturer had added the username in each event but it would be so easy 🙂

0 Karma

masonmorales
Influencer

What are you interested in doing with the data once you have the two events correlated with both the username and sessionid together?

0 Karma

mvagionakis
Path Finder

Hello masonmorales,

The idea is to find when the "session" is ended by the user.
The problem is that when the information "Session End" appears in the logs, the username is not given by the equipement. Instead, we have the UNIQUE ID "sessionId" attributed to the user, so my idea to correlate this in order to get the user who had ended his session.

I hope I was clear .

Thank you
Michael

0 Karma

darrenfuller
Contributor

Hi @mvagionakis,

Here is my thought on a solution:

| makeresults
| eval testdata="^26/02/2020 16:34:21|toto|test|600|440|Session End|device=titi|sessionId=3bee772f147|ext=External^26/02/2020 16:34:21|toto|test|600|440|Upload|sessionId=3bee772f147|ext=External|username=mvag^"
| rex field=testdata max_match=0 "\^(?<events>[^\^]+)"
| table testdata events
| fields - testdata
| mvexpand events
| rename events AS _raw
| rex field=_raw "^(?<timestamp>[^\|]+)\|"
| eval _time=strptime(timestamp, "%d/%m/%Y %H:%M:%S")
| rex field=_raw "device=(?<device>\w+)\|"
| rex field=_raw "sessionId=(?<sessionId>\w+)\|"
| rex field=_raw "ext=(?<ext>\w+)"
| rex field=_raw "([^\|]+\|){5}(?<action>[^\|]+)\|"
| rex field=_raw "username=(?<username>\w+)"
| eval starttime=if(action="Upload", _time, null)
| eval endtime=if(action="Session End", _time, null)
| stats values(username) min(starttime) AS starttime max(endtime) AS endtime by sessionId
| eval sessionlength=endtime-starttime

Explaination:

| makeresults
| eval testdata="^26/02/2020 16:34:21|toto|test|600|440|Session End|device=titi|sessionId=3bee772f147|ext=External^26/02/2020 16:34:21|toto|test|600|440|Upload|sessionId=3bee772f147|ext=External|username=mvag^"
| rex field=testdata max_match=0 "\^(?<events>[^\^]+)"
| table testdata events
| fields - testdata
| mvexpand events
| rename events AS _raw
| rex field=_raw "^(?<timestamp>[^\|]+)\|"
| eval _time=strptime(timestamp, "%d/%m/%Y %H:%M:%S")
| rex field=_raw "device=(?<device>\w+)\|"
| rex field=_raw "sessionId=(?<sessionId>\w+)\|"
| rex field=_raw "ext=(?<ext>\w+)"
| rex field=_raw "([^\|]+\|){5}(?<action>[^\|]+)\|"
| rex field=_raw "username=(?<username>\w+)"

Everything up to this point is to create my run anywhere test data for your test case. In all likelihood, depending on how your sourcetype was created, you will not need to extract some of the fields (any with fieldname=fieldvalue should be extracted by themselves... I created the field "action" to get the "Session End" / "Upload" values

 | eval starttime=if(action="Upload", _time, null)
 | eval endtime=if(action="Session End", _time, null)

This bit creates a field called starttime, for all the events where action=Upload (if you have more than one possible action that can signify the start of the transaction, this may need to be more detailed, either with a nested if, or a case)

| stats values(username) min(starttime) AS starttime max(endtime) AS endtime by sessionId

Here we group each sessionId by its starttime endtime and username combinations

| eval sessionlength=endtime-starttime

Lastly, i am using the difference between the start and end to set a duration.

Let me know if this helps. .

./Darren

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