topic Re: how can i get details with both span 10m and 30 m with dedup _time. in Splunk Search

how can i get details with both span 10m and 30 m with dedup _time.

harishalipaka — Wed, 12 Jul 2017 11:14:08 GMT

I have one Search Query . (index=indexname earliest=1499819400 latest=1499848200 | where Tag="Tagname" |bin _time span=10m | dedup _time|table _time Tag Value | where Value=0).
this is displaying values like this
_time count
7/12/2017 11:00 0
7/12/2017 11:10 0
7/12/2017 11:20 0
7/12/2017 11:30 0
7/12/2017 11:40 0
But I want output like this (first 30 mins at a time then ten mins)
_time count
7/12/2017 11:00 0
7/12/2017 11:30 0
7/12/2017 11:40 0

Re: how can i get details with both span 10m and 30 m with dedup _time.

DalJeanis — Wed, 12 Jul 2017 15:07:39 GMT

Are you saying you want the results for every half hour up to the end of the last half hour increment, and then every ten minutes after that? So, the 11:30 record is actually 11:30-40 and the 11:40 is 11:40-50?

Re: how can i get details with both span 10m and 30 m with dedup _time.

DalJeanis — Wed, 12 Jul 2017 15:42:26 GMT

Okay, without understanding the underlying data, and what your use case is, your code doesn't make a lot of sense to me. dedup is going to give you the first record it encounters for each _time group, which will be the very latest record for each _time group, and then you want to throw away all _time groups that do not have a zero Value at the end of their ten-minute _time period?

If so, then this accomplishes the same thing

index=indexname earliest=1499819400 latest=1499848200 Tag="Tagname"
| bin _time span=10m 
| stats latest(Value) as Value by _time Tag
| where Value=0

The problem is, once you throw away non-zero values, it doesn't make any sense to group the remaining records into 30 minute increments, unless there are three 0 records in that time frame. That would look like this -

index=indexname earliest=1499819400 latest=1499848200 Tag="Tagname"
| table _time Tag Value 
| bin _time span=10m 
| bin _time as Time30 span=30m 
| dedup _time
| where Value=0
| eventstats count as Count30 by Time30
| eval _time=if(Count30=3,Time30,_time)
| dedup _time

But in this case, you haven't accounted for the potential difference between records (A) that happen to be at 9:30 but represent only 10 minutes because Value was not zero at 9:40 and 9:50, and records (B) that happen at 9:30 and represent a half hour of 0.

I suspect that what you really want to know is what periods you have received no activity. For that, you need a different strategy. This one here will work if you can be assured there will be records in each ten minute time period, and some of those records will have zero values...

 index=indexname earliest=1499819400 latest=1499848200 Tag="Tagname"
| table _time Tag Value 
| bin _time as StartTime span=10m 
| stats max(Value) as Value by StartTime Tag
| rename COMMENT as "Above gets us the highest Value in each ten-minute period, and the StartTime"

| rename COMMENT as "Now we kill non-zero periods, and calculate the EndTime"
| where Value=0
| eval EndTime=Time+600

| rename COMMENT as "This combines each half-hour period if it is three ten-minute periods of zero activity"
| bin Time as Time30 span=30m 
| eventstats count as Count30 max(EndTime) as End30 by Time30
| eval StartTime=if(Count30=3,Time30,StartTime)
| eval EndTime=if(Count30=3,End30,EndTime)
| dedup StartTime


| rename COMMENT as "And now we format our output Start and EndTime"
| eval StartTime=strftime(StartTime,"%Y-%m-%d %H:%M:%S")
| eval EndTime=strftime(EndTime,"%Y-%m-%d %H:%M:%S")

If you can't be sure that there will be records in each _time period, then alter the first section to add dummy records with an appendpipe as follows...

index=indexname earliest=1499819400 latest=1499848200 Tag="Tagname"
| table _time Tag Value 
| bin _time as StartTime span=10m

| rename COMMENT as "Add an extra zero record for each time period, then take the highest Value for each time period."
| appendpipe [| stats min(StartTime) as FirstTime max(StartTime) as LastTime by Tag | eval StartTime=mvrange(FirstTime,LastTime,600) | table StartTime Tag | mvexpand StartTime | eval Value = 0] 
| stats max(Value) as Value by StartTime Tag
| rename COMMENT as "Above gets us the highest Value in each ten-minute period, and the StartTime"

Re: how can i get details with both span 10m and 30 m with dedup _time.

ajayabburi508 — Thu, 13 Jul 2017 06:16:29 GMT

Hai Dalnis thanks for replay.

What i Need is i want machine which value is zero for particular epoch values.
if machine value value is zero for ten minutes then print that time like

_time count
7/12/2017 11:00 0
7/12/2017 11:10 0

this means my machine value is zero for 11:00 to 11:10 .

then if my machine value is zero for thirty minutes then print time like this

_time count
7/12/2017 11:30 0
7/12/2017 12 :00 0

So finally what i want is which timeperiod my machine value is zero?

_time Value
7/12/2017 11:30 0
7/12/2017 11 :40 0
7/12/2017 11 :50 0

7/12/2017 12 :00 0
7/12/2017 11:00 0
7/12/2017 11:10 0

but i want like this

_time Value
7/12/2017 11:30 0
7/12/2017 12 :00 0
7/12/2017 11:00 0
7/12/2017 11:10 0

my machine value is zero for thirty minutes so it displays 11:30 to 12:00 Value 0
my machine value is zero for ten minutes so it displays 11:00 to 11:10 Value 0

Re: how can i get details with both span 10m and 30 m with dedup _time.

ajayabburi508 — Thu, 13 Jul 2017 08:34:12 GMT

hai Bro I want when the value of machine is zero

if machine value is zero for 30 mins i want time stamp of that 30 mins with Value.

Re: how can i get details with both span 10m and 30 m with dedup _time.

ajayabburi508 — Fri, 14 Jul 2017 13:45:47 GMT

_time Value
7/12/2017 11:30 0
7/12/2017 11 :40 0
7/12/2017 11 :50 0
7/12/2017 12 :00 0
7/12/2017 11:00 0
7/12/2017 11:10 0

but i want like this

_time Value
7/12/2017 11:30 0
7/12/2017 12 :00 0
7/12/2017 11:00 0
7/12/2017 11:10 0

my machine value is zero for thirty minutes so it displays 11:30 to 12:00 Value 0
my machine value is zero for ten minutes so it displays 11:00 to 11:10 Value 0

Re: how can i get details with both span 10m and 30 m with dedup _time.

DalJeanis — Tue, 03 Oct 2017 23:30:26 GMT

@ajayabburi508 - Did you get this solved? The code that we gave you on July 12 would do all that for you. It will get the start and end times of zero values for each 10-minute period by Tag, and group any sets of 3 into 30-minute periods.

Here it is in order and formatted for easy reading.

index=indexname earliest=1499819400 latest=1499848200 Tag="Tagname"
 | table _time Tag Value 
 | bin _time as StartTime span=10m 

 | rename COMMENT as "Add an extra zero record for each time period, then take the highest Value for each time period."
 | appendpipe 
    [| stats min(StartTime) as FirstTime max(StartTime) as LastTime by Tag 
     | eval StartTime=mvrange(FirstTime,LastTime,600) 
     | table StartTime Tag 
     | mvexpand StartTime 
     | eval Value = 0
     ] 
 | stats max(Value) as Value by StartTime Tag

 | rename COMMENT as "Above gets us the highest Value in each ten-minute period, and the StartTime"
 | rename COMMENT as "Now we kill non-zero periods, and calculate the EndTime"
 | where Value=0
 | eval EndTime=Time+600

 | rename COMMENT as "This combines each half-hour period if it is three ten-minute periods of zero activity"
 | bin Time as Time30 span=30m 
 | eventstats count as Count30 max(EndTime) as End30 by Time30
 | eval StartTime=if(Count30=3,Time30,StartTime)
 | eval EndTime=if(Count30=3,End30,EndTime)
 | dedup StartTime

 | rename COMMENT as "And now we format our output Start and EndTime"
 | eval StartTime=strftime(StartTime,"%Y-%m-%d %H:%M:%S")
 | eval EndTime=strftime(EndTime,"%Y-%m-%d %H:%M:%S")