Splunk Enterprise Security

How to get multiple events into single row or table format with timestamp of login logout of session

srik1234
Explorer

Hi,
I'm new to splunk. I learned many things from Splunk Answers section.
Firstly i would like to thank you all who have given answers and Splunk support team.

I have requirement to fetch user login and logout with timestamp and couple of fields more.
Currently one of my network component generates multiple events for single session and send it to splunk.
Session ID remains same for all events but required fields displays in a separate event or row with same session id.

example i'm looking table format like this:

hostname  session_id username clientip   country   session_start              session_end 
device_A  af1202010  userX     1.1.1.x     US      01-01-2020 11:15:00 AM     02-01-2020 03:30:00 AM
device_B  zqfs04011  userY     2.2.2.y     UK      01-01-2020 12:15:00 PM             

events appears like this

01-01-2020 01:15:00 GMT session_id af1202010    "User_Agent:IE"       
01-01-2020 01:15:01 GMT session_id  af1202010    Country US clientip 1.1.1.x destination ip 9.0.0.1
01-01-2020 01:15:02 GMT session_id  af1202010  username userX
01-01-2020 01:15:03 GMT session_id  af1202010  resource assigned computer_A
01-01-2020 01:15:04 GMT session_id  af1202010  Allowed
02-01-2020 03:30:00 GMT session_id  af1202010  Bytes_out

Based on above splunk logs, I need a query to fetch output in a table format last 24 hrs report or 1 week report into csv format.

example:

index=xyz hostname=device_* session_id="*" 
|  eval session_start=if(searchmatch("User_Agent"),_time,null()) 
| eval session_end=if(searchmatch("Bytes_Out"),_time,null()) 
| transaction session_id 
| rex field=_raw "\d\d,\d\d\d \d\d/\d\d (?\S*)" 
| search session_id username country resource 
| convert ctime(session_start) ctime(session_end) 
| table hostname, session_id, username,  country, clientip, session_start, session_end

Note:
Some of the users who already logged in several days before so log out of the user could today or not all.

Your help much appreciated, also if you provide to correct search string to fetch report into csv format.

Thank you

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

transaction is a resource hog, and not needed here since the host and sessionid are on all the records.

index=xyz hostname=device_* session_id=* 
| rename COMMENT as "limit to needed fields"
| fields hostname, session_id, username, country, clientip 

| rename COMMENT as "set the start and end times"
| eval session_start=if(searchmatch("User_Agent"),_time,null()) 
| eval session_end=if(searchmatch("Bytes_Out"),_time,null()) 

| rename COMMENT as "roll the records together"
| stats values(*) as * by hostname session_id

| rename COMMENT as "format the time fields"
| convert ctime(session_start) ctime(session_end) 

| rename COMMENT as "present the results"
| table hostname, session_id, username, country, clientip, session_start, session_end

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

transaction is a resource hog, and not needed here since the host and sessionid are on all the records.

index=xyz hostname=device_* session_id=* 
| rename COMMENT as "limit to needed fields"
| fields hostname, session_id, username, country, clientip 

| rename COMMENT as "set the start and end times"
| eval session_start=if(searchmatch("User_Agent"),_time,null()) 
| eval session_end=if(searchmatch("Bytes_Out"),_time,null()) 

| rename COMMENT as "roll the records together"
| stats values(*) as * by hostname session_id

| rename COMMENT as "format the time fields"
| convert ctime(session_start) ctime(session_end) 

| rename COMMENT as "present the results"
| table hostname, session_id, username, country, clientip, session_start, session_end

srik1234
Explorer

Hi Dal Jeanis,

Thanks alot it worked as expected.
But I get sessionid 00000000 for some of the session. Not sure what does that event means. Any idea?
However ignored it in my query at this moment.

Thanks again! It worked.

DalJeanis
SplunkTrust
SplunkTrust

You are welcome.

I would check the individual events and find out if any of them have zeroes for that sessionid.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Formatted your code and tables for easier review

you can highlight your code and use the code button (101 010), or you can precede your code by four or more spaces on each line, or you can precede and follow it by three grave accent marks (the accent to the left of the 1 on a US keyboard). Any of those will cause the code to be formatted as code, so the interface doesnt' treat your code as html.

There may have been something lost in your rex statement.

0 Karma

srik1234
Explorer

Based on username can i get related sessions and country code. While digging more, currently missing some of the users like country and logout session entries. Rest all works fine as expected.

0 Karma

srik1234
Explorer

anyone here to answer ?

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...