Alert with 2 searches, that counts events and show logs only when both searches return data.

[siltechnix]

Hi 

my aim is to create an alert that will perform first search and look for at least 10 similar events within last
30 minutes.

Then using another search it will look for at least one occurrence of different log

At the end it should compare which log came first using min(_time) and trigger alert only if both searches returned logs. Checking the time can be omitted but it is important that both searches have returned anything.
Both searches works fine when I run them separately, but then I am appending one to the other, I am receiving only logs from the 1 search.

As an outcome would like to see the logs/events instead of stats table.   

I am a novice so if anybody could give me some hints how could I make it all work as expected, it would be great.

Here's what I have done so far:

index="index1" (Message=SEARCH1 earliest=-31m@m latest=-1m@m ) 

| transaction Message  SrcIP | where eventcount > 10
| search Message="SEARCH1"
| eventstats min(_time) as condition1 

| append 
[ | search index="index1" SEARCH3 earliest=-61m@m latest=-1m@m 
| eventstats min(_time) as condition2 ]

| where condition1 > condition2 

[richgalloway]

The append command combines the results of two searches in a very rudimentary way.  That is, the output of the second search follows the output of the first.  In tablular form it might look like this:

_time	_raw	condition1	condition2
2020-11-29T14:40:04	result 1 from search 1	2020-11-29T14:40:03
2020-11-29T14:40:03	result 2 from search 1	2020-11-29T14:40:03
2020-11-29T14:40:02	result 1 from search 2		2020-11-29T14:40:01
2020-11-29T14:40:01	result 2 from search 2		2020-11-29T14:40:01

Because half the results contain the condition1 field and the other half contain the condition2 field only the first half will meet the condition where condition1>condition2.

The solution is to merge the two sets of events before comparing fields.

index="index1" (Message=SEARCH1 earliest=-31m@m latest=-1m@m ) 

| transaction Message  SrcIP | where eventcount > 10
| search Message="SEARCH1"
| eventstats min(_time) as condition1 

| append 
[ | search index="index1" SEARCH3 earliest=-61m@m latest=-1m@m 
| eventstats min(_time) as condition2 ]
```Merge the results```
| stats values(*) as * by someCommonField
| where condition1 > condition2