Find three different events within a timeframe for the same user

[mlorrette]

In this dataset, transactions (#3 + #9 + #10 - Mike), and (#5 + #7 +#11  - Alex) -- Would be displayed.

#	Time	User	Transaction
1	12:01	David	Login from 1.1.1.1
2	12:01	Joe	Login from 2.2.2.2
3	12:02	Mike	Login from 1.1.1.1
4	12:03	David	Something else
5	12:05	Alex	Login from 1.1.1.1
6	12:06	Mike	Something else
7	12:09	Alex	Delete table
8	12:10	Joe	Delete table
9	12:06	Mike	Delete table
10	12:09	Mike	Insert Table
11	12:14	Alex	Insert Table
12	12:20	David	Delete table

Looking for one search to find all events where within 10 minutes:

1. User logged in from IP address 1.1.1.1 (Search:  userIP = "1.1.1.1"  transaction="Logged") 

2. The same user then deleted a table (Search: databaseAction = "DeleteTable")

3. The same user then inserted a table (Search: databaseAction = "InsertTable")

I can use startswith and endswith with transaction, but this only gives me the first and last event, not the second.

[yuanliu]

First, thank you for giving a clear illustration of input, desired output, and the logic linking the two.  Let me confirm: Are you skipping Joe because IP address is not 1.1.1.1?

Assuming this is correct, you are looking for something like

<some index search> transaction IN (Logged, DeleteTable)
| stats list(transaction) as transaction min(_time) as logon_time max(_time) as delete_time values(userIP) as userIP by User
| where mvindex(transaction, 0) == "Logged" AND mvindex(transaction, -1) LIKE "DeleteTable"
  AND delete_time < relative_time(logon_time, "+10min") AND userIP == "1.1.1.1"
| fieldformat logon_time = strftime(logon_time, "%F %T")
| fieldformat delete_time = strftime(delete_time, "%F %T")

Output from your sample data is

User	transaction	logon_time	delete_time	userIP
Alex	Logged DeleteTable	2023-11-05 12:05:00	2023-11-05 12:10:00	1.1.1.1
Mike	Logged DeleteTable	2023-11-05 12:02:00	2023-11-05 12:06:00	1.1.1.1

This is an emulation you can play with and compare with real data

| makeresults
| eval _raw="#	Time	User	Transaction
1	12:01	David	Login from 1.1.1.1
2	12:01	Joe	Login from 2.2.2.2
3	12:02	Mike	Login from 1.1.1.1
4	12:03	David	Something else
5	12:05	Alex	Login from 1.1.1.1
6	12:06	Mike	Something else
7	12:09	Joe	Delete table
8	12:10	Alex	Delete table
9	12:06	Mike	Delete table
10	12:20	David	Delete table"
| multikv forceheader=1
| eval transaction = case(Transaction LIKE "Login from %", "Logged", Transaction == "Delete table", "DeleteTable", true(), "SomethingElse")
| rex field=Transaction "Login from (?<userIP>.+)"
| fields - _* linecount Transaction
| eval _time = strptime(Time, "%H:%M")
| search transaction IN (Logged, DeleteTable)
``` the above emulates
<some index search> transaction IN (Logged, DeleteTable)
```

[mlorrette]

Elegant solution. I'll take a look with our prod data and respond here. Thanks again the the reply.