associated query according to the context

[caili]

My raw data is like:

   FieldA  | FieldB  | FieldC | FieldD 
1439638106 | 1.1.1.1 | 21 | 500
1439637106 | 1.1.1.1 | 21 | 200
1439636106 | 2.2.2.2 | 23 | 200
1439635106 | 3.3.3.3 | 21 | 500 
1439634106 | 4.4.4.4 | 25 | 200  
1439633106 | 3.3.3.3 | 21 | 200
1439631106 | 5.5.5.5 | 28 | 500
1439532106 | 3.3.3.3 | 21 | 200 
.......

FieldA is a timestamp field
1、
I want to find the FieldD=200 by FieldB,FieldC, but in the next time (FieldA +1h@h), FieldD!=500

so the result is:

   FieldA  | FieldB  | FieldC | FieldD 
1439636106 | 2.2.2.2 | 23 | 200
1439634106 | 4.4.4.4 | 25 | 200  
.......

2、
I want to find the FieldD=500 by FieldB,FieldC, and in the previous time (FieldA -1h@h), FieldD=200

so the result is:

   FieldA  | FieldB  | FieldC | FieldD 
1439638106 | 1.1.1.1 | 21 | 500
1439637106 | 1.1.1.1 | 21 | 200
1439635106 | 3.3.3.3 | 21 | 500  
1439633106 | 3.3.3.3 | 21 | 200
.......

How can I write the search query ?

[maciep]

I think you want to use autoregress

Something like:

... | autoregress p=1 FieldD as Next_FieldD | where FieldD=200 AND Next_FieldD!=500 

And

... | sort FieldA | autoregress p=1 FieldD AS Prev_FieldD | where FieldD=500 AND Prev_FieldD=200

Not sure if you want to see those results in the same search or separate, but I think you can pipe to autoregress to get the next field, re-sort by FieldA, autoregress again to get the prev field, then pipe to where to filter on those two scenarios.

[caili]

I can use map command to meet the second requirement, but the search speed is slow

And the first one may be need using "NOT" , but I'm unable to write the query

[caili]

I think the autoregress command does not meet the requirements, because I don't know the "p".

I want query according to the FieldA (time)

Thank you for your kindness~

[maciep]

oh ok, I think I misunderstood the requirement. If you sort by B, then C then A, will the events be in an order where autoregress could work? Or are the timestamps random enough where you still can't rely on looking before/after the current event?

Still not sure if I understand the data set well enough to give this a shot....