Hi all, I'm trying to create a query that gets the number of occurrences of certain Event per month. For that i get the number of elements with the following query: index=kpis|convert timeformat="%Y-%m" ctime(_time) AS year_month|chart count(Event) as events over year_month This will return only the months where there is actual data. For adding the months that are not in there since one year ago i thought that i can use gentimes. To do so I've tried to add : |append [| gentimes start=08/24/2014 increment=28d|convert timeformat="%Y-%m" ctime(starttime) AS year_month|table year_month|dedup year_month]|fillnull resulting in index=kpis|convert timeformat="%Y-%m" ctime(_time) AS year_month|chart count(Event) as events over year_month|append [| gentimes start=08/24/2014 increment=28d|convert timeformat="%Y-%m" ctime(starttime) AS year_month|table year_month|dedup year_month]|fillnull But now i want to add the start dynamically out of a subquery. I thought doing: index=kpis|convert timeformat="%Y-%m" ctime(_time) AS year_month|chart count(Event) as events over year_month|append [| gentimes start=[search index="notexisting"|stats count|eval time=relative_time(now(),"-1y")|convert ctime(time) as time timeformat="\"%m/%d/%Y\"" |return $time] increment=28d|convert timeformat="%Y-%m" ctime(starttime) AS year_month|table year_month|dedup year_month]|fillnull But I receive the following error: [subsearch]: command="gentimes", generatetimestamps requires start=mm/dd/yyyy:hh:mm:ss and optional takes 'end' and 'increment' values. Even using as the error suggest index=kpis|convert timeformat="%Y-%m" ctime(_time) AS year_month|chart count(Event) as events over year_month|append [| gentimes start=[search index="notexisting"|stats count|eval time=relative_time(now(),"-1y")|convert ctime(time) as time timeformat="\"%m/%d/%Y:%H:%M:%S\"" |return $time] increment=28d|convert timeformat="%Y-%m" ctime(starttime) AS year_month|table year_month|dedup year_month]|fillnull that is expected to return "08/21/2014:16:34:18" as the error indicates it still fails with [subsearch]: command="gentimes", generatetimestamps requires start=mm/dd/yyyy:hh:mm:ss and optional takes 'end' and 'increment' values. Even if i remove the quotes: index=kpis|convert timeformat="%Y-%m" ctime(_time) AS year_month|chart count(Event) as events over year_month|append [| gentimes start=[search index="notexisting"|stats count|eval time=relative_time(now(),"-1y")|convert ctime(time) as time timeformat="%m/%d/%Y:%H:%M:%S" |return $time] increment=28d|convert timeformat="%Y-%m" ctime(starttime) AS year_month|table year_month|dedup year_month]|fillnull The error is the same. Is there a way to pass a relative time to gentimes? and append it to the query? ... View more

I have a group of entries that has start_time, end_time , duration and name. Some of them are concurrent some of them not. I would like to create a table that has time buckets of 5 seconds and shows the duration of each element in each time bucket. For instance, for the following values: start_time=0 end_time=10 duration=10 name=test1 start_time=3 end_time=15 duration=12 name=test1 start_time=5 end_time=8 duration=3 name=test2 start_time=9 end_time=11 duration=2 name=test2 will create a table like this: Bucket(time) test1(list(duration in bucket)) test2(list(duration in bucket)) _time=0 5,2 0,0 _time=5 5,5 3,1 _time=10 0,5 0,1 _time=15 0,0 0,0 TOTALS = 10,12 3,2 or even better using the max function by bucket. Bucket(time) test1(max(duration in bucket)) test2(max(duration in bucket)) _time=0 5 0 _time=5 5 3 _time=10 5 1 _time=15 0 0 I believe that the key is somewhere with the following functions: |timechart span=5s list(duration) as dur |where dur >0 or |timechart span=5s list(duration) by name (where column name value >0) But I fail to see how to divide the events in the different buckets since i only have one event and the duration and not multiple events that falls in each bucket. Can somebody help me? ... View more