topic Timechart of open sessions per username. in Splunk Search https://community.splunk.com/t5/Splunk-Search/Timechart-of-open-sessions-per-username/m-p/54955#M13402 <P>I have a file like this:</P> <PRE><CODE>Time,User-Name,Action Thu Mar 7 15:09:22,admin,login Thu Mar 7 17:46:21,admin,login Thu Mar 7 18:01:33,admin,logout Thu Mar 7 18:17:23,1111,login Thu Mar 7 18:37:02,admin,login Thu Mar 7 19:00:02,admin,logout Thu Mar 7 19:05:21,admin,logout Thu Mar 7 20:51:23,1111,logout Thu Mar 7 21:10:45,admin,logout </CODE></PRE> <P>I want to plot a timechart of open sessions per each user in the log file. Having read <A href="http://splunk-base.splunk.com/answers/5547/">this</A><BR /> and <A href="http://splunk-base.splunk.com/answers/3347/timechart-of-running-sum">this</A> discussions, I wrote this query:</P> <PRE><CODE>source="Accounting01" | eval Diff=if(Action="login", 1, if(Action="logout", -1, 0)) | bin _time | stats sum(Diff) as OpenSessions by _time User_Name | streamstats sum(OpenSessions) as OpenSessions by User_Name | eval Str_Time=strftime(_time, "%d-%m-%Y %H:%M:%S") | chart max(OpenSessions) as "Open sessions" by User_Name, Str_Time </CODE></PRE> <P>This is how it looks now:</P> <P><IMG src="http://i.imgur.com/j2JD4Ow.png" alt="timechart.png" /></P> <P>The problem is that some of the data is not shown, e. g. there is seemingly 0 sessions for <CODE>admin</CODE> between 18:00 and 18:35 while from the data it is obvious that they were logged in the whole time. Same with <CODE>1111</CODE>: they should have 1 session for every point in time until they log out. It is more obviously reflected in the tabular version of the data:</P> <P><IMG src="http://i.imgur.com/0XlK6di.png" alt="tabular.png" /></P> <P>Is it possible to alter the query so that the running sum per each user is stored per each event, even if it is 0 the whole time?</P> <HR /> <P><CODE>inputs.conf</CODE>:</P> <PRE><CODE>[monitor:///home/user/tmp/accounting01.csv] disabled = false sourcetype = Acc01 source = Accounting01 </CODE></PRE> <P><CODE>props.conf</CODE>:</P> <PRE><CODE>[Acc01] REPORT-rep = Acc01_Fields TRANSFORMS-skip = Skip_Header </CODE></PRE> <P><CODE>transforms.conf</CODE>:</P> <PRE><CODE>[Acc01_Fields] DELIMS = "," FIELDS = "Time", "User_Name", "Action" [Skip_Header] REGEX = Time, DEST_KEY = queue FORMAT = nullQueue </CODE></PRE> Thu, 07 Mar 2013 19:59:08 GMT MikhailArefiev 2013-03-07T19:59:08Z Timechart of open sessions per username. https://community.splunk.com/t5/Splunk-Search/Timechart-of-open-sessions-per-username/m-p/54955#M13402 <P>I have a file like this:</P> <PRE><CODE>Time,User-Name,Action Thu Mar 7 15:09:22,admin,login Thu Mar 7 17:46:21,admin,login Thu Mar 7 18:01:33,admin,logout Thu Mar 7 18:17:23,1111,login Thu Mar 7 18:37:02,admin,login Thu Mar 7 19:00:02,admin,logout Thu Mar 7 19:05:21,admin,logout Thu Mar 7 20:51:23,1111,logout Thu Mar 7 21:10:45,admin,logout </CODE></PRE> <P>I want to plot a timechart of open sessions per each user in the log file. Having read <A href="http://splunk-base.splunk.com/answers/5547/">this</A><BR /> and <A href="http://splunk-base.splunk.com/answers/3347/timechart-of-running-sum">this</A> discussions, I wrote this query:</P> <PRE><CODE>source="Accounting01" | eval Diff=if(Action="login", 1, if(Action="logout", -1, 0)) | bin _time | stats sum(Diff) as OpenSessions by _time User_Name | streamstats sum(OpenSessions) as OpenSessions by User_Name | eval Str_Time=strftime(_time, "%d-%m-%Y %H:%M:%S") | chart max(OpenSessions) as "Open sessions" by User_Name, Str_Time </CODE></PRE> <P>This is how it looks now:</P> <P><IMG src="http://i.imgur.com/j2JD4Ow.png" alt="timechart.png" /></P> <P>The problem is that some of the data is not shown, e. g. there is seemingly 0 sessions for <CODE>admin</CODE> between 18:00 and 18:35 while from the data it is obvious that they were logged in the whole time. Same with <CODE>1111</CODE>: they should have 1 session for every point in time until they log out. It is more obviously reflected in the tabular version of the data:</P> <P><IMG src="http://i.imgur.com/0XlK6di.png" alt="tabular.png" /></P> <P>Is it possible to alter the query so that the running sum per each user is stored per each event, even if it is 0 the whole time?</P> <HR /> <P><CODE>inputs.conf</CODE>:</P> <PRE><CODE>[monitor:///home/user/tmp/accounting01.csv] disabled = false sourcetype = Acc01 source = Accounting01 </CODE></PRE> <P><CODE>props.conf</CODE>:</P> <PRE><CODE>[Acc01] REPORT-rep = Acc01_Fields TRANSFORMS-skip = Skip_Header </CODE></PRE> <P><CODE>transforms.conf</CODE>:</P> <PRE><CODE>[Acc01_Fields] DELIMS = "," FIELDS = "Time", "User_Name", "Action" [Skip_Header] REGEX = Time, DEST_KEY = queue FORMAT = nullQueue </CODE></PRE> Thu, 07 Mar 2013 19:59:08 GMT https://community.splunk.com/t5/Splunk-Search/Timechart-of-open-sessions-per-username/m-p/54955#M13402 MikhailArefiev 2013-03-07T19:59:08Z Re: Timechart of open sessions per username. https://community.splunk.com/t5/Splunk-Search/Timechart-of-open-sessions-per-username/m-p/54956#M13403 <P>How about this?</P> <PRE><CODE>... | eval Diff=if(Action="login", 1, if(Action="logout", -1, 0)) | reverse | streamstats sum(Diff) as openSessions by User_Name | timechart max(openSessions) by User_Name | filldown </CODE></PRE> Fri, 08 Mar 2013 09:02:03 GMT https://community.splunk.com/t5/Splunk-Search/Timechart-of-open-sessions-per-username/m-p/54956#M13403 martin_mueller 2013-03-08T09:02:03Z Re: Timechart of open sessions per username. https://community.splunk.com/t5/Splunk-Search/Timechart-of-open-sessions-per-username/m-p/54957#M13404 <P>Thank you! This is exactly what I was looking for.</P> Fri, 08 Mar 2013 10:17:19 GMT https://community.splunk.com/t5/Splunk-Search/Timechart-of-open-sessions-per-username/m-p/54957#M13404 MikhailArefiev 2013-03-08T10:17:19Z