How to plot every minute between a start and end time for multiple events on a timechart?

[srobinsonxtl]

All, I hope someone can help me.

I am trying to plot every minute of an event between a start and end time to get a number of connections at a set time. For example. I have an event that starts at 1 and ends at 3 and a second event that start at 1:30 and ends at 3:30 and lastly, an event that starts at 2:45 and ends at 2:50. Basically as you can see there will be 3 connections between 2:45 and 2:49 and 2 connections at 3:00.

1--------------------------------------3
             2:30-----------------------------------------------------3:30
                           2:45--------2:50

I would like to put this on a time chart.
Thanks,

Stephen Robinson

[somesoni2 · Accepted solution]

You can try something like this (run anywhere sample, adjust the query per yours)

| gentimes start=-1 | eval temp="2016/01/13 07:00,2016/01/13 10:00#2016/01/13 08:00,2016/01/13 10:30#2016/01/13 09:45,2016/01/13 10:00" | table temp | makemv temp delim="#" | mvexpand temp | rex field=temp "(?<Start>.*),(?<End>.*)" | table Start End | eval temp=Start.",1"."#".End.",-1" |  table temp | makemv temp delim="#" | mvexpand temp | rex field=temp "(?<Time>.*),(?<count>.*)" | eval _time=strptime(Time,"%Y/%m/%d %H:%M") | sort _time | timechart span=1m sum(count) as count | fillnull value=0 | accum count

Updated Answer

index=foo sourcetype=foo CallState!=SERVER LeaveTime!=NULL | lookup hosts id as ServerID OUTPUT name as host | eval startTime=_time | eval leaveTime=LeaveTime | eval startTime_epoch = startTime | eval duration = (leaveTime-startTime) | convert ctime(startTime) AS Start | convert ctime(leaveTime) AS Stop | table Start,Stop | eval temp=Start.",1"."#".Stop.",-1" |  table temp | makemv temp delim="#" | mvexpand temp | rex field=temp "(?<Time>.*),(?<count>.*)" | eval _time=strptime(Time,"%m/%d/%Y %H:%M %p") | sort _time | timechart span=1m sum(count) as count | fillnull value=0 | accum count

[srobinsonxtl]

Thank you for sending this information, the only problem I see is that my data has to come through as a search. The gentimes has to be at the first part of the line, so I tried to do

| gentimes start=-1 | eval temp=[search  | return $temp] | table temp | makemv temp delim="#" | mvexpand temp | rex field=temp "(?.*),(?.*)" | table Start End | eval temp=Start.",1"."#".End.",-1" |  table temp | makemv temp delim="#" | mvexpand temp | rex field=temp "(?.*),(?.*)" | eval _time=strptime(Time,"%Y/%m/%d %H:%M") | sort _time | timechart span=1m sum(count) as count | fillnull value=0 | accum count 

but that didn't work. Do you know how to do this?

Thanks,

Stephen Robinson

[srobinsonxtl]

Although this worked with a small sample of data, it looks like it doesn't fit my needs.

[somesoni2]

Sorry to hear that. Would you be able to tell what all things it's lagging in meeting your requirements?

[srobinsonxtl]

I really appreciate you stepping in to help me solve my issue. I believe you have gotten me close, but it seem like when data is large only get a limit amount of data. So let me start at the beginning to get better clarity.

Here is my starting data which I generate using a search.

ServerName,StartTime,EndTime,Duration,
server02,1/11/2016 8:52,1/11/2016 8:52,0
server02,1/11/2016 8:52,1/11/2016 8:52,0
server01,1/11/2016 10:53,1/11/2016 10:56,3
server02,1/11/2016 11:40,1/11/2016 11:47,7
server01,1/12/2016 8:58,1/12/2016 9:38,40
server01,1/12/2016 9:50,1/12/2016 9:50,0
server01,1/12/2016 14:00,1/12/2016 14:00,0
server01,1/12/2016 14:41,1/12/2016 14:42,1
server01,1/12/2016 15:52,1/12/2016 16:00,8
server01,1/12/2016 15:57,1/12/2016 15:57,0
server01,1/13/2016 8:47,1/13/2016 8:47,0
server01,1/13/2016 10:50,1/13/2016 10:58,8
server02,1/13/2016 12:24,1/13/2016 12:28,4
server01,1/13/2016 13:59,1/13/2016 14:00,1
server01,1/14/2016 9:39,1/14/2016 9:44,5
server01,1/14/2016 12:00,1/14/2016 12:02,2

Now I am trying to plot the concurrent connections by ServerName. This is a small sample size, but should help to determine the best course of action. The key is, that the data needs to come in as a search first and then all the magic should happen. I tried using the map command in conjunction with gentimes, but can't seem to get it to work.

Thanks,

Stephen Robinson

[somesoni2]

Try something like this. Now I included different column for each server name

| inputlookup ServeLogins.csv  *****Replace this with your current search which gives ServerName,StartTime,EndTime,Duration****
| eval temp=StartTime.",1"."#".EndTime.",-1" |  table ServerName temp | makemv temp delim="#" | mvexpand temp | rex field=temp "(?<Time>.*),(?<count>.*)" | eval _time=strptime(Time,"%m/%d/%Y %H:%M") | eval _time=if(count=-1,_time+60,_time )| timechart span=1m sum(count) as count by ServerName | fillnull value=0 | streamstats sum(*)  as *

[somesoni2]

Ok.. I should've been more clear. You've to replace following with your search.

| gentimes start=-1 | eval temp="2016/01/13 07:00,2016/01/13 10:00#2016/01/13 08:00,2016/01/13 10:30#2016/01/13 09:45,2016/01/13 10:00" | table temp | makemv temp delim="#" | mvexpand temp | rex field=temp "(?<Start>.*),(?<End>.*)" | table Start End

Also, I've considering Start and End as the field that gives start time and end time in the format %Y/%m/%d %H:%M, update the field name and date format as per your data.

Or post your query and field names

[srobinsonxtl]

Here is my search that gives me the start and stop times for my events.

index=foo sourcetype=foo CallState!=SERVER LeaveTime!=NULL | lookup hosts id as ServerID OUTPUT name as host | eval startTime=_time | eval leaveTime=LeaveTime | eval startTime_epoch = startTime | eval duration = (leaveTime-startTime) | convert ctime(startTime) AS Start | convert ctime(leaveTime) AS Stop | table Start,Stop

Start,Stop
1/3/2016 10:00 AM,1/3/2016 10:10 AM

Now I need to pass this to the gentimes start=startTime .......

Thanks for all the help so far I really appreciate it.

Thanks,

Stephen Robinson