Count as 1 value if TIMESTAMPs are consecutives

[ngerosa]

Hello,
I have this search string

index="flap" DELTA_SPAN>= 3
| eval TRATTA=NODO_A."->".NODO_Z
| stats count(TRATTA) as FLAP by TRATTA
| where FLAP>2
| sort -FLAP

What I want is If I have for one TRATTA three consecutive TIMESTAMPs (10:15 - 10:30 - 11:00) I want to count as 1 value and not as 3 values.

For example if for TRATTA_A there are timestamp 2017-05-24 07:28:12.0 and 2017-05-24 07:32:13.0 and 2017-05-24 07:40:13.0 , I want to count as one event.

Any Ideas?

Thanks a lot!
Nicolò

[ngerosa · Accepted solution]

I found the solution!

index=flap DELTA_SPAN>=3 | bin _time span=15m | dedup TRATTA _time |stats count(TRATTA) as FLAP by TRATTA
| where FLAP>2
| sort -FLAP

[somesoni2]

Assuming the log frequency is 15 mins, try this

index="flap" DELTA_SPAN>= 3 
| eval TRATTA=NODO_A."->".NODO_Z
| sort 0  TRATTA _time
| streamstats values(_time) as prev_time by TRATTA
| where isnull(prev_time) OR (_time-prev_time>900)
| stats count(TRATTA) as FLAP by TRATTA 
| where FLAP>2 
| sort -FLAP

[ngerosa]

Hi somesoni2,
thanks for the answer.
I tried but the search retrieve no results.
My timestamp is like this : _time = 2017-05-24 07:28:12.0
For example if for TRATTA_A there are timestamp 2017-05-24 07:28:12.0 and 2017-05-24 07:32:13.0 , I want to count as one event.

Thanks

[inventsekar]

07:28:12.0
and
07:32:13.0 ( Consecutive ?!?!? )

I want to count as one event // whats the criteria to count as one event? on the question title you said "Count as 1 value if TIMESTAMPs are consecutives"
consecutive minutes?!?!

[ngerosa]

Yes because I have event every 15 minutes

[inventsekar]

ok, assuming first log is at 0min, second log is at 15mins and third log is at 30mins.
so you want to count these 3 logs as one event.

so 15mins + 15mins = 30mins (30*60 = 1800sec)

Somesoni was using 900 .. maybe change it to 1800.
also he was using _time-prev_time>900 ... try it with less-than (as _time-prev_time<1800)

index="flap" DELTA_SPAN>= 3
| eval TRATTA=NODO_A."->".NODO_Z
| sort 0 TRATTA _time
| streamstats values(_time) as prev_time by TRATTA
| where isnull(prev_time) OR (_time-prev_time<1800)
| stats count(TRATTA) as FLAP by TRATTA
| where FLAP>2
| sort -FLAP

[ngerosa]

Hi inventsekar, I don't know why but it didn't work. I try to visualize the result of the diff=_time-prev_time and in the image attached you can find the results.
There is a problem exactly on the diff.
Can you help me?

image upload