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
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
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
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
Although this worked with a small sample of data, it looks like it doesn't fit my needs.
Sorry to hear that. Would you be able to tell what all things it's lagging in meeting your requirements?
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
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 *
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
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