Exclude value based on subsearch

[ngerosa]

Hello,
I want to exclude some values if that have the field SPAN_LOSS_MAX=50 between midnight to 7 a.m.

This is my approach:

index="flap" DELTA_SPAN>=3 | search NOT
[search index="flap" SPAN_LOSS_MAX=50 | dedup CONCATENATE_Z sortby +_time
|eval Hour=strftime(_time,"%H")
|where Hour>=00 AND Hour<=7
|stats count by Hour]
|dedup CONCATENATE_Z sortby +_time
| eval Hour=strftime(_time,"%H")
| chart count as FLAP by Hour

But this search returns also values that are in the subsearch.

Any Ideas?

Thanks!

[somesoni2 · Accepted solution]

Why not just do this

index="flap" DELTA_SPAN>=3 |eval Hour=tonumber(strftime(_time,"%H") )
| where NOT (SPAN_LOSS_MAX=50 AND Hour>=0 AND Hour<7)
|dedup CONCATENATE_Z sortby +_time
| eval Hour=strftime(_time,"%H") 
| chart count as FLAP by Hour

[ngerosa]

Thanks it works! Just a question:
if I want to exclude also 11 p.m.?
I tried | where NOT (SPAN_LOSS_MAX=50 AND Hour>=0 AND Hour<7 AND Hour !=23 ) but it didn't work.
Thanks

[somesoni2]

Try this

...| where NOT (SPAN_LOSS_MAX=50 AND Hour>=23 AND Hour<7)

[ngerosa]

I've already tried but it didn't work.

[rafaelsalazar]

NOT (SPAN_LOSS_MAX=50 AND ((Hour>=0 AND Hour<7) OR Hour=23))

[ngerosa]

Thank you! It works!

[rafaelsalazar]

Hello ngerosa,

There is already a default hour extraction called date_hour.. if you don't have it, I will recommend building the extraction yourself to be able to filter since search command.

index="flap" DELTA_SPAN>=3 NOT NOT (SPAN_LOSS_MAX=50 AND date_hour>=0 AND date_hour<=7)
| dedup CONCATENATE_Z sortby + _time
| chart count AS FLAP by date_hour

Hope it helps,
Regards.