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

[srik1234]

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

[DalJeanis · Accepted solution]

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]

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]

You are welcome.

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

[DalJeanis]

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.

[srik1234]

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.

[srik1234]

anyone here to answer ?