topic Re: Two row details into one row in Splunk Search

Two row details into one row

srajanbabu — Wed, 23 Oct 2013 10:58:25 GMT

I have the below search

index=main sourcetype=summa 
| rex "::\s(?<timestamp>\S+)\s"
| rex "^\S+\s(?<userid>\S+)\." 
| sort userid timestamp 
|transaction login startswith="Logged in" endswith="Processing complete" 
| rex "^\S+\s(?<userid>\S+)\."

Which provide the rsultlinke Table-1, I want fix few things in T-1.
1. The reuslt should display some thing like this now this details are spread in two rows one after the other.
USER-id, Login time, log out time, number of login sessaion

Thanks
Rajan

Table-1

4/3/13 11:46:12.000 AM SNM4 rajan.#### :: 04/03/13 11:46:12 :: User rajan logged in SNM4 rajan.857F :: 04/03/13 11:46:13 :: Processing complete host=hostname Options| sourcetype=summa Options| source=C:\Data\splunk\fxr\snm4-logger.log Options 4/3/13 11:46:08.000 AM SNM4 verify.#### :: 04/03/13 11:46:08 :: User verify logged in SNM4 different.855F :: 04/03/13 11:46:12 :: Processing complete 4/3/13 11:45:58.000 AM SNM4 suman.#### :: 04/03/13 11:45:58 :: User suman logged in SNM4 suman.853F :: 04/03/13 11:45:59 :: Processing complete host=hostname Options| sourcetype=summa Options| source=C:\Data\splunk\fxr\snm4-logger.log Options

Re: Two row details into one row

lguinn2 — Wed, 23 Oct 2013 16:03:13 GMT

Try this

index=main sourcetype=summa 
| rex "^\S+\s(?<userid>\S+)\." 
| sort userid _time 
| transaction login startswith="Logged in" endswith="Processing complete" 
| eval logoutTime = strftime( _time + duration,"%x %X")
| eval loginTime =strftime( _time, "%x %X")
| table userId loginTime logoutTime

I don't know how you want to show both the individual sessions and the overall count. To calculate the count, you could simply do

index=main sourcetype=summa 
| rex "^\S+\s(?<userid>\S+)\." 
| sort userid _time 
| transaction login startswith="Logged in" endswith="Processing complete" 
| stats count by userid

Re: Two row details into one row

kristian_kolb — Wed, 23 Oct 2013 17:11:13 GMT

Is login an extracted field? You can't make a transaction on a field that does not exist. Why is userid extracted twice? Also, there is no point in sorting before the transaction.

I believe that you want to do something like;

index=main sourcetype=summa 
| rex "^\S+\s(?<userid>\S+)\." 
| transaction userid startswith="Logged in" endswith="Processing complete"
| eval logoutTime = strftime( _time + duration,"%F %T")
| eval loginTime =strftime( _time, "%F %T")
| stats list(loginTime) as Login list(logoutTime) as Logout count as "Number of Sessions" by userid

alternatively, the last line can be substituted with;

| table userid, loginTime, logoutTime, duration

It's a bit unclear if you want to do it the first or the second way.

Re: Two row details into one row

srajanbabu — Thu, 24 Oct 2013 07:01:39 GMT

Kristian,
Your rex gave me what exactly I wanted thanks a lot. Just one more query on the same item, I want to list out incomplete transation (i.e.) user who are currently loged in and not loged out. Will you able to help me.

Thanks
Rajan

Re: Two row details into one row

dishasaxena — Fri, 25 Oct 2013 05:37:35 GMT

Hi Rajan,

For this requirement, do you have any 'suppression key' in these events which can identify a single session(it needs to be common in both 'Logged In' and Processing complete' event)?

Regards,
Disha

Re: Two row details into one row

srajanbabu — Fri, 25 Oct 2013 08:32:34 GMT

Kristian,
There is no suppression key associated with this even. Every "Logged in" should have "Processing complete" for that user-id. At present if there is no "Processing complete" after logged in" after the time of this start even then, will asume the user is yet to log out.

Re: Two row details into one row

srajanbabu — Fri, 25 Oct 2013 08:49:26 GMT

Thanks for taking time and answering this, the other answer worked for me.